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

Fact_date                                
2018-01-04                               
2018-01-05                             
2018-01-22    
                

dim_date

 date_dt
 2018-01-01
 2018-01-02
 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,
       type,
       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: https://stackoverflow.com/questions/64939310/create-table-with-missing-and-present-dates-from-fact-and-dim-tables-sql-query

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: