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?
CodePudding user response:
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 yesterday
ON today.id = yesterday.id and yesterday.dealdate = DATEADD("d", -1 ,DATE())
CodePudding user response:
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:
- in VBA Dates in strings are delimited by #
- Previous day assumed to be 0 on first day
- 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.
- cdate is used for robustness it may not be needed depending on the format and type of dealdate