How to pivot between current day and previous days

Question

I'm using Access SQL

I have data that looks like this:-

dealdate	value
17-Apr-22	267
18-Apr-22	274

I'm trying to make the data look like this (there will only ever be 2 dates) So, assuming that today is 18th April:-

CurrentDay	PreviousDay	Difference
274	267	7

How can I do this with Access SQL?

Answer 1

Using 3 calculated variables and the Query Designer:

PreviousDay: Nz(DLookUp("dealvalue","Table1","dealdate = #" & DateAdd("d",-1,CDate([dealdate])) & "#"),0)

'Full SQL


SELECT Table1.dealvalue AS CurrentDay, Nz(DLookUp("dealvalue","Table1","dealdate = #" & DateAdd("d",-1,CDate([dealdate])) & "#"),0) AS PreviousDay, [dealvalue]-[PreviousDay] AS Difference
FROM Table1;

giving:

---

| CurrentDay | PreviousDay | Difference |

| 267 | 0 | 267 |

| 274 | 267 | 7 |

Notes:

1. in VBA Dates in strings are delimited by #
2. Previous day assumed to be 0 on first day
3. you can replace the dlookup function with subquerys for more portability, but it takes me an hour to write my subquerys so I used dlookup.
4. cdate is used for robustness it may not be needed depending on the format and type of dealdate

Answer 2

Pivot would be a big mistake -- you can just use a where and a join --- looks something like this:

SELECT today.value as CurrentDay,
       yesterday.value as PreviousDay,
       today.value - yesterday.value as Difference
FROM sometableyoudidnotname AS today
WHERE today.dealdate = DATE()
LEFT JOIN sometableyoudidnotname as yesterday
            ON today.id = yesterday.id and yesterday.dealdate = DATEADD("d", -1 ,DATE())