tag:blogger.com,1999:blog-3589997105344226574.post8202434326487710330..comments2021-03-23T12:33:46.970-07:00Comments on Oracle DBMS related tidbits: Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-3589997105344226574.post-30214888882494759112021-03-23T12:33:46.970-07:002021-03-23T12:33:46.970-07:00I just note i missed to order by article_id ^^.
H...I just note i missed to order by article_id ^^.<br /><br />Here's the revised fiddle with the last order by including article_id :<br /><br />https://dbfiddle.uk/?rdbms=oracle_18&fiddle=65c3915ac03ecfcd2c6e772a245c2394<br />Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-43264526955706765072021-03-23T12:29:00.635-07:002021-03-23T12:29:00.635-07:00Maybe Toon Koppelaars expects the output to includ...Maybe Toon Koppelaars expects the output to include the slot 08:20:00-08:25:00 and<br />08:25:00-08:30:00. Nice idea to use width and lateral ^^.<br /><br />I suggest that for the twi 'null' slots : <br /><br />select to_char(periods.slot,'YYYY-MM-DD HH24:MI')||'-'||to_char(periods.slot+interval '5' minute,'HH24:MI') slot<br />, article_id<br />, count(article_id) slot_quantity<br />from (select (p_start + ((LEVEL - 1)/288)) slot<br /> from (select min(cast(arrival_starttime as date)) p_start, max(cast(arrival_stoptime as date)) p_stop from orderline_arrivals)<br /> connect by level <= round(to_number(p_stop - p_start) * 300,0)) periods<br /> LEFT JOIN ( select oa.*, ai.article_instance<br /> , oa.arrival_starttime+<br /> (width_bucket<br /> ( oa.arrival_starttime+(article_instance-1)*num_buckets/quantity*(interval '5' minute)<br /> , oa.arrival_starttime<br /> , oa.arrival_stoptime<br /> , oa.num_buckets<br /> )-1<br /> )*(interval '5' minute) slot<br /> from ( select oa.*, round((cast(arrival_stoptime as date)-cast(arrival_starttime as date))*285) num_buckets<br /> from orderline_arrivals oa<br /> ) oa<br /> , lateral ( select level article_instance from dual connect by level <= oa.quantity) ai<br /> ) result ON result.slot = periods.slot<br />group by periods.slot, article_id<br />order by periods.slot, article_id;Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-76647595624761139142021-03-23T04:53:13.628-07:002021-03-23T04:53:13.628-07:00how about
select to_char(slot,'YYYY-MM-DD HH...how about<br /><br />select to_char(slot,'YYYY-MM-DD HH24:MI')||'-'||to_char(slot+interval '5' minute,'HH24:MI') slot<br />, article_id<br />, count(*) slot_quantity<br />from ( select oa.*, ai.article_instance<br /> , oa.arrival_starttime+<br /> (width_bucket<br /> ( oa.arrival_starttime+(article_instance-1)*num_buckets/quantity*(interval '5' minute)<br /> , oa.arrival_starttime<br /> , oa.arrival_stoptime<br /> , oa.num_buckets<br /> )-1<br /> )*(interval '5' minute) slot<br /> from ( select oa.*, round((arrival_stoptime-arrival_starttime)*285) num_buckets<br /> from orderline_arrivals oa<br /> ) oa<br /> , lateral ( select level article_instance from dual connect by level <= oa.quantity) ai<br /> )<br />group by slot, article_id<br />order by slot, article_id;<br />Rob van den Berghttps://www.blogger.com/profile/03797947431578815225noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-85940225644730424772021-03-23T02:26:40.385-07:002021-03-23T02:26:40.385-07:00This comment has been removed by the author.Rob van den Berghttps://www.blogger.com/profile/03797947431578815225noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-60574186720404890722021-03-18T16:45:59.739-07:002021-03-18T16:45:59.739-07:00Condensed to 2à lines with a not so bad exec plan ...Condensed to 2à lines with a not so bad exec plan :<br /><br />https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fddedae5bff378572629209f8149d5a7<br /><br /> -- Oracle Version with final result set<br />with period as (select min(arrival_starttime) period_start, <br /> max(arrival_stoptime) period_end,<br /> round(to_number(max(cast(arrival_stoptime as date)) - min(cast(arrival_starttime as date))) * 300,0) nb_period<br /> from orderline_arrivals<br /> where arrival_starttime between to_date('2021-03-18', 'RRRR-MM-DD') and to_date('2021-03-19', 'RRRR-MM-DD')),<br /> orders_period as (select orders.*, <br /> round(to_number(cast(arrival_stoptime as date) - cast(arrival_starttime as date)) * 300,0) nb_period<br /> from orderline_arrivals orders)<br />select to_char(periods.p_start, 'HH24:MI:SS')||'-'||to_char(periods.p_end, 'HH24:MI:SS') slot,<br /> orders.article_id,<br /> round(sum((case when NVL(nb_period, 0) = 0 THEN NULL ELSE <br /> (quantity / nb_period) + (((case when p_start = orders.arrival_starttime then mod(quantity , nb_period) else 0 END) <br /> - (case when p_end = orders.arrival_stoptime then mod(quantity , nb_period) else 0 END)) / nb_period) END)) /*over (partition by orders.article_id, periods.p_start, periods.p_end)*/, 0) qte_order<br /> from (select (period_start + ((LEVEL - 1)/288)) p_start, <br /> (period_start + (LEVEL/288)) p_end<br /> from period<br /> connect by level <= nb_period) periods left join orders_period orders on orders.arrival_starttime <= periods.p_start and orders.arrival_stoptime > periods.p_start<br /> group by periods.p_start, periods.p_end, orders.article_id<br /> order by periods.p_start;<br /><br />Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-51023034306186887542021-03-18T14:51:27.478-07:002021-03-18T14:51:27.478-07:00Short solution: https://gist.github.com/xtender/cf...Short solution: https://gist.github.com/xtender/cf7253a74605f9740ad2c47950c14a13<br />/// blogspot doesn't support 'code' in comments... <br />select <br /> slot_1, slot_2, article_id, <br /> sum(case when (quantity - n*ceil(quantity/max_n)) >= (max_n-n)*floor(quantity/max_n) <br /> then ceil(quantity/max_n) <br /> else floor(quantity/max_n) <br /> end) as quantity_slot <br />from orderline_arrivals a <br /> ,lateral( <br /> select <br /> level as n, <br /> round((arrival_stoptime-arrival_starttime)*24*60/5) as max_n, <br /> a.arrival_starttime + (level-1)*interval '5' minute as slot_1, <br /> a.arrival_starttime + (level )*interval '5' minute as slot_2 <br /> from dual <br /> connect by a.arrival_starttime + (level )*interval '5' minute<=arrival_stoptime <br /> ) x <br />group by slot_1, slot_2, article_id <br />order by slot_1, slot_2, article_id;<br />Sayan Malakshinovhttps://www.blogger.com/profile/11087163803358489777noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-37858116117959570962021-03-18T14:46:44.483-07:002021-03-18T14:46:44.483-07:00Short final query: https://livesql.oracle.com/apex...Short final query: https://livesql.oracle.com/apex/livesql/s/liv25sp6wzgmudhxa1xrhpgux<br /><br />Detailed query with comments: https://livesql.oracle.com/apex/livesql/s/liv0vini2eaav6xvnnkcx54d4Sayan Malakshinovhttps://www.blogger.com/profile/11087163803358489777noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-81432571070861547692021-03-18T14:45:36.019-07:002021-03-18T14:45:36.019-07:00Less parenthesis (i love LISP) and better timestam...Less parenthesis (i love LISP) and better timestamp maths :<br /><br />https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7d5b7d628e238a9f3f1b8daf0975842eZilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-72868390340073562752021-03-18T14:36:11.968-07:002021-03-18T14:36:11.968-07:00Good catch, Rob.
Thanks!Good catch, Rob.<br />Thanks!Oren Nakdimonhttps://www.blogger.com/profile/05183397716813045976noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-42088127936467557372021-03-18T14:18:25.080-07:002021-03-18T14:18:25.080-07:00Awesome. Based on your solution, I just added
HAV...Awesome. Based on your solution, I just added<br /><br />HAVING sum<br /> ( case when s# <= r<br /> then ceil(quantity / num_of_slots)<br /> else trunc(quantity / num_of_slots)<br /> end<br /> ) != 0<br /><br />in case the number of slots exceeds the quantityRob van den Berghttps://www.blogger.com/profile/03797947431578815225noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-17810612210527087802021-03-18T14:07:54.816-07:002021-03-18T14:07:54.816-07:00I've separated the per orderline distribution ...I've separated the per orderline distribution and the final resultset here :<br /><br />https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8d62dada9585c9c4b6e862d7c2236044Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-83111197811030677722021-03-18T13:52:06.430-07:002021-03-18T13:52:06.430-07:00To precise: It's somehow a translation from th...To precise: It's somehow a translation from the MySQL, no optim. Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-62873511204962206922021-03-18T13:50:56.883-07:002021-03-18T13:50:56.883-07:00My Oracle version :
For the last select, use :
s...My Oracle version :<br /><br />For the last select, use :<br /><br />select * from reparts_period -- for the distribution per orderline<br /><br />or<br /><br />select * from reparts_period_synt -- for the final resultset for a given date<br /><br />Largely more compact than MySQL ^^<br /><br />https://dbfiddle.uk/?rdbms=oracle_18&fiddle=cf2cf07cfa39f92b5e2723aee1484dbbZilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-80641562501633063402021-03-18T13:10:27.482-07:002021-03-18T13:10:27.482-07:00Solved in MariaDB. No recursion:
https://dbfiddle....Solved in MariaDB. No recursion:<br />https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=84c7237e07bff4cc73e1f9394c7969f8ypercubehttps://www.blogger.com/profile/12328823603963158391noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-38947809863032496662021-03-18T11:24:13.196-07:002021-03-18T11:24:13.196-07:00This is using BigQuery, without recursion. To get ...This is using BigQuery, without recursion. To get your null slots back I would need one more outer join after the aggregation, but I didn't bother :)<br />DROP TABLE<br />jdata.orderline_arrivals;<br /><br />CREATE TABLE<br />jdata.orderline_arrivals AS<br />SELECT<br />1 AS orderlineid,<br />TIMESTAMP ('2021-03-18 08:00:00' ) AS arrival_starttime,<br />TIMESTAMP ('2021-03-18 08:15:00') AS arrival_stoptime,<br />'A1' AS article_id,<br />10 AS quantity<br />UNION ALL<br />SELECT<br />2,<br />TIMESTAMP ('2021-03-18 08:00:00' ),<br />TIMESTAMP ('2021-03-18 08:15:00'),<br />'A2',<br />20<br />UNION ALL<br />SELECT<br />3,<br />TIMESTAMP ('2021-03-18 08:10:00' ),<br />TIMESTAMP ('2021-03-18 08:20:00'),<br />'A1',<br />5<br />UNION ALL<br />SELECT<br />4,<br />TIMESTAMP ('2021-03-18 08:30:00' ),<br />TIMESTAMP ('2021-03-18 08:40:00'),<br />'A3',<br />50;<br /><br /><br />WITH<br />time_array AS (<br />SELECT<br />timeslot_start,<br />LEAD(timeslot_start) OVER (ORDER BY timeslot_start) AS timeslot_end<br />FROM (<br />SELECT<br />*<br />FROM<br />UNNEST(GENERATE_TIMESTAMP_ARRAY('2021-03-18 08:00:00',<br />'2021-03-18 08:45:00',<br />INTERVAL 5 MINUTE) )timeslot_start) ),<br />order_exploded AS (<br />SELECT<br />orderlineid,<br />TIMESTAMP_ADD(arrival_starttime, INTERVAL time_between_orders*nr SECOND) AS item_arrivaltime,<br />article_id<br />FROM (<br />SELECT<br />orderlineid,<br />arrival_starttime,<br />arrival_stoptime,<br />article_id,<br />nr,<br />SAFE_CAST(TIMESTAMP_DIFF(arrival_stoptime, arrival_starttime, SECOND)/quantity AS INT64) AS time_between_orders<br />FROM<br />jdata.orderline_arrivals oa,<br />UNNEST(GENERATE_ARRAY(0,100)) nr<br />WHERE<br />nr <= oa.quantity))<br />SELECT<br />FORMAT_TIMESTAMP('%R',timeslot_start)||' - '||FORMAT_TIMESTAMP('%R',timeslot_end) AS slots,<br />article_id,<br />COUNT(item_arrivaltime) AS slot_quantity<br />FROM<br />order_exploded,<br />time_array<br />WHERE<br />timeslot_start < item_arrivaltime<br />AND timeslot_end >= item_arrivaltime<br />GROUP BY 1,2<br />ORDER BY 1,2;db_magnushttps://www.blogger.com/profile/02152073911638389906noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-60115006094258326962021-03-18T11:22:38.648-07:002021-03-18T11:22:38.648-07:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/00267103289143473542noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-67786035752603933462021-03-18T11:11:35.871-07:002021-03-18T11:11:35.871-07:00I just registered on blogger to keep tracks.I just registered on blogger to keep tracks.Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-73037180063417786892021-03-18T11:01:52.992-07:002021-03-18T11:01:52.992-07:00Second part:
repart_cols as (select (@col:=@col...Second part: <br /><br /> repart_cols as (select (@col:=@col + 1) rcol, -- No PIVOT WITH MySQL...<br /> (@col1:=CONVERT((case when @col = 1 then id else @col1 end), CHAR)) rcol1,<br /> (@col2:=CONVERT((case when @col = 2 then id else @col2 end), CHAR)) rcol2,<br /> (@col3:=CONVERT((case when @col = 3 then id else @col3 end), CHAR)) rcol3,<br /> (@col4:=CONVERT((case when @col = 4 then id else @col4 end), CHAR)) rcol4,<br /> (@col5:=CONVERT((case when @col = 5 then id else @col5 end), CHAR)) rcol5,<br /> (@col6:=CONVERT((case when @col = 6 then id else @col6 end), CHAR)) rcol6,<br /> (@col7:=CONVERT((case when @col = 7 then id else @col7 end), CHAR)) rcol7,<br /> (@col8:=CONVERT((case when @col = 8 then id else @col8 end), CHAR)) rcol8<br /> from (SELECT @col:=0, @col1:=NULL, @col2:=NULL, @col3:=NULL, @col4:=NULL, @col5:=NULL, @col6:=NULL, @col7:=NULL, @col8:=NULL ) cols<br /> join (select distinct concat( orderlineid, '_', article_id) id from orderline_arrivals_date) orders on 1=1 ),<br /> reparts_period as (select 'slot' slot,<br /> @col1 col1,<br /> @col2 col2,<br /> @col3 col3,<br /> @col4 col4,<br /> @col5 col5,<br /> @col6 col6,<br /> @col7 col7,<br /> @col8 col8<br /> from (select distinct 1 from repart_cols) cols<br /> UNION ALL<br /> select CONCAT(reparts.p_start,'_', reparts.p_end) slot,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col1 THEN reparts.qte_order else NULL END) col1,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col2 THEN reparts.qte_order else NULL END) col2,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col3 THEN reparts.qte_order else NULL END) col3,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col4 THEN reparts.qte_order else NULL END) col4,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col5 THEN reparts.qte_order else NULL END) col5,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col6 THEN reparts.qte_order else NULL END) col6,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col7 THEN reparts.qte_order else NULL END) col7,<br /> (case when CONCAT(reparts.orderlineid,'_', reparts.article_id) = @col8 THEN reparts.qte_order else NULL END) col8<br /> from (select distinct 1 from repart_cols) cols left join reparts on 1 = 1),<br /> reparts_period_synt as (select concat(periods.p_start, '-', periods.p_end) slot, reparts.article_id, sum(reparts.qte_order) slot_quantity<br /> from periods left join reparts on reparts.p_start= periods.p_start<br /> group by concat(periods.p_start, '-', periods.p_end), reparts.article_id)<br />select * from reparts_period_synt ;<br /><br />Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-9383898167357795102021-03-18T11:01:39.825-07:002021-03-18T11:01:39.825-07:00First part (4kb limit..)
set @arrival_date=conver...First part (4kb limit..)<br /><br />set @arrival_date=convert('2021-03-18',date); -- the given date<br />with base as (select 1 rowid from dual union all select 2 rowid from dual), -- Cartesian product to simulate hierarchical statements<br /> counts as (select (@row_number:=@row_number + 1) rownum from (SELECT @row_number:=0) rowcount join base on 1 = 1 join base base2 on 1 = 1 join base base3 on 1 = 1 join base base4 on 1 = 1 join base base5 on 1 = 1 join base base6 on 1 = 1 join base base7 on 1 = 1 join base base8 on 1 = 1 join base base9 on 1 = 1), -- 512 entry to cover 288 periods at max<br /> orderline_arrivals_date as (select * from orderline_arrivals where @arrival_date between convert(arrival_starttime, date) and convert(arrival_stoptime,date)),<br /> period as (select min(arrival_starttime) period_start, max(arrival_stoptime) period_end,<br /> ((CAST(DATE_FORMAT(max(arrival_stoptime), '%H') as UNSIGNED) * 12 + (CAST(DATE_FORMAT(max(arrival_stoptime), '%i') as UNSIGNED) / 5))<br /> - (CAST(DATE_FORMAT(min(arrival_starttime), '%H') as UNSIGNED) * 12 + CAST(DATE_FORMAT(min(arrival_starttime), '%i') as UNSIGNED) / 5)) nb_period<br /> from orderline_arrivals_date),<br /> periods as (select convert((period_start + 500 * (counts.rownum - 1)), datetime) p_start, <br /> convert((period_start + 500 * counts.rownum), datetime) p_end<br /> from period join counts on period.nb_period >= counts.rownum ),<br /> orders_period as (select orders.*, <br /> ((CAST(DATE_FORMAT(arrival_stoptime, '%H') as UNSIGNED) * 12 + (CAST(DATE_FORMAT(arrival_stoptime, '%i') as UNSIGNED) / 5))<br /> - (CAST(DATE_FORMAT(arrival_starttime, '%H') as UNSIGNED) * 12 + CAST(DATE_FORMAT(arrival_starttime, '%i') as UNSIGNED) / 5)) nb_period<br /> from orderline_arrivals_date orders),<br /> repart as (select p_start, p_end, orderlineid, article_id,<br /> (quantity / nb_period) + ((case when p_start = orders.arrival_starttime then (quantity % nb_period) else 0 END) / nb_period)<br /> - ((case when p_end = orders.arrival_stoptime then (quantity % nb_period) else 0 END) / nb_period) qte <br /> -- (case when p_start = orders.arrival_starttime then (quantity % nb_period) else 0 END) modulus_start,<br /> -- (case when p_end = orders.arrival_stoptime then (quantity % nb_period) else 0 END) modulus_end<br /> -- orders.arrival_starttime, orders.arrival_stoptime<br /> from periods left join orders_period orders on orders.arrival_starttime <= periods.p_start and orders.arrival_stoptime > periods.p_start -- periods.p_start between orders.arrival_starttime and orders.arrival_stoptime<br /> order by p_start),<br /> reparts as (select articles.orderlineid, articles.article_id,<br /> repart.p_start, repart.p_end,<br /> round(sum(repart.qte), 0) qte_order<br /> from (select distinct orderlineid, article_id from orderline_arrivals_date) articles <br /> left join repart on repart.article_id = articles.article_id<br /> and repart.orderlineid = articles.orderlineid<br /> group by articles.article_id, articles.orderlineid, repart.p_start, repart.p_end),<br />Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-49987414114973660742021-03-18T11:00:31.234-07:002021-03-18T11:00:31.234-07:00Hi, a little too easy with Oracle. So here is the ...Hi, a little too easy with Oracle. So here is the queries for MySQL.<br /><br />set the given date at the line : <br /><br />set @arrival_date=convert('2021-03-18',date); -- the given date<br /><br />For the last select, use :<br /><br />select * from reparts_period; -- for the distribution per orderline<br /><br />or<br /><br />select * from reparts_period_synt ; -- for the final resultset for a given date<br /><br />Cheers, Zilog.<br /><br />create table orderline_arrivals<br />(orderlineid int not null<br />,arrival_starttime datetime not null<br /> check(mod(CAST(DATE_FORMAT(arrival_starttime, '%i') as UNSIGNED) ,5)=0)<br />,arrival_stoptime datetime not null<br /> check(mod(CAST(DATE_FORMAT(arrival_stoptime, '%i') as UNSIGNED),5)=0)<br />,article_id varchar(10) not null<br />,quantity int(4) not null<br /> check(quantity > 0)<br />,check(date(arrival_starttime) = date(arrival_stoptime))<br />,check(arrival_starttime < arrival_stoptime));<br /><br />alter table orderline_arrivals add primary key (orderlineid);<br /><br />DELETE FROM orderline_arrivals WHERE orderlineid>0;<br /><br />insert into orderline_arrivals(orderlineid, arrival_starttime, arrival_stoptime, article_id, quantity)<br /> values (1, convert('2021-03-18 08:00',datetime),convert('2021-03-18 08:15',datetime), 'A1', 10);<br />insert into orderline_arrivals(orderlineid, arrival_starttime, arrival_stoptime, article_id, quantity)<br /> values (2, convert('2021-03-18 08:00',datetime),convert('2021-03-18 08:15',datetime), 'A2', 20); <br />insert into orderline_arrivals(orderlineid, arrival_starttime, arrival_stoptime, article_id, quantity)<br /> values (3, convert('2021-03-18 08:10',datetime),convert('2021-03-18 08:20',datetime), 'A1', 5); <br />insert into orderline_arrivals(orderlineid, arrival_starttime, arrival_stoptime, article_id, quantity)<br /> values (4, convert('2021-03-18 08:30',datetime),convert('2021-03-18 08:40',datetime), 'A3', 50); <br /><br />Queries in the next post ^^ (max 4kB...)<br /><br />Zilog80https://www.blogger.com/profile/14200914390281156110noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-61477796983717393772021-03-18T08:28:08.963-07:002021-03-18T08:28:08.963-07:00select to_char(slot_start, 'hh24:mi') || &...select to_char(slot_start, 'hh24:mi') || ' - ' || to_char(any_value(slot_end), 'hh24:mi') slot,<br /> article_id,<br /> sum(case when s# <= r then ceil(quantity / num_of_slots) else trunc(quantity / num_of_slots) end) slot_quantity<br />from (select slots.*,<br /> ol.*,<br /> row_number() over(partition by ol.orderlineid order by slots.slot_start) s#,<br /> round(mod(ol.quantity, ol.num_of_slots)) r<br /> from (select lb + (level-1)*5/1440 slot_start,<br /> lb + level*5/1440 slot_end<br /> from (select min(arrival_starttime) lb,<br /> max(arrival_stoptime) hb<br /> from orderline_arrivals<br /> where trunc(arrival_starttime) = date '2021-03-18')<br /> connect by lb + (level-1)*5/1440 < hb) slots,<br /> (select a.*,<br /> (arrival_stoptime - arrival_starttime) * 1440/5 num_of_slots<br /> from orderline_arrivals a) ol<br /> where ol.arrival_starttime(+) <= slots.slot_start<br /> and ol.arrival_stoptime(+) >= slots.slot_end)<br />group by slot_start,article_id<br />order by slot,article_id;Oren Nakdimonhttps://www.blogger.com/profile/05183397716813045976noreply@blogger.comtag:blogger.com,1999:blog-3589997105344226574.post-23003176033640188212021-03-18T07:47:41.927-07:002021-03-18T07:47:41.927-07:00I did a possible solution on LiveSQL:
https://liv...I did a possible solution on LiveSQL:<br /><br />https://livesql.oracle.com/apex/livesql/s/liudk1wjsssreegx8kp5l77ba<br /><br />Might not be my prettiest work, but it solves the case with only a single access of the table and a tiny bit of analytic functions.Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.com