How can I create a new field value that shows a difference in value from 2 fields? They are numerical values and may vary each month. I only want to show the difference in value if there has been a change in value from the latest month to the previous month.
(it only needs to report on the previous month not every month in the database)
e.g.
table sales
field - amount - integer value
field - monthPeriod - values increment every month, e.g. 1,2,3,4,5,6,7,etc
difference - in the amount from the previous month.
I need to create a new column called difference and subtract the latest month value from the previous month and only report where value is >0, but I'm not sure how to format it.
CREATE TABLE #t
(
aYear int NOT NULL
,aMonth int NOT NULL
,amount decimal(19,5) NOT NULL
,PRIMARY KEY (aYear, aMonth)
);
INSERT INTO #t
VALUES (2024, 1, 10.22)
,(2024, 2, 10.22)
,(2024, 3, 10.33)
,(2024, 4, 10.33)
,(2024, 5, 10.33)
,(2024, 6, 11.32);
WITH Diffs
AS
(
SELECT aYear, aMonth
,amount - ISNULL(LAG(amount) OVER (ORDER BY aYear, aMonth), amount) AS aDifference
FROM #t
)
SELECT aYear, aMonth, aDifference
FROM Diffs
WHERE aDifference <> 0
ORDER BY aYear, aMonth;
Just a little trick... you don't need the ISNULL here. There is a "Default" to LAG. Here's the syntax from BOL.
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
If we make the change in your good code, it will look like this...
WITH Diffs
AS
(
SELECT aYear, aMonth
,amount - LAG(amount,1,amount) OVER (ORDER BY aYear, aMonth) AS aDifference
FROM #t
)
SELECT aYear, aMonth, aDifference
FROM Diffs
WHERE aDifference <> 0
ORDER BY aYear, aMonth;
Not to worry... I only remember that there are defaults. I don't use LEAD or LAG often enough to have the syntax memorized and so I have to look it up every time.
Thank you for the feedback. Sometimes, a little "Divide'n'Conquer" in the code is your best friend. For example, storing an interim answer in a Temp Table is NOT the end of the world, can make code much easier to write, and can make the code a whole lot faster (although it's NOT a panacea).
My point here is that too many people have difficulty try to fit something in when an interim Temp Table will do the trick.
To summarize, always remember that "Set Based" < > "All on one query".
I'm just struggling with getting the query to pick up these fields in the second select statement: aYear, aMonth, aDifference.
I can't get them to bind to the query. It was fine when building the first select query, but the 2nd select statement seems to be outside the scope and I get a 'could not be bound' error.
