I have a table with two columns ID and Date say like with below data. For a given range say like from 2022-09-01 to 2022-09-10 I want to return the missing dates for respective ID's along with ID value, I want data to be returned as mentioned in Expected output. How can I achieve this

Data inside table:

Expected Output:

CodePudding user response：

I wrote sample query for you:

CREATE TABLE test1 (
    id int4 NULL,
    pdate date NULL
);

INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-01');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-07');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-08');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-09');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-01');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-02');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-03');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-04');


select t1.id, t1.datelist from (
    select t.id, generate_series(t.startdate, t.enddate, '1 day')::date as datelist from (
        select distinct id, '2022-09-01'::date as startdate, '2022-09-10'::date as enddate from test1 
    ) t 
) t1 
left join test1 t2 on t2.pdate = t1.datelist and t1.id = t2.id 
where t2.pdate is null 


Result: 

id  datelist
1   2022-09-02
1   2022-09-03
1   2022-09-04
1   2022-09-05
1   2022-09-06
1   2022-09-10
2   2022-09-05
2   2022-09-06
2   2022-09-07
2   2022-09-08
2   2022-09-09
2   2022-09-10