CODEWITHSUNDEEP
sqlsql-server-2012
80gm2
80gm2

Reputation: 202

How do I calculate last year revenue within my query?

I need to calculate revenue for the current year and the previous year, my desired output is this:

Date            revenue_current   revenue_previous

January-2017        350                450

December-2016       750                250

November-2016       4550               4230

(Previous year revenue is the same month in the previous year)

Data:

SaleDate_Yr | SaleDate_Pd | Revenue

2017               1         100

2017               1         200

2017               1         300

2016               11        100

2016               11         50

2017               1         50

2016               12        50

2016               12        50

2016               1         100

2015               11        300

2015               12        520

2016               1         100

(Revenue numbers do not add up in the desired output or the sample data please pretend they do).

My query so far:

SELECT (CONVERT(varchar(10), SaleDate_Pd) + '-' + '2017') AS sale_pd, 

SUM(CASE WHEN SaleDate_Yr IN ('2017') THEN Total_Revenue ELSE 0 END)
as revenue_ty, 

SUM(CASE WHEN SaleDate_Yr IN ('2016') THEN Total_Revenue ELSE 0 END) as revenue_ly

FROM

[sales].[dbo].[mycompany]

WHERE

SaleDate_Yr IN ('2016', '2017')
AND
SaleDate_Pd IN ('11','12','1')

GROUP BY (CONVERT(varchar(10), SaleDate_Pd) + '-' + '2017')

This seems to work fine provided I look at sales from January 2017 to December 2017. However sometimes I want to look at specific time period such as November 2016, December 2016, January 2017 so the previous year figure wouldn't work in the November and December dates, and would also appear as November-2017 in the date column where I append the year to the end. I manually added the year in because if I didn't I got separate rows for each year and the current year and previous year figures wouldn't appear on the same line.

Upvotes: 1

Views: 4246

Answers (2)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

You could access the last row and work with subqueries, but I cannot tell you how it works by heart.

If the year would be number (the use of convert actually suggests this), you could do a fancy outer join.

It may make sense to create a view which contains a single row for each year and month. If you don't want the view, you can replace it with the corresponding subquery.

CREATE VIEW revenue_per_month AS
select 
  SaleDate_Pd,
  SaleDate_Yr,
  /* Probably it's not summed, you need to write the query the
     way that you know the monthly revenue */
  SUM(Revenue)
from mycompany
group by SaleDate_Pd, SaleDate_Yr

select
  CONVERT(varchar(10), thisYear.SaleDate_Pd)
     + '-' 
     + CONVERT(varchar(10), thisYear.SaleDate_Yr) as Date,
  thisYear.Revenue as revenue_current,
  lastYear.Revenue as revenue_previous
FROM revenue_per_month thisYear 
  left outer join revenue_per_month lastYear 
    on (
      thisYear.SaleDate_Yr - 1 = lastYear.SaleDate_Yr 
      AND thisYear.SaleDate_Pd = lastYear.SaleDate_Pd)

Upvotes: 1

Rachel Ambler
Rachel Ambler

Reputation: 1594

Create Table R
    (y Int, m Int, r int)

Insert Into R Values
    (2017, 1, 11000)
    ,(2016, 12, 1200)
    ,(2016, 11, 1100)
    ,(2016, 10, 500)
    ,(2016, 10, 500)
    ,(2016, 9, 900)
    ,(2016, 8, 800)
    ,(2016, 7, 700)
    ,(2016, 6, 600)
    ,(2016, 5, 250)
    ,(2016, 5, 250)
    ,(2016, 4, 300)
    ,(2016, 4, 100)
    ,(2016, 3, 300)
    ,(2016, 2, 200)
    ,(2016, 1, 100)
    ,(2015, 12, 12)
    ,(2015, 11, 5)
    ,(2015, 11, 6)
    ,(2015, 10, 10)
    ,(2015, 9, 9)
;

With _r(y ,m, r, D)
    As
        (
            Select   y, m, Sum(r), Cast(Cast(y As Char(4)) + Right('0' + Cast(m AS VarChar(2)), 2) + '01' As Date)
              From   r
              Group
              By     y, m
    )
Select   pc.y As Year, pc.m As Month, pc.r As Revenue_Current, IsNull(pp.r, 0) As Revenue_Previous
  From   _r As pc
  Left
  Join   _r As pp
    On      pp.d = DateAdd(Year, -1, pc.d)
  Where  pc.D Between '20161001' And  '20170101'
  Order
     By Pc.d;


(21 row(s) affected)
Year        Month       Revenue_Current Revenue_Previous
----------- ----------- --------------- ----------------
2016        10          1000            10
2016        11          1100            11
2016        12          1200            12
2017        1           11000           100

(4 row(s) affected)

Upvotes: 0

Related Questions