I have a table that contains the time periods when an issuer calls the service. this table can have overlapping and non overlapping time periods:
with mht_issuer_revoked_call (issuerid, startdate, enddate) as (values
(4, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(4, to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022', 'dd-mm-yyyy'),
(40, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 06:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 06:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 11:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 18:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 18:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 19:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 11:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022 01:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 19:31:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022', 'dd-mm-yyyy'),
(50, to_date('25-11-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 23:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:45:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 23:50:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:55:00', 'dd-mm-yyyy hh24:mi:ss')
)
i managed to merge the time periods and new time periods dont have any overlapping with each other. my output is as follows:
with issuer_calls_merged (issuerid, start_date_time, end_date_time) as (values
(4 ,'11/25/2022' , '11/26/2022'),
(40 ,'11/25/2022', '11/25/2022 6:00:00 PM'),
(40 ,'11/25/2022 6:30:00 PM', '11/25/2022 7:30:00 PM'),
(40 ,'11/25/2022 7:31:00 PM', '11/26/2022' ),
(50 ,'11/25/2022', '11/26/2022 1:30:00 AM')
)
i am trying to write a procedure that gets FromDate and EndDate as input parameters and for each issuer calculates how many minutes are not covered according to retrieved FromDate and EndDate Parameters. for example i will give these parameters: FromDate := '11/20/2022' EndDate := '11/28/2022' then according to inserted time periods in issuer_calls table, for issuerid 40 i expect this output:
| issuerid | start_date_time(uncovered) | end_date_time(uncovered) | uncovered_time_minutes
| 40 | 11/20/2022 | 11/25/2022 | 7200
| 40 | 11/25/2022 6:00:00 PM | 11/25/2022 6:30:00 PM | 30
| 40 | 11/25/2022 7:30:00 PM | 11/25/2022 7:31:00 PM | 1
| 40 | 11/26/2022 | 11/28/2022 | 2880
i tried to do the job with procedure bellow:
create or replace procedure GAP(out_res out sys_refcursor,
in_FromDate mht_issuer_revoked_call.startdate%type,
in_EndDate mht_issuer_revoked_call.enddate%type
) AS
BEGIN
**-- i tried to compare the given time period(FromDate-EndDate) with previous merged time periods and calculate the gaps and then union with previous gap**
open out_res for
select ut.issuerid,
ut.startdate,
ut.enddate,
ut.initialgap as gap
from
(
with minStartDate as
(
select r.issuerid,
min(r.startdate) as min_StartDate
from mht_issuer_revoked_call r
group by r.issuerid
)
select m.issuerid,
in_FromDate as StartDate,
case
when m.min_StartDate >= in_EndDate then in_EndDate
else m.min_StartDate
end as EndDate,
case
when m.min_StartDate >= in_EndDate then (in_EndDate - in_FromDate 1)*24*60
else (min_StartDate - in_FromDate 1)*24*60
end as initialgap
from minStartDate m
union all
**--- bellow part merges the time periods and calculate the gaps between them**
SELECT issuerid,
end_date_time,
next_row_start,
(next_row_start - end_date_time)*24*60 as gap
from
(
SELECT issuerid,
start_date_time,
end_date_time,
case
when lead(start_date_time) over(partition by issuerid order by start_date_time) is null then end_date_time
else lead(start_date_time) over(partition by issuerid order by start_date_time)
end as next_row_start
FROM (
SELECT issuerid,
LAG( dt ) OVER ( PARTITION BY issuerid ORDER BY dt ) AS start_date_time,
dt AS end_date_time,
start_end
FROM (
SELECT issuerid,
dt,
CASE SUM( value ) OVER ( PARTITION BY issuerid ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM mht_issuer_revoked_call
UNPIVOT ( dt FOR value IN ( startdate AS 1, enddate AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end'
)
where (next_row_start - end_date_time) > 0
group by issuerid,next_row_start,end_date_time
) ut
order by ut.issuerid, ut.StartDate;
END gap;
but at the end i couldn't achieve the explained result above
CodePudding user response：
You can get your result using just SQL and process it later. In this answer your FromDate And EndDate (P_FROM, P_UNTILL) are set to those from your question. You can define them as parameters or bind variables so you could change them. Comments are in the code.
WITH
tbl AS -- sample data
(
Select 4 "ID", To_Date('25.11.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 12:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 4 "ID", To_Date('25.11.2022 12:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('26.11.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 40 "ID", To_Date('25.11.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 06:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 40 "ID", To_Date('25.11.2022 06:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 12:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 40 "ID", To_Date('25.11.2022 11:30:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 18:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 40 "ID", To_Date('25.11.2022 18:30:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 19:30:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 40 "ID", To_Date('25.11.2022 19:31:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('26.11.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 50 "ID", To_Date('25.11.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 12:00:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 50 "ID", To_Date('25.11.2022 11:00:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('26.11.2022 01:30:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 50 "ID", To_Date('25.11.2022 23:10:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 23:30:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 50 "ID", To_Date('25.11.2022 23:30:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 23:45:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual Union All
Select 50 "ID", To_Date('25.11.2022 23:50:00', 'dd.mm.yyyy hh24:mi:ss') "START_DATE", To_Date('25.11.2022 23:55:00', 'dd.mm.yyyy hh24:mi:ss') "END_DATE" From Dual
),
day_tbl AS -- create CTE to prepare your data
( Select ID,
ROW_NUMBER() OVER(Partition By ID Order By START_DATE) "RN", -- ordering ID events
START_DATE "START_DATE", To_Char(START_DATE, 'hh24:mi:ss') "START_TIME", -- just showing the time part of START_DATE
END_DATE "END_DATE", To_Char(END_DATE, 'hh24:mi:ss') "END_TIME", -- just showing the time part of END_DATE
--
To_Date('20.11.2022', 'dd.mm.yyyy') "P_FROM", -- column with P_FROM - you could define it as bind variable
To_Date('28.11.2022', 'dd.mm.yyyy') "P_UNTILL", -- column with P_FROM - you could define it as bind variable
( END_DATE - START_DATE ) * 24 * 60 "MINS" -- first calculation used for first and last row
From
(Select *
From ( -- for each ID create starting and ending row and union them with your data
Select ID "ID", START_DATE "START_DATE", END_DATE "END_DATE" From tbl Union ALL
Select ID, To_Date('20.11.2022', 'dd.mm.yyyy'), Min(START_DATE) From tbl GROUP BY ID Union All -- row with P_FROM as START_DATE - you could define it as bind variable
Select ID, Max(END_DATE), To_Date('28.11.2022', 'dd.mm.yyyy') From tbl GROUP BY ID -- row with P_UNTILL as END_DATE - you could define it as bind variable
)
Order By ID, START_DATE
)
)
SELECT
* -- you can select just the columns you need (not all of them like here)
FROM
( Select
ID, RN, START_DATE, START_TIME, END_DATE, END_TIME, P_FROM, P_UNTILL,
CASE WHEN RN = 1 Or RN = Max(RN) OVER(Partition By ID) THEN MINS -- first and last row already calculated
-- else --> second calculation for rows that are not first nor last
ELSE Round(( START_DATE - FIRST_VALUE(END_DATE) OVER(Partition By ID, TRUNC(START_DATE) Order By START_DATE Rows Between 1 Preceding And Current Row) ) * 24 * 60, 0)
END "MINS"
From
day_tbl
)
WHERE
MINS > 0 -- if you want just ID=40 here you can filter it
--
/* R e s u l t :
ID RN START_DATE START_TIME END_DATE END_TIME P_FROM P_UNTILL MINS
---------- ---------- ---------- ---------- --------- -------- --------- --------- ----------
4 1 20-NOV-22 00:00:00 25-NOV-22 00:00:00 20-NOV-22 28-NOV-22 7200
4 4 26-NOV-22 00:00:00 28-NOV-22 00:00:00 20-NOV-22 28-NOV-22 2880
40 1 20-NOV-22 00:00:00 25-NOV-22 00:00:00 20-NOV-22 28-NOV-22 7200
40 5 25-NOV-22 18:30:00 25-NOV-22 19:30:00 20-NOV-22 28-NOV-22 30
40 6 25-NOV-22 19:31:00 26-NOV-22 00:00:00 20-NOV-22 28-NOV-22 1
40 7 26-NOV-22 00:00:00 28-NOV-22 00:00:00 20-NOV-22 28-NOV-22 2880
50 1 20-NOV-22 00:00:00 25-NOV-22 00:00:00 20-NOV-22 28-NOV-22 7200
50 6 25-NOV-22 23:50:00 25-NOV-22 23:55:00 20-NOV-22 28-NOV-22 5
50 7 26-NOV-22 01:30:00 28-NOV-22 00:00:00 20-NOV-22 28-NOV-22 2790
*/
CodePudding user response：
i think i could finally finish the job. mht_issuer_revoked_call is the name of the table where issuer requests are submitted.
With Merged_Recs as ( select issuerid, start_date_time as start_date, end_date_time as end_date FROM ( SELECT issuerid, LAG(dt) OVER(PARTITION BY issuerid ORDER BY dt) AS start_date_time, dt AS end_date_time, start_end FROM ( SELECT issuerid, dt, CASE SUM(value) OVER(PARTITION BY issuerid ORDER BY dt ASC, value DESC, ROWNUM) * value WHEN 1 THEN 'start' WHEN 0 THEN 'end' END AS start_end FROM mht_issuer_revoked_call UNPIVOT(dt FOR value IN(startdate AS 1, enddate AS - 1)) ) WHERE start_end IS NOT NULL ) WHERE start_end = 'end' ), my_tbl AS -- create CTE to prepare your data ( Select ISSUERID, ROW_NUMBER() OVER(Partition By ISSUERID Order By START_DATE) "RN", -- ordering ID events START_DATE "START_DATE", To_Char(START_DATE, 'hh24:mi:ss') "START_TIME", -- just showing the time part of START_DATE END_DATE "END_DATE", To_Char(END_DATE, 'hh24:mi:ss') "END_TIME", -- just showing the time part of END_DATE To_Date('20.11.2022', 'dd.mm.yyyy') "P_FROM", -- column with P_FROM - you could define it as bind variable To_Date('28.11.2022', 'dd.mm.yyyy') "P_UNTILL", -- column with P_FROM - you could define it as bind variable (END_DATE - START_DATE) * 24 * 60 "MINS" -- first calculation used for first and last row From ( Select * From ( -- for each ID create starting and ending row and union them with your data Select issuerid "ISSUERID", START_DATE "START_DATE", END_DATE "END_DATE" From Merged_Recs Union ALL -- row with P_FROM as START_DATE - you could define it as bind variable Select issuerid, To_Date('20.11.2022', 'dd.mm.yyyy'), Min(START_DATE) From Merged_Recs GROUP BY issuerid Union All -- row with P_UNTILL as END_DATE - you could define it as bind variable Select issuerid, Max(END_DATE), To_Date('28.11.2022', 'dd.mm.yyyy') From Merged_Recs GROUP BY issuerid) Order By ISSUERID, START_DATE ) ) select ISSUERID, START_DATE AS START_DATE, SELECTED_END_DATE AS END_DATE, MINS AS GAP_MINUTES from ( Select ISSUERID, RN, START_DATE, trunc(start_date), START_TIME, END_DATE, END_TIME, P_FROM, P_UNTILL, FIRST_VALUE(END_DATE) OVER(Partition By ISSUERID, TRUNC(START_DATE) Order By START_DATE Rows Between 1 Preceding And Current Row) as Selected_End_Date, CASE -- first and last row already calculated WHEN RN = 1 Or RN = Max(RN) OVER(Partition By ISSUERID) THEN MINS ELSE -- else --> second calculation for rows that are not first nor last Round((START_DATE - FIRST_VALUE(END_DATE)OVER(Partition By ISSUERID, TRUNC(START_DATE) Order By START_DATE Rows Between 1 Preceding And Current Row)) * 24 * 60,0) END "MINS" From my_tbl ) where mins > 0