How to Select dates when i sold nothing?-CodePudding

Assume i have a table of item_sold from 01-01-2020 to 01-01-2023 with columns product_id, product_name, quantity and date.

I want to get all the dates when i sold nothing. I am using Postgresql, please help me in this problem.

I tried withclaue and many other things but they didn't worked.

CodePudding user response：

You need some kind of calendar table containing all dates which you potentially want to report. Assuming the date range be the entire years of 2020 through 2023 inclusive, we can try the following left anti-join approach:

WITH dates AS (
    SELECT ('2020-01-01'::date   s.a) AS dt
    FROM generate_series(0, 365*4) AS s(a)
)

SELECT d.dt
FROM dates d
LEFT JOIN yourTable t
    ON t.item_sold = d.dt
WHERE t.item_sold IS NULL
ORDER BY d.dt;