Home
How to get the Previous and Next Row Data
If you’ve ever wrote reports that requires comparing numbers from previous weeks, months, quarter, and year SQL 2012 and later versions will solve your issue.
In this example, I have a table call HBClaimsRemits_DL_NW. Everyweek I bring in new data into this table. However, I would like to see if the data grew or shrank.
To do this, we use LAG and LEAD. Both of these functions requires that we use PARTITION so that we’re getting the LAG and LEAD data for the specific group of data.
LAG gets the data from the previous record. If no previous record exists, a NULL value will return.
LEAD gets the next row data. If no future record exits, a NULL value will return
Here is the script to show Previous (LAG) and Future (LEAD) records.
SELECT
Name,
RecordCount AS Current_Record_Count,
ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Previous_Record_Count,
ISNULL(LEAD(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Next_Record_Count,
CreateDate
FROM SSIS_Log.dl.event
I like to simplify the results to something more of an Indicator (KPI).
Here is the script with an Indicator field. There’s more scripting involved but the end results is easier to read.
SELECT
Name,
RecordCount,
CASE
WHEN ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) = 0 THEN ‘No Changes’
WHEN RecordCount ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0)
THEN ‘Up by ‘ + CAST(FORMAT(RecordCount – ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0), ‘##,###’) AS VARCHAR(20))
END AS Indicator,
CreateDate
FROM SSIS_Log.dl.event
