Loop vs Temp Table 2

[Naith]

Hey fellas,

I'm currently in discussion with my team about how best to go at this business logic.

This is a television media environment, where there are three schedules per channel. In order of priority; schedule A, B, and C.

Let's say the user is interested in looking at a weeks worth of scheduling for a particular channel, they only want to see one schedule made up from A, B, and C, depending on whether they have any data.

So, if A has data on Monday and Tuesday, B on Tuesday through Thursday, and C for the whole week, the dataset should go:

Monday (from A)
Tuesday (from A)
Wednesday (from B)
Thursday (from B)
Friday (from C)

The week is just an example. The real period is specified at runtime through a date range, which could be over a month long.

How would you approach this?

 

[taupirho]

Maybe this will get you started

SQL> select code,dte,to_char(dte,'DY')
2 from tom;

C DTE TO_
- --------- ---
A 02-MAR-09 MON
C 02-MAR-09 MON
C 03-MAR-09 TUE
A 03-MAR-09 TUE
B 03-MAR-09 TUE
B 04-MAR-09 WED
C 04-MAR-09 WED
C 05-MAR-09 THU
C 06-MAR-09 FRI
B 05-MAR-09 THU

10 rows selected.

1 select code,dte,to_char(dte,'DY')
2 from
3 (
4 select code,dte,count(*) over(partition by dte order by dte,code) cnt
5 from tom
6 )
7* where cnt = 1
SQL> /

C DTE TO_
- --------- ---
A 02-MAR-09 MON
A 03-MAR-09 TUE
B 04-MAR-09 WED
B 05-MAR-09 THU
C 06-MAR-09 FRI




In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Naith,

Can you please post the following:[ul][li]A sample set of input. (Taupirho was very generous to assert some data of his own without much guidance from your original post.)[/li][li]Sample output that you want to see from your input (including different schedule requests from different requesters).[/li][li]Code attempts that you have tried already, along with any syntax, run-time, or logic errors that you encountered.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Naith]

Thanks for your feedback. Okay guys - brace yourselves.

This is a rudimentary, over simplified example of data.

Schedule A (PRES)
----------

DATE        PROGRAMME          SCHEDULE
01/01/09    JAMES BOND         A
01/01/09    REMINGTON STEELE   A
02/01/09    JAMES BOND         A
03/01/09    AUSTIN POWERS      A
05/01/09    THE SAINT          A

Schedule B (PLAN)
----------

DATE        PROGRAMME          SCHEDULE
01/01/09    JAMES BOND         B
01/01/09    REMINGTON STEELE   B
02/01/09    JAMES BOND         B
03/01/09    AUSTIN POWERS      B
04/01/09    MAGNUM             B

The idea, in this high level, basic example, being to take everything from A on the days where there is an entry, and where there isn't, go to B.

These are the different approaches:

Use a temporary table to store all the scheduled dates between the parameter dates, insert everything from A, delete from the temp table where the dates exist in A, and then repeat for B, joining to the remaining dates in the temp table.

    --compile list of dates within parameter range
    delete from tempdates; --(temp table: (txdate))
    
    insert into tempdates (txdate)
    select distinct bat_utilities.battodate(e.start_time,
                                            c.channel_uid,
                                            c.time_zone_uid,
                                            c.base_day_definition_uid) 
    from    events    e,
            schedules sch,
            channels  c
    where   sch.channel_uid                = c.channel_uid
    and     e.schedule_uid                 = sch.schedule_uid
    and     e.start_time 		         BETWEEN vStartDateNum1 AND vEndDateNum1;

Insert 'A' records.

Remove dates from temp date table which match PRES dates.

    --delete from tempdates td
    select count(*) into vcount from tempdates td;

    delete from tempdates td  
    where  td.txdate in (SELECT txdate --preferably date vs date comparison. convert if not.
                         FROM InsertPackageTempTableHere);  
                         
    dbms_output.put_line(vcount);

Alternatively, loop through the days and channels:

  -- now loop for each channel in Busobjtempchannels
    open c_channels;
    LOOP
      FETCH c_channels INTO vBOChannel;
      EXIT WHEN c_channels%NOTFOUND;
      
      vLoopDate := vStartDate;
      
      WHILE vLoopDate <= vEndDate LOOP
      
        vDayStartBat := dyutilities.GetBroadcastDayBat('start', vBOChannel, vLoopDate);
        vDayEndBat   := dyutilities.GetBroadcastDayBat('end', vBOChannel, vLoopDate);
        
        SELECT Count(*)
        INTO   vCheck
        FROM   schedules sch, 
               schedule_kinds sk,
               channels c,
               events e
        WHERE  c.code                = dy.OffsetChannel.f_ChannelCode(vBOChannel)
        AND    c.channel_uid         = sch.channel_uid
        AND    sch.schedule_kind_uid = sk.schedule_kind_uid
        AND    e.schedule_uid        = sch.schedule_uid
        AND    sk.code               = 'ASRN'
        AND    e.start_time BETWEEN vDayStartBat AND vDayEndBat;
        
        -- Any 'A' schedule records?
        IF (vCheck  > 0) THEN
             
             vSchedule := 'A Schedule';
         ELSE
           -- Any PRES records
           SELECT Count(*)
           INTO   vCheck
           FROM   schedules sch, 
                  schedule_kinds sk,
                  channels c,
                  events e
           WHERE  c.code                = dy.OffsetChannel.f_ChannelCode(vBOChannel)
           AND    c.channel_uid         = sch.channel_uid
           AND    sch.schedule_kind_uid = sk.schedule_kind_uid
           AND    e.schedule_uid        = sch.schedule_uid
           AND    sk.code               = 'PRES'
           AND    e.start_time BETWEEN vDayStartBat AND vDayEndBat ;
             
           IF (vCheck  > 0) THEN
             vSchedule := 'PRES';
          ELSE
           -- ELSE PLAN  
             vSchedule := 'PLAN';
          END IF;
        END IF;

I hope this was the additional info you were looking for, Santa.

 

[ChrisHunt]

How about something along these lines?

SELECT p_date, programme, schedule
FROM   your_tables t
WHERE  schedule = 'A'  -- Retrieve all of schedule A
UNION
SELECT p_date, programme, schedule
FROM   your_tables t
WHERE  schedule = 'B'
AND NOT EXISTS (SELECT 99
                FROM   your_tables t1
                WHERE  t1.p_date = t.p_date
                AND    ti.schedule = 'A') -- Fill in gaps 
UNION
SELECT p_date, programme, schedule
FROM   your_tables t
WHERE  schedule = 'C'
AND NOT EXISTS (SELECT 99
                FROM   your_tables t1
                WHERE  t1.p_date = t.p_date
                AND    ti.schedule = IN('A','B'))

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[taupirho]

Does this work for you?

SQL> select * from tom
2 /

C DTE
- ---------
A 01-JAN-09
A 01-JAN-09
A 02-JAN-09
A 03-JAN-09
A 05-JAN-09
B 01-JAN-09
B 01-JAN-09
B 02-JAN-09
B 03-JAN-09
B 04-JAN-09

10 rows selected.

1 select code,dte from
2 (
3 select code,dte,
4 rank() over(partition by dte order by code,dte) rnk2
5 from tom
6* ) where rnk2 = 1
SQL> /

C DTE
- ---------
A 01-JAN-09
A 01-JAN-09
A 02-JAN-09
A 03-JAN-09
B 04-JAN-09
A 05-JAN-09

6 rows selected.



In order to understand recursion, you must first understand recursion.

 

[Naith]

Hey guys,

Thanks for taking the time out to take a look at this.

Both these options throw up some useful ideas. I like the rank notion, which I'd not thought about at all. I'll probably go with Mr Hunt, as it's closest to the road I'm already halfway down providing the performance doesn't come with too many overheads.

 

[Naith]

Actually, Chris, that doesn't handle a date ranged parameter over multiple days.

Taupirho, I can only envisage working with your approach if I amalgamate both schedules into a temp table before applying your logic. Because of the fact that both schedules are derived in completely different ways from different tables, combined with the volume of data involved I don't know that this would be the best move performance wise.

 

[ChrisHunt]

Actually, Chris, that doesn't handle a date ranged parameter over multiple days.

You didn't ask for it to do so, and I'm not psychic.


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Naith]

Actually, it does say so in the initial post. So psychic powers not needed.