Missing sequence and gap fill

Analytics

Missing sequence and gap fill

Hi I have a requirement to gap fill a sequence as below

 

I have a table for a class , quantity and interval of 15 mins

 

TAB1

CLASS Interval TypeQTY
CLASS A8/10/2016 11:0011
CLASS A8/10/2016 11:0025
CLASS A8/10/2016 11:1513
CLASS A8/10/2016 11:1521
CLASS A8/10/2016 11:3018
CLASS A8/10/2016 11:3023
CLASS A8/10/2016 11:4512
CLASS A8/10/2016 11:4525
CLASS A8/10/2016 12:1515
CLASS A8/10/2016 12:1525
CLASS A8/10/2016 12:3011
CLASS A8/10/2016 12:3021


TAB1 has the sequence broken at 11:45 where the 12:00 interval is missing. 

 

My desired output is 

CLASS Interval TypeQTY
CLASS A8/10/2016 11:0011
CLASS A8/10/2016 11:0025
CLASS A8/10/2016 11:1513
CLASS A8/10/2016 11:1521
CLASS A8/10/2016 11:3018
CLASS A8/10/2016 11:3023
CLASS A8/10/2016 11:4512
CLASS A8/10/2016 11:4525
CLASS A8/10/2016 12:001NULL
CLASS A8/10/2016 12:002NULL
CLASS A8/10/2016 12:1515
CLASS A8/10/2016 12:1525
CLASS A8/10/2016 12:3011
CLASS A8/10/2016 12:3021

 

I have a TAB2 which contains all the intervals maintained in a sequence for a given date. But on doing a outer join am only getting the output as 

CLASS A8/10/2016 11:4512
CLASS A8/10/2016 11:4525
NULLNULLNULLNULL
CLASS A8/10/2016 12:1515

 

Any suggestion would be helpful

  • Sequence

Accepted Solutions
Junior Contributor

Re: Missing sequence and gap fill

You need to create all Class/Type/Interval combinations first and then Left Join like this

select x.class, x.type, i.interval, t.qty
from 
 ( select distinct class, type from myTable) as x -- hopefully there's already a table with this data
cross join myIntervals as i
left join myTable as t
on t.class = x.class
and t.type = x.type
and t.interval = i.interval

There might be other solution, but you need to add more details about the actual DDL/data.

 

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: Missing sequence and gap fill

You need to create all Class/Type/Interval combinations first and then Left Join like this

select x.class, x.type, i.interval, t.qty
from 
 ( select distinct class, type from myTable) as x -- hopefully there's already a table with this data
cross join myIntervals as i
left join myTable as t
on t.class = x.class
and t.type = x.type
and t.interval = i.interval

There might be other solution, but you need to add more details about the actual DDL/data.