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 ?
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.