Suppose there exist a table called: RandomPriceSummary , which has the date ranging from Wed Oct 01 2022 00:00:00 GMT 0100 to Wed Oct 03 2022 00:00:00 GMT 0100, and period ranging from 1-3 and cost as shown below:

In the above table, how can we check all of the missing dates and missing periods?

For example, we need a query WHERE SETTLEMENT_DATE BETWEEN TIMESTAMP '10-01-2022' AND TIMESTAMP '10-03-2022' which has a missing period ranging from 1-3. So the expected answer should return something along the lines of :

CodePudding user response：

We can use the following calendar table left anti-join approach:

SELECT d.dt, p.period
FROM (SELECT date_trunc('day', dd)::date AS dt
      FROM generate_series(
          '2022-01-01'::timestamp,
          '2022-12-31'::timestamp,
          '1 day'::interval) dd
) d
CROSS JOIN (SELECT 1 AS period UNION ALL SELECT 2 UNION ALL SELECT 3) p
LEFT JOIN RandomPriceSummary t
    ON t.date::date = d.dt AND t.period = p.perio
WHERE d.dt BETWEEN '2022-10-01'::date AND '2022-10-03'::date AND
      t.date IS NULL
ORDER BY d.dt, p.period;