input needed in sql design

Database
Enthusiast

input needed in sql design

Hello,

I am trying to manipulate below source data a particular o/p using OLAP functions.
Below data is there is one of the staging table.

And need to populate another fact table from this data.

Source data

Title_id Network_id Start_Dt End_dt Play_day_hrs Runs_in_playdays
1 1 10/11/09 11:00 AM 10/11/09 01:00 PM 24 2
1 1 10/20/09 11:00 AM 10/20/09 01:00 PM 24 2
1 1 10/20/09 09:00 PM 10/20/09 11:00 PM 24 2
2 11 10/11/09 11:00 AM 10/11/09 01:00 PM 24 2
2 11 10/20/09 11:00 AM 10/20/09 01:00 PM 24 2
2 11 10/20/09 09:00 PM 10/20/09 11:00 PM 24 2
2 11 10/21/09 07:00 AM 10/21/09 09:00 AM 24 2
3 11 10/11/09 11:00 AM 10/11/09 01:00 PM 48 2
3 11 10/20/09 11:00 AM 10/20/09 01:00 PM 48 2
3 11 10/20/09 09:00 PM 10/20/09 11:00 PM 48 2
3 11 10/21/09 07:00 AM 10/21/09 09:00 AM 48 2

Out Put

Title_id Network_id Start_Dt End_dt SLOT Flag
1 1 10/11/09 11:00 AM 10/11/09 01:00 PM Y
1 1 10/20/09 11:00 AM 10/20/09 01:00 PM Y
1 1 10/20/09 09:00 PM 10/20/09 11:00 PM N
2 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
2 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y
2 11 10/20/09 09:00 PM 10/20/09 11:00 PM N
2 11 10/21/09 07:00 AM 10/21/09 09:00 AM N
3 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
3 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y
3 11 10/20/09 09:00 PM 10/20/09 11:00 PM N
3 11 10/21/09 07:00 AM 10/21/09 09:00 AM N

So from source I am supposed to take min(start_dt) record for combination of network and title combination.
So in first go I will have below records in fact table from staging.

Title_id Network_id Start_Dt End_dt Slot
1 1 10/11/09 11:00 AM 10/11/09 01:00 PM Y
2 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
3 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y

Now in second go I want to take second min (start_dt) from stage for corresponding network and title in fact and will compare stage air date and fact date.
if airing from staging table is in next 24 or 48 hrs ,window depending upon play_day_hrs, then slot flag will be N else Y.

So in second go fact data will look like.

Title_id Network_id Start_Dt End_dt Slot
1 1 10/11/09 11:00 AM 10/11/09 01:00 PM Y
2 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
3 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
1 1 10/20/09 11:00 AM 10/20/09 01:00 PM Y
2 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y
3 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y

Now in third go I want to compare max(start date) from fact table and third min airing from stage.
So o/p of third go will be.

Title_id Network_id Start_Dt End_dt Slot
1 1 10/11/09 11:00 AM 10/11/09 01:00 PM Y
2 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
3 11 10/11/09 11:00 AM 10/11/09 01:00 PM Y
1 1 10/20/09 11:00 AM 10/20/09 01:00 PM Y
2 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y
3 11 10/20/09 11:00 AM 10/20/09 01:00 PM Y
1 1 10/20/09 09:00 PM 10/20/09 11:00 PM N
2 11 10/20/09 09:00 PM 10/20/09 11:00 PM N
3 11 10/20/09 09:00 PM 10/20/09 11:00 PM N

Now this I can implement using informatica but I need to implement dynamic lookup which will be costly and slow from performance perspective.
Also want to avoid informatica as data volume is pretty high.

Second option is to write a unix script which will generate dynamic bteq script and can pass rank as an variable from script to bteq at run time.
but I want to have this solution at last.

I am trying to implement this with the help of some intermediate staging table by introducing rank in that table.

but after inserting first go I am not able to think for second.
Basically here I want to implementing something like sliding window with floating reference..

any idea?

Regards,
Subhash
1 REPLY
Junior Contributor

Re: input needed in sql design

Hi Subhash,
there's no need for any kind of loop, you just compare the "current" start_dt to the "previous" end_dt:

CASE WHEN (start_dt --current
- MIN(end_dt) --previous
OVER (PARTITION BY title_id, network_id
ORDER BY start_dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) DAY(4) TO SECOND <= CAST(play_day_hrs AS INTERVAL HOUR)
THEN 'N' ELSE 'Y'
END

Dieter