Hi
I need help to write sql script to get the tab1_result
sample data is in tab1 and result needed in tab1_result
with tab1 as
(
select 101 as id, 'abc' as area,'2021-01-07' as strt_dt,'2021-01-07' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area, '2021-01-07' as strt_dt, '2021-01-08' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'mno' as area,'2021-01-08' as strt_dt,'2021-01-15' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt
union
select 101 as id, 'tt' as area,'2021-01-15' as strt_dt, '2021-01-17' as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)select * from tab1
with tab1_result as
(
select 101 as id, 'abc' as area,'2021-01-07' as strt_dt,'2021-01-07' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area, '2021-01-07' as strt_dt, '2021-01-08' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area, '2021-01-08' as strt_dt, '2021-01-08' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, 'mno' as area,'2021-01-08' as strt_dt,'2021-01-15' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt
union
select 101 as id, 'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, 'tt' as area,'2021-01-15' as strt_dt, '2021-01-17' as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, 'tt' as area,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt
union
select 101 as id, 'tt' as area,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)select * from tab1_result

