TSQL – Compare two columns with self join – Calculate the difference between yesterdays total’s and today’s total

I’m trying to calculate the difference between today’s and yesterday’s totals on a column. If a self join isn’t the best way to do that, that is fine, whichever will give me the result I’m after should be ok.

Requirements:

1. Only compare the last 2 days worth of data, even though the table will have multiple days worth of data, only one entry per day though.
2. Calculate the difference between yesterdays totals and today’s totals for the column.

Problem

The code below returns a zero and I’m not understanding why.

Why won’t it calculate and what should I do to meet the requirements please?

---

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

CREATE TABLE #t1 (
 countID UNIQUEIDENTIFIER
,empCount VARCHAR(20)
,CountDate DATETIME
)

INSERT INTO #t1 (
    countID
  , empCount
  , CountDate
)
VALUES
     (NEWID(),'123000', GETDATE()) 
    ,(NEWID(),'100', '20200813')
    ,(NEWID(),'100', '20200810')

SELECT 
    today.countID
  , (CAST(today.empCount AS INT)) - (CAST(yesterday.empCount AS INT)) AS CountDiff
  , today.empCount
  , today.CountDate
FROM #t1 AS today
INNER JOIN #t1 AS yesterday ON today.countID = yesterday.countID
                                AND yesterday.CountDate > (SELECT dateadd(day,datediff(day,2,GETDATE()),0))

Answer

I think you want lag():

select t.*,
       (empcount - lag(empcount) over (order by countdate)) as diff
from #t1 t;

If you want only the last two days, then:

select top (1) t.*
from (select t.*,
             (empcount - lag(empcount) over (order by countdate)) as diff
      from #t1 t
     ) t
order by countdate desc;

Note: This interprets “yesterday” as the last two days in the table. If you actually want today and yesterday specifically, then you can use a where clause:

select top (1) t.*
from (select t.*,
             (empcount - lag(empcount) over (order by countdate)) as diff
      from #t1 t
      where countdate >= dateadd(day, -1, convert(date, getdate()))
     ) t
order by countdate desc;