SQL Best way to JOIN day over day data into same row including new and deleted IDs
I initially thought this would be fairly simple but for some reason I’m struggling with it.
If I have a table that looks like this:
table1 Date ID Quantity 2/21 1 100 2/21 2 500 2/21 3 200 2/20 2 600 2/20 3 400 2/20 5 2000
And I want to join this data to look like:
ID prev_date prev_quantity curr_date curr_quantity 1 2/20 0 2/21 100 2 2/20 600 2/21 500 3 2/20 400 2/21 200 5 2/20 2000 2/21 0
The tricky part being ID 1 doesn’t have an entry for previous day (so make 0) and ID 5 doesnt have an entry for current day (make that 0 as well)
What is the best way to do this? Thanks in advance!!
3 Solutions collect form web for “SQL Best way to JOIN day over day data into same row including new and deleted IDs”
This may help u,,,,,
SELECT ISNULL(T.ID,T1.ID), ISNULL(T.DATE,GETDATE()) AS 'CurrDate', ISNULL(T.Quantity,0) AS 'CurrQty', ISNULL(T1.DATE,GETDATE()-1) AS 'PrevDate' , ISNULL(T1.Quantity,0) AS 'PrevQty' FROM ( SELECT T.ID, T.Quantity, T.DATE FROM @TABLE T WHERE T.DATE = CONVERT(VARCHAR,GETDATE(),100) )T FULL OUTER JOIN ( SELECT T1.ID, T1.Quantity, MAX(T1.DATE) AS [DATE] FROM @TABLE T1 WHERE T1.DATE <> CONVERT(VARCHAR,GETDATE(),100) GROUP BY T1.ID,T1.Quantity ) T1 ON T.ID = T1.ID
SELECT curr.ID, ISNULL(Prev.date, DATEADD(DD, -1, curr.Date)) AS Prev_Date, ISNULL(prev.Quantity, 0) AS Prev_Quantity, ISNULL(curr.date, DATEADD(DD, 1, prev.Date)) AS Curr_Date, ISNULL(curr.Quantity, 0) AS Curr_Quantity FROM table1 curr FULL OUTER JOIN table1 prev ON curr.ID = prev.ID AND prev.Date = DATEADD(DD, -1, curr.Date)
You could solve the gaps problem using a rank. Also instead of ISNULL use a UNION:
SELECT RANK() OVER (ORDER BY ID,DATE) rank, ID, date, quantity INTO temp1 FROM ( SELECT date, ID, quantity FROM table1 UNION ALL SELECT dateadd(day,-1,MIN(date)) date, ID, 0 quantity FROM table1 GROUP BY ID UNION ALL SELECT dateadd(day,1,MAX(date)) date, ID, 0 quantity FROM table1 GROUP BY ID ) AS U; SELECT p.ID, p.date prev_date, p.quantity prev_quantity, c.date curr_date, c.quantity curr_quantity FROM temp1 p INNER JOIN temp1 c ON c.rank = p.rank + 1 AND c.ID = p.ID