calculate movement based on previous row
here is some sample data from the orders table. sql server 2019
month/year orderamount
jan-2021 200
feb-2021. 0
mar-2021. 250
Apr-2021. 300
May-2021 100
trying to write a query that will take into account the order amount of the current month and the previous month and put result in a column called movement
need to use following criteria
if it is the first month or last month column should be zero
if given month value is not > 0 then 0
if given month value greater than 0 and next month greater than given month need the difference
if given month value greater than 0 and next month less than given month should be zero
given sample data above
query results should be
month/year orderamount movement
jan-2021 200 0
feb-2021. 0 0
mar-2021. 250 0
Apr-2021. 300 50
May-2021 100 0
would this be good use of lag function ?
month/year orderamount
jan-2021 200
feb-2021. 0
mar-2021. 250
Apr-2021. 300
May-2021 100
trying to write a query that will take into account the order amount of the current month and the previous month and put result in a column called movement
need to use following criteria
if it is the first month or last month column should be zero
if given month value is not > 0 then 0
if given month value greater than 0 and next month greater than given month need the difference
if given month value greater than 0 and next month less than given month should be zero
given sample data above
query results should be
month/year orderamount movement
jan-2021 200 0
feb-2021. 0 0
mar-2021. 250 0
Apr-2021. 300 50
May-2021 100 0
would this be good use of lag function ?
Last Comment
Brian Crowe
What's the datatype of "month/year"?
ASKER
it's a varchar(50)
DECLARE @Order TABLE
(
MonthYear DATETIME NOT NULL,
OrderAmount INT NOT NULL
);
INSERT @Order
(
MonthYear,
OrderAmount
)
VALUES
('20210101', 200),
('20210201', 0),
('20210301', 250),
('20210401', 300),
('20210501', 100);
WITH cteOrder AS
(
SELECT MonthYear,
OrderAmount,
LAG(OrderAmount, 1, NULL) OVER(ORDER BY MonthYear) AS PrevOrderAmount,
LEAD(OrderAmount, 1, NULL) OVER(ORDER BY MonthYear) AS NextOrderAmount
FROM @Order
)
SELECT cteOrder.MonthYear,
cteOrder.OrderAmount,
CASE
WHEN cteOrder.PrevOrderAmount IS NULL OR cteOrder.NextOrderAmount IS NULL THEN 0
WHEN cteOrder.OrderAmount = 0 THEN 0
WHEN OrderAmount > 0 AND cteOrder.NextOrderAmount > cteOrder.OrderAmount THEN cteOrder.NextOrderAmount - OrderAmount
WHEN OrderAmount > 0 AND cteOrder.NextOrderAmount < cteOrder.OrderAmount THEN 0
ELSE NULL
END AS Movement
FROM cteOrder
ORDER BY cteOrder.MonthYear
That's a dumb way to store a date. You'll have to convert them to a date to order them appropriately.
IF the months are all for the SAME YEAR, you wouldn't need to convert them to easily order them. If there's other years present, you'd have to pull out the year and sort by it first, obviously, but actually you still wouldn't have to convert to a date.
Even when the date is in varchar then SQL Server allows to use it correctly (of course, the correct format is expected).
More important is the fact that the algorithm description describes something else than expected results are showing...
Why should be the first and the last month zero? The first month is OK because we don't have data but the last month always has some data.
I would describe expected results this simple way:
If the previous month results are greater than zero and less then the current month display the diff otherwise display zero.
And appropriate code update:
More important is the fact that the algorithm description describes something else than expected results are showing...
Why should be the first and the last month zero? The first month is OK because we don't have data but the last month always has some data.
I would describe expected results this simple way:
If the previous month results are greater than zero and less then the current month display the diff otherwise display zero.
And appropriate code update:
DECLARE @Order TABLE
(
MonthYear varchar(50) NOT NULL,
OrderAmount INT NOT NULL
);
INSERT @Order
(
MonthYear,
OrderAmount
)
VALUES
('jan-2021', 200),
('feb-2021', 0),
('mar-2021', 250),
('Apr-2021', 300),
('May-2021', 100);
WITH cteOrder AS
(
SELECT MonthYear,
OrderAmount,
LAG(OrderAmount, 1, NULL) OVER(ORDER BY CONVERT(smalldatetime, REPLACE(MonthYear, '-', ' 1 '), 109)) AS PrevOrderAmount,
LEAD(OrderAmount, 1, NULL) OVER(ORDER BY CONVERT(smalldatetime, REPLACE(MonthYear, '-', ' 1 '), 109)) AS NextOrderAmount
FROM @Order
)
SELECT cteOrder.MonthYear,
cteOrder.OrderAmount,
CASE
WHEN cteOrder.PrevOrderAmount > 0 AND cteOrder.PrevOrderAmount < cteOrder.OrderAmount THEN cteOrder.OrderAmount - cteOrder.PrevOrderAmount
ELSE 0
END AS Movement
FROM cteOrder
ORDER BY CONVERT(smalldatetime, REPLACE(cteOrder.MonthYear, '-', ' 1 '), 109)
IF the months are all for the SAME YEAR, you wouldn't need to convert them to easily order them. If there's other years present, you'd have to pull out the year and sort by it first, obviously, but actually you still wouldn't have to convert to a date.@Scott Pletcher
I was curious about this, wouldn't it sort in alphabetical order meaning April, August, December, etc. ? I guess my assumption is that without it being in a datetime format (or having a column for month and a column for year) you would need to convert but if not it's a good opportunity to learn something new!
Why should be the first and the last month zero? The first month is OK because we don't have data but the last month always has some data.
if given month value greater than 0 and next month less than given month should be zero
I agree, this seems confusing at an objective level. I'm sure there's a reason you need it in this format but it seems counter-intuitive that you wouldn't have something like
month/year orderamount movement
jan-2021 200 0
feb-2021. 0 -200
mar-2021. 250 250
Apr-2021. 300 50
May-2021 100 -200
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for all the posts!
Didn't make sense to me either so I double checked
Lo and behold. I totally misunderstood
Dustin,
Your chart is absolutely the correct output
Didn't make sense to me either so I double checked
Lo and behold. I totally misunderstood
Dustin,
Your chart is absolutely the correct output
Well that simplifies the code drastically:
DECLARE @Order TABLE ( MonthYear varchar(50) NOT NULL, OrderAmount INT NULL );
INSERT INTO @Order ( MonthYear, OrderAmount ) VALUES
('jan-2021', 200), ('feb-2021', 0), ('mar-2021', 250), ('Apr-2021', 300), ('May-2021', 100);
SELECT
O.MonthYear, O.OrderAmount,
O.OrderAmount - LAG(OrderAmount, 1) OVER(ORDER BY SUBSTRING(O.MonthYear, 5, 4), CHARINDEX(LEFT(O.MonthYear, 3),
'JanFebMarAprMayJunJulAugSepOctNovDec'))
AS movement
FROM @Order O
ORDER BY SUBSTRING(O.MonthYear, 5, 4),
CHARINDEX(LEFT(O.MonthYear, 3), 'JanFebMarAprMayJunJulAugSepOctNovDec')
DECLARE @Order TABLE ( MonthYear varchar(50) NOT NULL, OrderAmount INT NULL );
INSERT INTO @Order ( MonthYear, OrderAmount ) VALUES
('jan-2021', 200), ('feb-2021', 0), ('mar-2021', 250), ('Apr-2021', 300), ('May-2021', 100);
SELECT
O.MonthYear, O.OrderAmount,
O.OrderAmount - LAG(OrderAmount, 1) OVER(ORDER BY SUBSTRING(O.MonthYear, 5, 4), CHARINDEX(LEFT(O.MonthYear, 3),
'JanFebMarAprMayJunJulAugSepOctNovDec'))
AS movement
FROM @Order O
ORDER BY SUBSTRING(O.MonthYear, 5, 4),
CHARINDEX(LEFT(O.MonthYear, 3), 'JanFebMarAprMayJunJulAugSepOctNovDec')
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
