SQL Server How to get sales diff between previous and next month

lxkprmvk 于 2023-05-28 发布在其他

关注(0)|答案(2)|浏览(180)

I have table where I am having sales data for each day.
| Date | Sales |
| ------------ | ------------ |
| 3/1/2023 | 800 |
| 3/2/2023 | 300 |
| 3/3/2023 | 500 |
| 4/1/2023 | 300 |
| 4/2/2023 | 500 |
| 4/3/2023 | 200 |
| 5/1/2023 | 100 |
| 5/2/2023 | 400 |
| 5/3/2023 | 400 |

Expected Output:

Date	Sales	Diff
5/1/2023	900	100
4/1/2023	1000	600
3/1/2023	1600	0

I created a query to get sales sum for each month and I need to calculate difference between previous month and next month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) AS TrendStartDate,
       SUM(Sales) AS TotalSales ,
       LEAD(SUM(Sales), 1) OVER (PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) DESC) AS Sales_diff 
FROM salestable
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) ASC

Could any one please help on this?

sql-server

来源：https://stackoverflow.com/questions/76306201/how-to-get-sales-diff-between-previous-and-next-month

2条答案

按热度按时间

pbgvytdp1#

You can apply a simple aggregation first, and aggregate:

minimum date,
sum of sales yet partitioning on:
year date,
month date

Then you can use the LAG window function to gather your previous sum of sales.

SELECT MIN(Date) AS Date, 
       SUM(Sales) AS TotalSales,
       LAG(SUM(Sales), 1, SUM(Sales)) OVER(ORDER BY YEAR(Date), MONTH(Date)) - SUM(Sales) AS Diff
FROM tab
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Date DESC

The last ORDER BY is not needed, it's just for visualization purposes.

Output:
| Date | TotalSales | Diff |
| ------------ | ------------ | ------------ |
| 2023-05-01 | 900 | 100 |
| 2023-04-01 | 1000 | 600 |
| 2023-03-01 | 1600 | 0 |

Check the demo here .

赞(0）回复(0）举报 2023-05-28

mnemlml82#

You can do it by applying the LAG function to only the rows with totals as follows :

First we need to get totals per month using :

select MIN(Date) as date, sum(Sales) as TotalSales
from salestable
group by YEAR(Date), MONTH(Date)

Then apply the analytic function LAG() to this dataset to get difference:

select *, LAG(TotalSales, 1, TotalSales) OVER(ORDER BY date) - TotalSales AS Diff
from (
  select MIN(Date) as date, sum(Sales) as TotalSales
  from salestable
  group by YEAR(Date), MONTH(Date)
) as s
order by date desc

Demo here

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server How to get sales diff between previous and next month

2条答案

相关问题

热门标签

最新问答