Create a new field value that shows a difference in value from 2 fields

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.

amount | month |
10.22 | 1
10.22 | 2
10.33 | 3
10.33 | 4
10.33 | 5
11.32 | 6

so I would expect to see:

amount | month | difference
10.33 | 3 | 0.11
11.32 | 6 | 0.99

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.

Thanks

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;
1 Like

@Ifor ,

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;

Results are the same...
image

Thanks. I keep forgetting the defaults on LEAD and LAG.

1 Like

thanks @Ifor and @JeffModen
I can see your code works and looks good. I'm just trying to get it to work with our joins around the tables.

1 Like

@Ifor

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. :rofl:

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". :grin:

1 Like

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.

It is all one query. Diffs is a CTE.

@Barnsley

In other words, the second SELECT cannot run without the first because the first makes a temporary object known as a CTE (Common Table Expression) called "DIFFS" and that's what the second SELECT uses. You have to run the whole thing (both SELECTs) for it to work.