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.
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.
Hi
Hope this helps
WITH MonthlySales AS (
SELECT
amount,
monthPeriod,
LAG(amount) OVER (ORDER BY monthPeriod) AS PreviousAmount
FROM
sales
)
SELECT
amount,
monthPeriod,
(amount - PreviousAmount) AS difference
FROM
MonthlySales
WHERE
PreviousAmount IS NOT NULL
AND (amount - PreviousAmount) > 0;
I understand its one query and its a CTE etc.
But I can't get it to work with all our joins.
Here's what I mean...
so for this sql below:
/*without CTE*/
SELECT TOP (100) PERCENT PET.PAYELEMENTTYPENAME, P.SURNAME, R.PAYPERIOD, R.PAYYEAR, M.WORKSROLLNUMBER, A.Amount,
LAG(A.AMOUNT,1) OVER (PARTITION BY M.WORKSROLLNUMBER ORDER BY R.PAYPERIOD, M.WORKSROLLNUMBER)
AS PrevMonth
FROM dbo.PAYROLLMEMBER AS M INNER JOIN
dbo.FOLDER AS F ON M.PAYROLLMEMBERID = F.PAYROLLMEMBERID INNER JOIN
dbo.PERSON AS P ON F.PERSONID = P.PERSONID INNER JOIN
dbo.PAYROLLTRANSACTION AS T ON M.PAYROLLMEMBERID = T.PAYROLLMEMBERID INNER JOIN
dbo.PAYROLLPAYRUN AS R ON T.PAYROLLPAYRUNID = R.PAYROLLPAYRUNID INNER JOIN
dbo.PAYROLLACCENTRY AS A ON T.PAYROLLTRANSACTIONID = A.PAYROLLTRANSACTIONID INNER JOIN
dbo.PAYELEMENTTYPE AS PET ON A.PAYELEMENTTYPEID = PET.PAYELEMENTTYPEID
WHERE (PET.PAYELEMENTTYPENAME LIKE 'WESTFIELD%') AND (R.PAYYEAR = 2024)
WITH myDiff AS (
SELECT TOP (100) PERCENT PET.PAYELEMENTTYPENAME, P.SURNAME, R.PAYPERIOD, R.PAYYEAR, M.WORKSROLLNUMBER, A.Amount,
LAG(A.AMOUNT,1) OVER (PARTITION BY M.WORKSROLLNUMBER ORDER BY R.PAYPERIOD, M.WORKSROLLNUMBER)
AS PrevMonth
FROM dbo.PAYROLLMEMBER AS M INNER JOIN
dbo.FOLDER AS F ON M.PAYROLLMEMBERID = F.PAYROLLMEMBERID INNER JOIN
dbo.PERSON AS P ON F.PERSONID = P.PERSONID INNER JOIN
dbo.PAYROLLTRANSACTION AS T ON M.PAYROLLMEMBERID = T.PAYROLLMEMBERID INNER JOIN
dbo.PAYROLLPAYRUN AS R ON T.PAYROLLPAYRUNID = R.PAYROLLPAYRUNID INNER JOIN
dbo.PAYROLLACCENTRY AS A ON T.PAYROLLTRANSACTIONID = A.PAYROLLTRANSACTIONID INNER JOIN
dbo.PAYELEMENTTYPE AS PET ON A.PAYELEMENTTYPEID = PET.PAYELEMENTTYPEID
WHERE (PET.PAYELEMENTTYPENAME LIKE 'WESTFIELD%') AND (R.PAYYEAR = 2024)
)
SELECT
R.PAYPERIOD,
PET.PAYELEMENTTYPENAME,
A.Amount,
prevMonth
FROM
myDiff;
I get this error:
maybe its some of the other tables that are outside the scope and I need to bring them in some how?
