CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MattC
MattC

Reputation: 135

Calculating month-to-month sales variance

I am trying to calculate the month to month variance in sales over the past 3 years. I am a little stuck on how to get the historical month's actual joined to the historical month's + 1 month actual. When the month is December, it does not display in the result set, causing the next month of January not to display.

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

Upvotes: 1

Views: 909

Answers (1)

iamdave
iamdave

Reputation: 12243

This is mostly down to you simply not using date values as dates.

The date logic you need is to find the difference in months between an arbitrary date and the invoicedate, then add that difference in months to the same arbitrary date to get the first day of that month. You can then add or subtract from this difference to find months before or after.

You can see this working by playing around with the following:

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

The following should not only work for you, but run in less time due to the lack of functions in your where clauses:

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

Upvotes: 2

Related Questions