Forum Stats

  • 3,873,020 Users
  • 2,266,496 Discussions
  • 7,911,404 Comments

Discussions

Tricky SELECT to get a hourly calendar

user13117585
user13117585 Member Posts: 680 Bronze Badge

Hi again everyone,

I have another tricky query to write. I could do that using a programming language like java but I was wondering if we could generate such advanced output using SELECT statements.

Imagine I have a simple table like this one:

CREATE TABLE cal 
(
 start_period date, 
 end_period date,
 cnt NUMBER
);

insert into cal values(to_date('10/04/2022 02', 'DD/MM/YYYY HH24'), to_date('10/04/2022 03', 'DD/MM/YYYY HH24'), 4); -- we don't care because it's sunday from two weeks before.
insert into cal values(to_date('11/04/2022 04', 'DD/MM/YYYY HH24'), to_date('11/04/2022 05', 'DD/MM/YYYY HH24'), 8); -- monday we are interested in this date.
insert into cal values(to_date('11/04/2022 22', 'DD/MM/YYYY HH24'), to_date('11/04/2022 23', 'DD/MM/YYYY HH24'), 5); 
insert into cal values(to_date('13/04/2022 02', 'DD/MM/YYYY HH24'), to_date('13/04/2022 03', 'DD/MM/YYYY HH24'), 1);
insert into cal values(to_date('15/04/2022 00', 'DD/MM/YYYY HH24'), to_date('15/04/2022 01', 'DD/MM/YYYY HH24'), 3);

insert into cal values(to_date('18/04/2022 00', 'DD/MM/YYYY HH24'), to_date('18/04/2022 01', 'DD/MM/YYYY HH24'), 1);
insert into cal values(to_date('21/04/2022 04', 'DD/MM/YYYY HH24'), to_date('21/04/2022 05', 'DD/MM/YYYY HH24'), 1);


I would like to generate an output like a timetable. For each day, each hour, I would like to summarise what is in the table. We shouldn't have more than one row for each period. Just in case it happens, we could either SUM them or LISTAGG them.


(previous week)
WEEK_15 |  Monday | Tuesday | Wednesday | Thursday |  Friday | Saturday | Sunday
00 - 01 |         |         |           |          |       3 |
01 - 02 |         |       1 |           |          |         |
02 - 03 |         |         |           |          |         |
03 - 04 |         |         |           |          |         |
04 - 05 |       8 |         |           |          |         |
05 - 06 |         |         |           |          |         |
...
22 - 23 |       5 |         |           |          |         |
23 - 00 |         |         |           |          |         |

(week 16 is this week)
WEEK_16 |  Monday | Tuesday | Wednesday | Thursday |  Friday | Saturday | Sunday
00 - 01 |       1 |         |           |          |         |
01 - 02 |         |         |           |          |         |
02 - 03 |         |         |           |          |         |
03 - 04 |         |         |           |          |         |
04 - 05 |         |         |           |        1 |         |
05 - 06 |         |         |           |          |         |
...
22 - 23 |         |         |           |          |         |
23 - 00 |         |         |           |          |         |


This is quite tricky and I'm looking for some guidance on how we could achieve it if possible in SQL. Otherwise, I could do in Java.

Thank you for all your suggestions.

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,927 Red Diamond
    Answer ✓

    "And how did you get", ah comments in

    insert into cal values(to_date('10/04/2022 02', 'DD/MM/YYYY HH24'), to_date('10/04/2022 03', 'DD/MM/YYYY HH24'), 4); -- we don't care because it's sunday from two weeks before.
    insert into cal values(to_date('11/04/2022 04', 'DD/MM/YYYY HH24'), to_date('11/04/2022 05', 'DD/MM/YYYY HH24'), 8); -- monday we are interested in this date.
    

    prevented the above from inserting. Now I do get:

    WITH DRIVER AS (
                    SELECT  TRUNC(SYSDATE,'IW') - 7 + FLOOR((LEVEL - 1) / 24) DT,
                            MOD(LEVEL,24) HR
                      FROM  DUAL
                      CONNECT BY LEVEL <= 14 * 24
                   ),
              T AS (
                    SELECT  TRUNC(D.DT,'IW') WEEK_START,
                            TO_CHAR(D.DT,'FMDay') DAY,
                            D.HR,
                            C.CNT
                      FROM  DRIVER D,
                            CAL C
                      WHERE D.DT + D.HR / 24 >= C.START_PERIOD(+)
                        AND D.DT + D.HR / 24 <  C.END_PERIOD(+)
                   )
    SELECT  'Week_' || TO_CHAR(WEEK_START,'IW') "Week",
            TO_CHAR(HR,'09') || ' - ' || TO_CHAR(MOD(HR + 1,24),'09') "Hour",
            "Monday",
            "Tuesday",
            "Wednesday",
            "Thursday",
            "Friday",
            "Saturday",
            "Sunday"
      FROM  T
      PIVOT(
            SUM(CNT)
            FOR(DAY) IN (
                         'Monday' "Monday",
                         'Tuesday' "Tuesday",
                         'Wednesday' "Wednesday",
                         'Thursday' "Thursday",
                         'Friday' "Friday",
                         'Saturday' "Saturday",
                         'Sunday' "Sunday"
                        )
           )
      ORDER BY WEEK_START,
               HR           
    /
    
    Week    Hour          Monday    Tuesday  Wednesday   Thursday     Friday   Saturday     Sunday
    ------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    Week_15  00 -  01                                                      3
    Week_15  01 -  02
    Week_15  02 -  03                                1
    Week_15  03 -  04
    Week_15  04 -  05          8
    Week_15  05 -  06
    Week_15  06 -  07
    Week_15  07 -  08
    Week_15  08 -  09
    Week_15  09 -  10
    Week_15  10 -  11
    Week_15  11 -  12
    Week_15  12 -  13
    Week_15  13 -  14
    Week_15  14 -  15
    Week_15  15 -  16
    Week_15  16 -  17
    Week_15  17 -  18
    Week_15  18 -  19
    Week_15  19 -  20
    Week_15  20 -  21
    Week_15  21 -  22
    Week_15  22 -  23          5
    Week_15  23 -  00
    Week_16  00 -  01          1
    Week_16  01 -  02
    Week_16  02 -  03
    Week_16  03 -  04
    Week_16  04 -  05                                           1
    Week_16  05 -  06
    Week_16  06 -  07
    Week_16  07 -  08
    Week_16  08 -  09
    Week_16  09 -  10
    Week_16  10 -  11
    Week_16  11 -  12
    Week_16  12 -  13
    Week_16  13 -  14
    Week_16  14 -  15
    Week_16  15 -  16
    Week_16  16 -  17
    Week_16  17 -  18
    Week_16  18 -  19
    Week_16  19 -  20
    Week_16  20 -  21
    Week_16  21 -  22
    Week_16  22 -  23
    Week_16  23 -  00
    
    48 rows selected.
    
    SQL>
    

    SY.

Answers

  • Paulzip
    Paulzip Member Posts: 8,800 Blue Diamond
    edited Apr 21, 2022 6:46PM

    Something like this...

    with 
      minmax(min_dt, max_dt) as (
        select trunc(min(start_period)), trunc(max(end_period))
        from cal
      )
    , iter(dt, dow, wknum) as (
        select dt, trunc(dt) - trunc(dt, 'iw') + 1, to_char(dt, 'iw')
        from (
          select min_dt + level - 1 dt
          from minmax c
          connect by level <= max_dt - min_dt + 1
        )
      )
    , hrs as (
        select dt + hr/24 ts, dow, wknum 
        from iter
        cross join (
          select level - 1hr
          from   dual
          connect by level <= 24
        ) hrs 
      )
    , cal_hrs as (    
        select h.*, c.cnt
        from hrs h
        left join cal c on h.ts >= start_period and h.ts < end_period  
      )
    select to_char(ts, 'hh24') || ' - ' || to_char(ts + 1/24, 'hh24') hr, wknum 
         , monday, tuesday, wednesday, thursday, friday, saturday, sunday
    from cal_hrs
    pivot (
      max(cnt)
      for dow in (
        1 as monday
      , 2 as tuesday
      , 3 as wednesday
      , 4 as thursday
      , 5 as friday
      , 6 as saturday
      , 7 as sunday
      )
    )
    order by ts;
    
    HR      WKNUM     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY   SATURDAY     SUNDAY
    ------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    00 - 01 14
    01 - 02 14
    02 - 03 14                                                                               4
    03 - 04 14
    [snip..]
    03 - 04 15
    04 - 05 15             8
    [snip..]
    21 - 22 15
    22 - 23 15             5
    23 - 00 15
    [snip..]
    02 - 03 15                                   1
    03 - 04 15
    [snip..]
    00 - 01 16             1
    [snip..]
    03 - 04 16
    04 - 05 16                                              1
    05 - 06 16
    [snip..]
    
    
    288 rows selected.
    
    
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,927 Red Diamond

    And how did you get:

    WEEK_15 |  Monday | Tuesday | Wednesday | Thursday |  Friday | Saturday | Sunday
    04 - 05 |       8 |         |           |          |         |
    22 - 23 |       5 |         |           |          |         |
    

    Out of:

    SQL> select * from cal;
    
    START_PERIOD  END_PERIOD           CNT
    ------------- ------------- ----------
    04/13/2022 02 04/13/2022 03          1
    04/15/2022 00 04/15/2022 01          3
    04/18/2022 00 04/18/2022 01          1
    04/21/2022 04 04/21/2022 05          1
    
    SQL>
    

    I'll assume you didn't provide sample that corresponds that output,. Anyway:

    WITH DRIVER AS (
                    SELECT  TRUNC(SYSDATE,'IW') - 7 + FLOOR((LEVEL - 1) / 24) DT,
                            MOD(LEVEL,24) HR
                      FROM  DUAL
                      CONNECT BY LEVEL <= 14 * 24
                   ),
              T AS (
                    SELECT  TRUNC(D.DT,'IW') WEEK_START,
                            TO_CHAR(D.DT,'FMDay') DAY,
                            D.HR,
                            C.CNT
                      FROM  DRIVER D,
                            CAL C
                      WHERE D.DT + D.HR / 24 >= C.START_PERIOD(+)
                        AND D.DT + D.HR / 24 <  C.END_PERIOD(+)
                   )
    SELECT  'Week_' || TO_CHAR(WEEK_START,'IW') "Week",
            TO_CHAR(HR,'09') || ' - ' || TO_CHAR(MOD(HR + 1,24),'09') "Hour",
            "Monday",
            "Tuesday",
            "Wednesday",
            "Thursday",
            "Friday",
            "Saturday",
            "Sunday"
      FROM  T
      PIVOT(
            SUM(CNT)
            FOR(DAY) IN (
                         'Monday' "Monday",
                         'Tuesday' "Tuesday",
                         'Wednesday' "Wednesday",
                         'Thursday' "Thursday",
                         'Friday' "Friday",
                         'Saturday' "Saturday",
                         'Sunday' "Sunday"
                        )
           )
      ORDER BY WEEK_START,
               HR           
    /
    
    Week    Hour          Monday    Tuesday  Wednesday   Thursday     Friday   Saturday     Sunday
    ------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    Week_15  00 -  01                                                      3
    Week_15  01 -  02
    Week_15  02 -  03                                1
    Week_15  03 -  04
    Week_15  04 -  05
    Week_15  05 -  06
    Week_15  06 -  07
    Week_15  07 -  08
    Week_15  08 -  09
    Week_15  09 -  10
    Week_15  10 -  11
    Week_15  11 -  12
    Week_15  12 -  13
    Week_15  13 -  14
    Week_15  14 -  15
    Week_15  15 -  16
    Week_15  16 -  17
    Week_15  17 -  18
    Week_15  18 -  19
    Week_15  19 -  20
    Week_15  20 -  21
    Week_15  21 -  22
    Week_15  22 -  23
    Week_15  23 -  00
    Week_16  00 -  01          1
    Week_16  01 -  02
    Week_16  02 -  03
    Week_16  03 -  04
    Week_16  04 -  05                                           1
    Week_16  05 -  06
    Week_16  06 -  07
    Week_16  07 -  08
    Week_16  08 -  09
    Week_16  09 -  10
    Week_16  10 -  11
    Week_16  11 -  12
    Week_16  12 -  13
    Week_16  13 -  14
    Week_16  14 -  15
    Week_16  15 -  16
    Week_16  16 -  17
    Week_16  17 -  18
    Week_16  18 -  19
    Week_16  19 -  20
    Week_16  20 -  21
    Week_16  21 -  22
    Week_16  22 -  23
    Week_16  23 -  00
    
    48 rows selected.
    
    SQL>
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,927 Red Diamond
    Answer ✓

    "And how did you get", ah comments in

    insert into cal values(to_date('10/04/2022 02', 'DD/MM/YYYY HH24'), to_date('10/04/2022 03', 'DD/MM/YYYY HH24'), 4); -- we don't care because it's sunday from two weeks before.
    insert into cal values(to_date('11/04/2022 04', 'DD/MM/YYYY HH24'), to_date('11/04/2022 05', 'DD/MM/YYYY HH24'), 8); -- monday we are interested in this date.
    

    prevented the above from inserting. Now I do get:

    WITH DRIVER AS (
                    SELECT  TRUNC(SYSDATE,'IW') - 7 + FLOOR((LEVEL - 1) / 24) DT,
                            MOD(LEVEL,24) HR
                      FROM  DUAL
                      CONNECT BY LEVEL <= 14 * 24
                   ),
              T AS (
                    SELECT  TRUNC(D.DT,'IW') WEEK_START,
                            TO_CHAR(D.DT,'FMDay') DAY,
                            D.HR,
                            C.CNT
                      FROM  DRIVER D,
                            CAL C
                      WHERE D.DT + D.HR / 24 >= C.START_PERIOD(+)
                        AND D.DT + D.HR / 24 <  C.END_PERIOD(+)
                   )
    SELECT  'Week_' || TO_CHAR(WEEK_START,'IW') "Week",
            TO_CHAR(HR,'09') || ' - ' || TO_CHAR(MOD(HR + 1,24),'09') "Hour",
            "Monday",
            "Tuesday",
            "Wednesday",
            "Thursday",
            "Friday",
            "Saturday",
            "Sunday"
      FROM  T
      PIVOT(
            SUM(CNT)
            FOR(DAY) IN (
                         'Monday' "Monday",
                         'Tuesday' "Tuesday",
                         'Wednesday' "Wednesday",
                         'Thursday' "Thursday",
                         'Friday' "Friday",
                         'Saturday' "Saturday",
                         'Sunday' "Sunday"
                        )
           )
      ORDER BY WEEK_START,
               HR           
    /
    
    Week    Hour          Monday    Tuesday  Wednesday   Thursday     Friday   Saturday     Sunday
    ------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    Week_15  00 -  01                                                      3
    Week_15  01 -  02
    Week_15  02 -  03                                1
    Week_15  03 -  04
    Week_15  04 -  05          8
    Week_15  05 -  06
    Week_15  06 -  07
    Week_15  07 -  08
    Week_15  08 -  09
    Week_15  09 -  10
    Week_15  10 -  11
    Week_15  11 -  12
    Week_15  12 -  13
    Week_15  13 -  14
    Week_15  14 -  15
    Week_15  15 -  16
    Week_15  16 -  17
    Week_15  17 -  18
    Week_15  18 -  19
    Week_15  19 -  20
    Week_15  20 -  21
    Week_15  21 -  22
    Week_15  22 -  23          5
    Week_15  23 -  00
    Week_16  00 -  01          1
    Week_16  01 -  02
    Week_16  02 -  03
    Week_16  03 -  04
    Week_16  04 -  05                                           1
    Week_16  05 -  06
    Week_16  06 -  07
    Week_16  07 -  08
    Week_16  08 -  09
    Week_16  09 -  10
    Week_16  10 -  11
    Week_16  11 -  12
    Week_16  12 -  13
    Week_16  13 -  14
    Week_16  14 -  15
    Week_16  15 -  16
    Week_16  16 -  17
    Week_16  17 -  18
    Week_16  18 -  19
    Week_16  19 -  20
    Week_16  20 -  21
    Week_16  21 -  22
    Week_16  22 -  23
    Week_16  23 -  00
    
    48 rows selected.
    
    SQL>
    

    SY.

  • user13117585
    user13117585 Member Posts: 680 Bronze Badge

    Hello,


    Thank you both of you for your solutions!

    Regards,