troubleshooting Question

calculate movement based on previous row

Avatar of johnnyg123
johnnyg123Flag for United States of America asked on
Microsoft SQL Server
11 Comments1 Solution12 ViewsLast Modified:
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 ?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros