June 26, 2023 at 4:58 pm
Hi
In my code below the row data are not grouping into month by month name. I want to group row data of the same month.
| Date | Revenue |
| -------------- |---------|
| 2023-03-01 | 500 |
| 2023-03-29 | 300 |
| 2023-04-15 | 600 |
| 2023-05-11 | 300 |
| 2023-05-23 | 600 |
| 2023-05-31 | 500 |
`
SELECT
DATENAME(month, OrderDate) as Month,
Sum(Amount) as revenue,
Amount - LAG (Amount) OVER (ORDER BY OrderDate ASC) AS Revenue_growth
FROM OrderDetail
Group by OrderDate
`
Expected output
| Date | Revenue | Revenue_growth |
| ---------- | --------- | ------------------- |
| March | 800 | 0 |
| April | 600 | -200 |
| May | 1400 | 800 |
June 26, 2023 at 10:34 pm
You have to sum first, and then use LAG on that result to get what you want... like this... Oh, and since you're new, I created tables for you etc. You should do that from now on... makes answering your questions much easier!
use tempdb;
go
CREATE TABLE Revenues (
RevenueDate DATE,
Amount INT
);
GO
INSERT INTO Revenues VALUES
('2023-03-01' , 500 ),
('2023-03-29' , 300 ),
('2023-04-15' , 600 ),
('2023-05-11' , 300 ),
('2023-05-23' , 600 ),
('2023-05-31' , 500 );
SELECT d.FOM
, d.Month_name
, d.Amount
, PrevAmt = LAG(d.Amount,1) OVER (ORDER BY d.FOM)
, RevenueGrowth = d.Amount - LAG(d.Amount,1) OVER (ORDER BY d.FOM)
FROM
(SELECT FOM = DATEFROMPARTS(YEAR(RevenueDate),MONTH(RevenueDate),1)
, Month_Name = FORMAT(RevenueDate,'MMMM')
, Amount
FROM Revenues) d;
June 27, 2023 at 8:57 pm
It's because you're using the wrong GROUP BY expression. You're grouping on DATE, not by MONTH. You need to use an expression that gives you the same value for every date within a given month. I like EOMONTH(), but Piet's expression will also work.
You also do not need to sum first and then do a lag. You can do it all in one fell swoop.
Note, you cannot use OrderDate directly. You either need to use the chose unique expression or an aggregate on any other expression that uses OrderDate.
Also, you are missing aggregates on your expressions for the Revenue growth calculation. You may also want to specify a default value for the LAG by specifying the optional 2nd and 3rd parameters.
SELECT
MIN(DATENAME(month, OrderDate)) as Month,
Sum(Amount) as revenue,
SUM(Amount) - LAG (SUM(Amount)) OVER (ORDER BY EOMONTH(OrderDate) ASC) AS Revenue_growth
FROM OrderDetail
Group by OrderDate
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 27, 2023 at 9:03 pm
Thank you all
June 28, 2023 at 3:07 pm
Thank you all
I was exploring the question/solutions and noticed that neither of the queries grouped the March or May records into one monthly total, so they return mutliple records per month name.
I broke it up into two parts for my clarity. My first CTE uses Drew's idea with EOMONTH() to aggregate monthly amounts. Next, I used pietlinden's lag to calculate monthly revenue growth. In my lag, I added a default so the first record would return a zero for revenue growth (essentially just a End_Of_Month_Amount - End_Of_Month_Amount = 800 - 800 = 0) since the first record lag would return a null. You could also wrap the calculation in an isnull or other code to handle the null.
If you use Drew's query and group by EOMONTH(OrderDate) instead of just OrderDate, it produces desired results with the exception of the first record's zero revenue growth.
with cte_Monthly_Aggregates as (
SELECT
EOMONTH(OrderDate) as End_Of_Month_Date,
Sum(Amount) as End_Of_Month_Amount
FROM #OrderDetail
Group by EOMONTH(OrderDate)
)
select
End_Of_Month_Date,
DATENAME(month, End_Of_Month_Date) as Month_Name,
End_Of_Month_Amount,
End_Of_Month_Amount - LAG (End_Of_Month_Amount, 1, End_Of_Month_Amount) OVER (ORDER BY End_Of_Month_Date ASC) AS Revenue_growth
FROM cte_Monthly_Aggregates
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply