Skip to Main Content
  • Questions
  • Building a complex logic for grouping the day of the week and time

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mallleswara.

Asked: January 18, 2017 - 10:56 pm UTC

Last updated: January 20, 2017 - 10:36 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom and Team,

Good Afternoon.

I have a Location table contains below data in my DB.

LOCATION_I LOCATION_SCHED_DAY LOCATION_SCHED_HOUR_FROM LOCATION_SCHED_HOUR_TO NUMERIC_DAY
1528011 MONDAY 9:00 17:00 1
1528011 TUESDAY 9:00 17:00 2
1528011 WEDNESDAY 9:00 17:00 3
1528011 THURSDAY 13:00 17:30 4
1528011 FRIDAY 11:00 17:00 5
1528011 SATURDAY 8:00 17:00 6
1528011 SUNDAY 8:00 17:00 7

I have to write a function that gets the data in below format from the Location table.

M-W 9-5 ;Th 1-5:30;F 11-5;Sat,Sun 8-5.

The output should be semicolon separated days,group by TO-FROM timings. If few days are sequential then we use hyphen('-') to separate and use start and end day with the time like (M-F 10-12)..

Please see the below example data and outputs.

LOCATION_ID LOCATION_SCHED_DAY LOCATION_SCHED_HOUR_FROM LOCATION_SCHED_HOUR_TO LOCATION_SCHED_NOTE_TXT
1,528,011 Monday 09:00 17:00
1,528,011 Tuesday 09:00 17:00
1,528,011 Wednesday 09:00 17:00
1,528,011 Thursday 09:00 17:00
1,528,011 Friday 09:00 17:00
1,527,782 Monday 09:00 17:00
1,527,782 Tuesday 09:00 17:00
1,527,782 Wednesday 09:00 17:00
1,527,782 Thursday 09:00 17:00
1,527,782 Friday 09:00 17:00
1,528,004 Monday 09:00 17:00
1,528,004 Tuesday 09:00 17:00
1,528,004 Wednesday 09:00 17:00
1,528,004 Thursday 09:00 17:00
1,528,004 Friday 09:00 17:00

out Put :
1528011 M-Th 9-5; Sa 9-4
M-F 9-5
M,W,Th,F 8:30-4:30; T 10:30-4:30
M-F 9-5
M-F 9-5
M-F 8-5
M,W,Th,F 9-6; T 9-8; Sat 9-1
M-F 9-5
M-F 9-5
M 7:30-4:30; T,Th 7:30-6:30; W,F 9-5; Sat 8:30-1:30
M-F 9-5
M-F 9-5
M-F 9-5
M-F 8-5
M-F 8-5
M-F 8-5

M-F 9-5

Hi Tom ,

I have build the code halfway as below ,Please help on it.

select LISTAGG(day_time,';') WITHIN GROUP (ORDER BY 1) AS output
from (

SELECT LISTAGG(get_day_seq(NULL, (get_day_seq(UPPER(LOCATION_SCHED_DAY)))),',') WITHIN GROUP (ORDER BY LOCATION_ID, GRP)||' '||Time day_time
FROM(
SELECT LOCATION_ID,
LOCATION_SCHED_DAY,
REPLACE(TO_CHAR(TO_DATE(LOCATION_SCHED_HOUR_FROM, 'hh24:mi'), 'fmhhfm:mi'),':00')||'-'||
REPLACE(TO_CHAR(TO_DATE(LOCATION_SCHED_HOUR_TO, 'hh24:mi'), 'fmhhfm:mi'),':00') Time,

get_day_seq(UPPER(LOCATION_SCHED_DAY)) NUMERIC_DAY,
DENSE_RANK() OVER (PARTITION BY LOCATION_ID order by LOCATION_ID,LOCATION_SCHED_HOUR_FROM, LOCATION_SCHED_HOUR_TO
)GRP
FROM LOCATION
WHERE location_id = 1528011

)
GROUP BY LOCATION_ID,GRP,TIme);

OUTPUT for above query:
F 11-5;M,T,W 9-5;Sat,Sun 8-5;Th 1-5:30
but I'm expecting below format.

M-W 9-5 ;Th 1-5:30;F 11-5;Sat,Sun 8-5.


In above code I have used below function for day conversions

CREATE OR REPLACE FUNCTION get_day_seq
(IN_DAY_NAME IN VARCHAR, IN_DAY_NUM IN NUMBER DEFAULT NULL)
RETURN VARCHAR
IS
OUT_VAL VARCHAR2(100);

BEGIN

CASE WHEN IN_DAY_NUM IS NULL THEN

WITH TEMP_DAYNUM(DAY_NAME, DAY_NUM) AS
(
SELECT 'MONDAY',1 FROM DUAL UNION
SELECT 'TUESDAY',2 FROM DUAL UNION
SELECT 'WEDNESDAY',3 FROM DUAL UNION
SELECT 'THURSDAY',4 FROM DUAL UNION
SELECT 'FRIDAY',5 FROM DUAL UNION
SELECT 'SATURDAY',6 FROM DUAL UNION
SELECT 'SUNDAY',7 FROM DUAL)

SELECT DAY_NUM INTO OUT_VAL FROM TEMP_DAYNUM T WHERE T.DAY_NAME = IN_DAY_NAME;

ELSE
WITH TEMP_DAYNUM(DAY_NAME, DAY_NUM) AS
(
SELECT 'M',1 FROM DUAL UNION
SELECT 'T',2 FROM DUAL UNION
SELECT 'W',3 FROM DUAL UNION
SELECT 'Th',4 FROM DUAL UNION
SELECT 'F',5 FROM DUAL UNION
SELECT 'Sat',6 FROM DUAL UNION
SELECT 'Sun',7 FROM DUAL
)

SELECT DAY_NAME INTO OUT_VAL FROM TEMP_DAYNUM T WHERE T.DAY_NUM = IN_DAY_NUM;

END CASE;
RETURN OUT_VAL;

END;



Hi Tom,

Kindly Please help on it.




with LiveSQL Test Case:

and Chris said...

Your LiveSQL link just leads to the worksheet... For us to see your working, you need to save it as a public script first. Then share the link that gives you.

Anyway, to get what you want there's a few things you need to do:

- Group consecutive rows with the same value together
- Find the first and last day in each group. If these are the same day, just return one
- String the rows together so they're all on one line

I'm assuming that if M-W are 9-5, Thurs is different and F is 9-5 again, you want to display F separately to M-W. So I've tweaked your data slightly to show this:

create table t (
  location_id int, sched_day varchar2(9), 
  hour_from varchar2(5), 
  hour_to varchar2(5), 
  numeric_day int
)
;
    
INSERT INTO t VALUES (1528011, 'MONDAY', '9:00', '17:00', 1);
insert into t values (1528011, 'TUESDAY', '9:00', '17:00', 2);
insert into t values (1528011, 'WEDNESDAY', '9:00', '17:00', 3);
insert into t values (1528011, 'THURSDAY', '13:00', '17:30', 4);
insert into t values (1528011, 'FRIDAY', '9:00', '17:00', 5);
insert into t values (1528011, 'SATURDAY', '8:00', '17:00', 6);
insert into t values (1528011, 'SUNDAY', '8:00', '17:00', 7);
commit;


To find if it's a new group, check whether the previous from/to are the same as the current. If they're not assign a rownum:

with grps as (
select t.*, 
       lag(hour_from, 1, '##') over (order by numeric_day) lg_from,
       lag(hour_to, 1, '##') over (order by numeric_day) lg_to,
       case 
         when 
           lag(hour_from, 1, '##') over (order by numeric_day) <> hour_from or
           lag(hour_to, 1, '##') over (order by numeric_day) <> hour_to
         then rownum 
       end r
from   t
)


Then "fill down" the blanks using last value:

grp2 as (
   select grps.*, 
          last_value(r) ignore nulls over (order by numeric_day) grp
   from   grps
)


At this point your results look like:

LOCATION_ID  SCHED_DAY  HOUR_FROM  HOUR_TO  NUMERIC_DAY  LG_FROM  LG_TO  R  GRP  
1,528,011    MONDAY     9:00       17:00    1            ##       ##     1  1    
1,528,011    TUESDAY    9:00       17:00    2            9:00     17:00     1    
1,528,011    WEDNESDAY  9:00       17:00    3            9:00     17:00     1    
1,528,011    THURSDAY   13:00      17:30    4            9:00     17:00  4  4    
1,528,011    FRIDAY     9:00       17:00    5            13:00    17:30  5  5    
1,528,011    SATURDAY   8:00       17:00    6            9:00     17:00  6  6    
1,528,011    SUNDAY     8:00       17:00    7            8:00     17:00     6


Use first_value & last_value to find the first and last day in each group:

vals as (
  select grp2.*, 
         first_value(sched_day) over (partition by grp) st,
         last_value(sched_day) over (partition by grp) en
  from   grp2
)


Then check whether these two are the same, returning one if they are. At the same time, squish out the duplicate rows with distinct:

ranges as (
  select distinct grp, 
         case when st = en then st else st || '-' || en end || ' ' || hour_from || '-' || hour_to dys
  from   vals
)


Which gives:

GRP  DYS                          
4    THURSDAY 13:00-17:30         
6    SATURDAY-SUNDAY 8:00-17:00   
5    FRIDAY 9:00-17:00            
1    MONDAY-WEDNESDAY 9:00-17:00


So to get your final output, you just need to listagg this:

select listagg(dys, ';') within group (order by grp) 
  from   ranges;



Put it all together and you have:

with grps as (
  select t.*, 
         dense_rank() over (order by hour_from, hour_to) dr 
  from   t
)
  select * from grps
  order  by numeric_day;
  
select listagg(sched_day, ';') within group (order by numeric_day) from t
group  by hour_from, hour_to;

with grps as (
select t.*, 
       lag(hour_from, 1, '##') over (order by numeric_day) lg_from,
       lag(hour_to, 1, '##') over (order by numeric_day) lg_to,
       case 
         when 
           lag(hour_from, 1, '##') over (order by numeric_day) <> hour_from or
           lag(hour_to, 1, '##') over (order by numeric_day) <> hour_to
         then rownum 
       end r
from   t
), grp2 as (
   select grps.*, 
          last_value(r) ignore nulls over (order by numeric_day) grp
   from   grps
), vals as (
  select grp2.*, 
         first_value(sched_day) over (partition by grp) st,
         last_value(sched_day) over (partition by grp) en
  from   grp2
), ranges as (
  select distinct grp, 
         case when st = en then st else st || '-' || en end || ' ' || hour_from || '-' || hour_to dys
  from   vals
) 
  select listagg(dys, ';') within group (order by grp) 
  from   ranges;

LISTAGG(DYS,';')WITHINGROUP(ORDERBYGRP)                                                        
MONDAY-WEDNESDAY 9:00-17:00;THURSDAY 13:00-17:30;FRIDAY 9:00-17:00;SATURDAY-SUNDAY 8:00-17:00  


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much Chris Saxon !!!!

A reader, January 19, 2017 - 12:53 pm UTC

it's really helpful,once again thanks for quick reply.
Chris Saxon
January 20, 2017 - 10:36 am UTC

Happy to help.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.