Create table with missing and present dates from fact and dim tables SQL query

Given fact table with some dates and dim table that has all dates , need to create a table like below with Missing and present dates:

Fact Table



 2018-01-03 …

From above two tables, create this below table

Type                 start_date     end_date
Missing             2018-01-01   2018-01-03
Present             2018-01-04   2018-01-05
Missing             2018-01-06   2018-01-22    

Here's what I have till now:

with a as (
(select date_dt as date_col,
       'missing' as type
       from dimdate)
union all 
 (select fact_date as date_col,
         'present' as type
         from fact_table)
   b as (    
select date_col,
       row_number() over (order by date_col asc) as seq
   from a

select type, 
min(date_col) as start_date, max(date_col) as end_date
from b
group by dateadd(d, -seq, date_col),type

This is giving me like this:

Type                 start_date     end_date
Missing             2018-01-01   2018-01-04
Present             2018-01-04   2018-01-05
Missing             2018-01-05   2018-01-22    

In Missing rows, I should have 2018-01-03 instead of 2018-01-04 as end_date in first row.

Can someone help me in getting this query right?

Read more here:

Content Attribution

This content was originally published by harshini at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: