CODEWITHSUNDEEP
sqlsql-server
FelipePerezR
FelipePerezR

Reputation: 175

Compare totalamount from previous month with current month result

I am trying to compare the total amount between months, current and previous month. The issue comes when a new year appears.

SELECT 
    currYear.orderyear, currYear.ordermonth, currYear.totalamount, 
    prevYear.totalamount
FROM 
    (SELECT 
         SUM(val) AS totalamount, 
         YEAR(orderdate) AS orderyear,
         MONTH(orderdate) AS ordermonth
     FROM 
         Sales.OrderValues 
     GROUP BY 
         YEAR(orderdate), MONTH(orderdate)
    ) AS currYear
LEFT JOIN 
    (SELECT 
         SUM(val) AS totalamount, 
         YEAR(orderdate) AS orderyear,
         MONTH(orderdate) AS ordermonth
     FROM 
         Sales.OrderValues 
     GROUP BY 
         YEAR(orderdate), MONTH(orderdate)
    ) AS prevYear ON currYear.orderyear = prevYear.orderyear
                  AND currYear.ordermonth = prevYear.ordermonth + 1
WHERE 
    currYear.orderyear = '2006' 
    OR currYear.orderyear = '2007'
ORDER BY 
    currYear.orderyear, currYear.ordermonth

table result

Upvotes: 1

Views: 1509

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use lag(). I'm not sure what the comparison is, but this should be most of the work:

SELECT YEAR(orderdate) as orderyear, MONTH(orderdate) as ordermonth,
       SUM(val) as totalamount, 
       LAG(SUM(val)) OVER (ORDER BY YEAR(orderdate), MONTH(orderdate)) as prev_totalamount
FROM Sales.OrderValues 
GROUP BY YEAR(orderdate), MONTH(orderdate);

This does assume that you have data for every month, which seems likely in this situation.

Upvotes: 3

Related Questions