计算月对月销售差额

Question

我正试图计算过去三年销售中的月到月差异。我有点困在如何让历史月的实际加入到历史月的+1个月的实际。当月份是12月时，它不会在结果集中显示，从而导致1月的下一个月不显示。

with _t1 as (
SELECT
Year(Invoicedate) as [Year]
,Month(Invoicedate) as [Month]
,Sum([TaxableSalesAmt]+[NonTaxableSalesAmt]+[FreightAmt]+[SalesTaxAmt]) as Revenue
FROM [InvoiceHistory]
where year(invoicedate) >= '2015'
group by Year(Invoicedate) ,Month(Invoicedate)
)
,_t2 as (
SELECT 
Year(Invoicedate) as [Year]
,Month(Invoicedate) as [Month]
,Sum([TaxableSalesAmt]+[NonTaxableSalesAmt]+[FreightAmt]+[SalesTaxAmt]) as Revenue
FROM [MAS_RDP].[dbo].[AR_InvoiceHistoryHeader]
where year(invoicedate) >= '2015'
group by Year(Invoicedate) ,Month(Invoicedate)
)

Select _t1.year
,_t1.Month
,_t1.Revenue
,_t2.year
,_t2.month
,_t2.Revenue
,_t2.Revenue-_t1.Revenue as GrowthActual
,(_t2.Revenue-_t1.Revenue)/_t2.Revenue*100 as GrowthPercent
from _t1
inner join _t2 on _t1.year = _t2.year and _t1.month = _t2.month-1
order by _t1.year, _t1.month

Answer 1

这在很大程度上取决于您根本不使用日期值作为日期。

您需要的日期逻辑是以月份为单位来查找任意日期与invoicedate之间的差异，然后将以月份为单位的差异添加到相同的任意日期中，以获得该月份的第一天。然后，您可以添加或减去这一差异，以找到前几个月或之后。

您可以通过以下操作来查看此操作：

select getdate() as RightNow
      ,dateadd(day,  datediff(day  , 0, getdate())  , 0) as DayStart
      ,dateadd(month,datediff(month, 0, getdate())-1, 0) as PreviousMonthStart
      ,dateadd(month,datediff(month, 0, getdate())  , 0) as MonthStart
      ,dateadd(month,datediff(month, 0, getdate())+1, 0) as NextMonthStart

以下内容不仅适用于您，而且由于您的where子句中缺少函数而在较短的时间内运行：

with _t1 as
(
    select dateadd(month,datediff(month,0,Invoicedate),0) as InvoiceMonth
        ,sum([TaxableSalesAmt]
            +[NonTaxableSalesAmt]
            +[FreightAmt]
            +[SalesTaxAmt]
            ) as Revenue
    from [InvoiceHistory]
    where invoicedate >= '20150101'
    group by dateadd(month,datediff(month,0,Invoicedate),0)
)
,_t2 as
(
    select dateadd(month,datediff(month,0,Invoicedate),0) as InvoiceMonth
        ,dateadd(month,datediff(month,0,Invoicedate)-1,0) as PreviousInvoiceMonth
        ,sum([TaxableSalesAmt]
            +[NonTaxableSalesAmt]
            +[FreightAmt]
            +[SalesTaxAmt]
            ) as Revenue
    from [MAS_RDP].[dbo].[AR_InvoiceHistoryHeader]
    where invoicedate >= '20150101'
    group by dateadd(month,datediff(month,0,Invoicedate),0)
            ,dateadd(month,datediff(month,0,Invoicedate)-1,0)
)
select _t1.InvoiceMonth
    ,_t1.Revenue
    ,_t2.PreviousInvoiceMonth
    ,_t2.Revenue
    ,_t2.Revenue-_t1.Revenue as GrowthActual
    ,(_t2.Revenue - _t1.Revenue)/_t2.Revenue*100 as GrowthPercent
from _t1
    inner join _t2
        on _t1.InvoiceMonth = _t2.PreviousInvoiceMonth
order by _t1.InvoiceMonth