CODEWITHSUNDEEP
sqlamazon-redshiftparaccel
Teja
Teja

Reputation: 13544

Get all the missing dates in a sequence of dates

I am trying to get all the missing dates in a sequence of dates which are in an ascending order. How can I do it using a simple sql without using any functions or udfs.

Input :-

2016-09-01
2016-09-02
2016-09-05
2016-09-10

Output :-

2016-09-03
2016-09-04
2016-09-06
2016-09-07
2016-09-08
2016-09-09

What I have tried?

  select start, stop 
    from
       (
          select m.x + 1 as start,
                 (select min(x) - 1 from X as x where x.x > m.x) as stop
            from X as m
           left outer join X as r
              on m.x = r.x - 1
           where r.x is null
        ) as x
  where stop is not null;

Upvotes: 0

Views: 366

Answers (1)

Randy
Randy

Reputation: 16673

  1. create a new table and insert 365 rows numbered 1-365 (alternately use a table that already has more than 365 rows and use rownum or similar construct to get unique integers)

  2. Convert your dates to integers (in Oracle use something like TO_CHAR( mydate, 'ddd' ))

  3. join these two list together in your query to find the appropriate set (overlap, missing etc.)

  4. convert back to dates

Upvotes: -1

Related Questions