CODEWITHSUNDEEP
sqlsql-servert-sql
Phil Collins
Phil Collins

Reputation: 327

SQL - Display sales this month and last month in the same table

I'm using SQL Server 2018 and I have a single table called SALES, which looks something like this

SALES_PERSON_ID MONTH   SALES
001             Aug-20  3
002             Aug-20  2
003             Aug-20  0
001             Sep-20  4
002             Sep-20  2
003             Sep-20  3

Each new month is added to the table, which will show the accumulative sales for each sales person.

I want to summarise this data as sales this month vs last month, which I've done like this:

SELECT 
   tb1.SALES_PERSON_ID
  ,SALES_THIS_MONTH
  ,SALES_LAST_MONTH
FROM 
   (SELECT SALES_PERSON_ID, MONTH, SUM(SALES) AS SALES_THIS_MONTH 
    FROM SALES
    WHERE MONTH = 'Sep-20') AS tb1
JOIN
   (SELECT SALES_PERSON_ID, MONTH, SUM(SALES) AS SALES_LAST_MONTH
    FROM SALES
    WHERE MONTH = 'Aug-20') AS tb2
ON. tb1.SALES_PERSON_ID = tb2.SALES_PERSON_ID

If Sep-20 is the current month, then it gives me something like this:

SALES_PERSON_ID SALES_THIS_MONTH SALES_LAST_MONTH
001             4                3
002             2                2
003             3                0

That works fine, but in reality I have hundreds of thousands of records with hundreds of months and I can't "hard-code" the dates for all possible months in a WHERE clause. I want a dynamic solution that will always provide the sum of sales for a given month and what the sum of sales was for the previous month specific to that record.

So I would need my tb2 to somehow always know which month tb1 was referring to and then subtract a month. Any help would be appreciated!

Upvotes: 1

Views: 1834

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use lag():

select s.*,
       lag(sales) over (partition by SALES_PERSON_ID order by month) as prev_sales
from sales s;

This assumes that month is really a date. If it is not, you can use:

       lag(sales) over (partition by SALES_PERSON_ID order by convert(date, '01-' + month)) as prev_sales

If you want to filter this for only the most recent month, use a subquery for the lag():

select s.*
from (select s.*,
             lag(sales) over (partition by SALES_PERSON_ID order by month) as prev_sales
      from sales s
     ) s
where s.month = dateadd(day, 1, eomonth(getdate(), -1))

Upvotes: 1

Related Questions