I have a log table "table1" with values appended every day and need to find "column3" values and dates from "column1", for which the data wasn’t recorded. For example, my table looks like this:
column1 | column2 | column3 |
---|---|---|
2022-07-14 | 274,5 | markus |
2022-07-14 | 251,2 | tess |
2022-07-14 | 162,6 | mike |
2022-07-15 | 286,9 | markus |
2022-07-15 | 254,8 | tess |
2022-07-16 | 289,1 | markus |
2022-07-17 | 295,2 | markus |
2022-07-17 | 260,0 | tess |
2022-07-17 | 182,3 | mike |
Everything is ok with column3 = 'markus'
, but I need to get something like this as output:
column1 | column3 |
---|---|
2022-07-15 | mike |
2022-07-16 | tess |
2022-07-16 | mike |
2
Answers
One way of addressing this problem is by:
Output:
Check the demo here.
If gaps can be found among your dates, you need to use generate_series with boundary dates to generate the corresponding calendar in the first cte:
Check the demo here.
At first, you must build a time base for the whole period, then cross-join it with distinct of column3 to create all possibilities, then subtract it from your base data as follows: