Is there any way I could use Alternate for Lead() and Lag() Functions in SQL Server 2008?

Question

I've table where I need to calculate difference between row to one underneath it and get the resultant to xml. it's daily task so i need it is kind of recursive task.

Structure for my current table is as below :

CREATE TABLE #Temp
(

    , CurrentDateTime        DateTime
    , ID                     INT
    , ThisYearToDateTotal    INT
    , ThisYearToDateCBT      INT
    , ThisYearToDateManual   INT
    , ThisYearToDateScanned  INT
    , InProcess              INT
    , InputRequired          INT
)`

So far I've written the code as below :

SELECT 
  Today_CurrentDateTime
, Today_Total
, Today_CBT
, Today_Manual
, Today_Scanned
, Today_InProcess
, Today_InputRequired
, Yesterday_Total
, Yesterday_CBT
, Yesterday_Manual
, Yesterday_Scanned
, Yesterday_InProcess
, Yesterday_InputRequired
, (TD.Today_Total         - YD.Yesterday_Total)          AS Diff_in_Total 
, (TD.Today_CBT           - YD.Yesterday_CBT)            AS Diff_in_CBT
, (TD.Today_Manual        - YD.Yesterday_Manual)         AS Diff_in_Manual
, (TD.Today_Scanned       - YD.Yesterday_Scanned)        AS Diff_in_Scanned   
, (TD.Today_InProcess     - YD.Yesterday_InProcess)      AS Diff_in_InProcess
, (TD.Today_InputRequired - YD.Yesterday_InputRequired)  AS Diff_in_InputRequired
 FROM #YesterdayData AS YD
INNER JOIN #TodayData AS TD ON TD.Today_ID = YD.Yesterday_ID

and getting the output as below :

Now I've a restriction here that I can't create another permanent table and that's why I can't calculate difference for a each day for throughout a week.

Any Help ?

Answer 1

If the dates are input in order of the identity field ID then you can inner join with the same #temp table ON previous ID.

CREATE TABLE #Temp
(    CurrentDateTime        DateTime
    , ID                     INT 
    , ThisYearToDateTotal    INT
    , ThisYearToDateCBT      INT
    , ThisYearToDateManual   INT
    , ThisYearToDateScanned  INT
    , InProcess              INT
    , InputRequired          INT
)

INSERT INTO #Temp VALUES
 ('2017-11-14 07:50:25.230', 1, 400000, 50000, 20000, 30000, 1000, 700)
,('2017-11-15 07:50:25.230', 2, 460000, 53000, 26000, 38000, 2000, 1400)
,('2017-11-16 07:53:01.943', 3, 469692, 53904, 26755, 389033, 2026, 1489)
,('2017-11-17 07:53:01.943', 4, 469692, 53904, 26755, 389033, 2026, 1489)

DELETE FROM #Temp WHERE ID = 3

SELECT T.CurrentDateTime
     , TPrev.ThisYearToDateTotal - T.ThisYearToDateTotal [Total Diff]
     , TPrev.ThisYearToDateCBT - T.ThisYearToDateCBT [ThisYearToDateCBT Diff]
     , TPrev.ThisYearToDateManual - T.ThisYearToDateManual [ThisYearToDateManual Diff]
     , TPrev.ThisYearToDateScanned - T.ThisYearToDateScanned [ThisYearToDateScanned Diff]
     , TPrev.InProcess - T.InProcess [InProcess Diff]
     , TPrev.InputRequired - T.InputRequired [InputRequired Diff]
  FROM #Temp AS T LEFT JOIN #Temp AS TPrev ON TPrev.ID = (SELECT MAX(T2.ID)
                                                             FROM #Temp T2
                                                            WHERE T2.ID > T.ID)
ORDER BY T.ID

--DROP TABLE #Temp

Answer 2

It's quite easy to mimic LAG and LEAD using sub queries.

For the #Temp table you have in your question, to get ThisYearToDateTotal value of the previous or next row (order by CurrentDateTime).

Here is a simple example:

SELECT ID,
       CurrentDateTime,
       ThisYearToDateTotal,
       (
           SELECT TOP 1 ThisYearToDateTotal
           FROM #Temp as tLag
           WHERE tLag.ID = tMain.Id -- partition by
           AND tLag.CurrentDateTime < tMain.CurrentDateTime
           ORDER BY CurrentDateTime DESC 
       ) As Lag_ThisYearToDateTotal,
       (
           SELECT TOP 1 ThisYearToDateTotal
           FROM #Temp as tLead
           WHERE tLead.ID = tMain.Id -- partition by
           AND tLead.CurrentDateTime > tMain.CurrentDateTime
           ORDER BY CurrentDateTime 
       ) As Lead_ThisYearToDateTotal
 FROM #Temp as tMain