CODEWITHSUNDEEP
sqlt-sqlsql-server-2014
JM1
JM1

Reputation: 1715

TSQL - Calculate difference between values from yesterday to today, SELF JOIN question

I have pieced together code from various answers online to get the result I want but, I don't understand why it's working and I would like to know what the JOIN is actually doing where it says RowNum + 1.

The original problem is to calculate the percentage difference between a value from yesterday to today. I'm a little fuzzy on Self Joins, but I do understand self join. When I add the RowNum column, that confuses me.

Question

What is T2.RowNum = T1.RowNum + 1 doing in the self join please?

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

CREATE TABLE #T1 (
ProductTotal int
,CountDate date
)

INSERT INTO #t1
VALUES 
       (893911,'20200815')
      ,(888970,'20200816')
      ,(899999,'20200817')
      

WITH cte AS (
SELECT 
 ROW_NUMBER() OVER(ORDER BY CountDate) AS RowNum
,ProductTotal
,CountDate
FROM #t1
WHERE   CountDate > CAST(GETDATE () - 2 AS DATE)
)
SELECT 
    t1.RowNum
   ,t1.ProductTotal   
   ,CAST(((t1.ProductTotal - t2.ProductTotal) * 1.0 / t2.ProductTotal) * 100 AS DECIMAL(10,2))  AS ProductDiff
   ,t1.CountDate
FROM cte AS t1
LEFT JOIN cte t2 ON T2.RowNum = T1.RowNum + 1

Upvotes: 0

Views: 461

Answers (2)

Thom A
Thom A

Reputation: 95590

Note, that, as I commented, I completely agree with Gordon here, and that LAG (or LEAD) is the right answer here. To explain what you ask in the comment "I don't understand how T2.RowNum = T1.RowNum + 1 works":

A JOIN returns rows where the expression in the ON is true. As you have an INNER JOIN then only rows from both sides of the JOIN where the expression evaluates to True are displayed. For a LEFT JOIN any prior previously returned would not be "lost". (There are other types of joins too.)

For T2.RowNum = T1.RowNum + 1 this is basic maths. 2 is matched to 1 (1+1), 3 is match to 2 (2+1)... 100 is matched to 99 (99 + 1). So the data from T1 is matched to the row "after" in terms of the ROW_NUMBER order defined within the CTE. In this case, that would be the row with the "next" value for CountDate in ascending order.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Assuming you have values on each day, a better approach uses lag():

SELECT ProductTotal, CountDate,
       (ProductTotal - prev_ProductTotal) * 1.0 / ProductTotal
FROM (SELECT t.*, 
             LAG(ProductTotal) OVER (ORDER BY CountDate) as prev_ProductTotal
      FROM #t1 t
     ) t
WHERE CountDate > CAST(GETDATE () - 1 AS DATE)

Upvotes: 5

Related Questions