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