Is there a way to have SQL SELECT a column with a different date?

I’m trying to pull a pretty simple report that reviews the last day’s data. It looks similar to this:

SELECT Name, Date, Count
FROM dbo.X
WHERE Date BETWEEN DATEADD(DD, -1, GETDATE()) AND DATEADD(DD, 0, GETDATE())

This works fine, but I’ve been asked to have a column present that shows day before count as well for comparison. I’m not really sure how to do this since the datetime condition is already specifying the last day. Is there a way to make this work so the select list would look like this for example?

SELECT Name, Date, YesterdaysCount, Count

Edit: Sample Data:

Name    Date    Count
a   6/22/2020   1
b   6/22/2020   2
c   6/22/2020   3
d   6/22/2020   4
e   6/22/2020   5
a   6/21/2020   2
b   6/21/2020   4
c   6/21/2020   6
d   6/21/2020   8
e   6/21/2020   10

Desired Results:

Name    Date    YesterdayCount  Count
a   6/22/2020   2   1
b   6/22/2020   4   2
c   6/22/2020   6   3
d   6/22/2020   8   4
e   6/22/2020   10  5

Answer

This should work if you don’t have a time component. If you do, just change the “Y.[Date] =” to a BETWEEN as with the first part.

SELECT [Name], [Date], [Count], 
   (SELECT SUM(Y.[Count]) FROM dbo.X AS Y WHERE Y.[Name] = X.[Name] AND Y.[Date] = DATEADD(DAY, -1, X.[DATE]) AS YesterdaysCount
FROM dbo.X
WHERE [Date] BETWEEN DATEADD(DD,-1,GETDATE()) AND DATEADD(DD, 0, GETDATE())