Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on 

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 ?
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

What's the datatype of "month/year"?
Avatar of johnnyg123
johnnyg123
Flag of United States of America image

ASKER

it's a varchar(50)
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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

Open in new window

Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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.
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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:
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)

Open in new window

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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of johnnyg123
johnnyg123
Flag of United States of America image

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
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')


Microsoft SQL Server
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo