how can I find the gaps between overlapping or non overlapping date ranges in one day?-CodePudding

I have a table that shows the date and time whenever an issuer has called the service. I want to write a query to show in a specific day the requests of an specific issuer has not covered the 24 hours. I will be appreciated if someone can guide me. I am beginner at SQL. mht_issuer_revoked_call

i tried to partition by issuerid and order by startdate and use the lag to compare startdate and enddate with previous record and add a new start and end date but i think i cant get the answer this way.

select r.*,
        case
          when r.startdate > lag(r.enddate) over(partition by r.issuerid order by r.startdate) then r.startdate
          else min(r.startdate) over(partition by r.issuerid order by r.startdate)
        end startdate_new,
        case
          when lag(r.enddate) over(partition by r.issuerid order by r.startdate) is null then r.enddate
          when r.enddate <= lag(r.enddate) over(partition by r.issuerid order by r.startdate) then lag(r.enddate) over(partition by r.issuerid order by r.startdate)
          when r.enddate > lag(r.enddate) over(partition by r.issuerid order by r.startdate)  then r.enddate
        end enddate_new
  from mht_issuer_revoked_call r

CodePudding user response：

Not quite sure what you are trying to get exactly (there is no expected outcome), but maybe something like this could help:

Sample data

WITH 
    tbl AS
        (
            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 
        ),

Create CTE (day_tbl) transforming your dates and times to get you dayly events and to record possible extension to next day:

    day_tbl AS
        (   Select  
                ID,
                START_DATE "START_DATE", 
                ROW_NUMBER() OVER(Partition By ID Order By START_DATE) "CALL_NO",
                To_Char(START_DATE, 'hh24:mi:ss') "START_TIME",
                END_DATE "END_DATE", 
                To_Char(END_DATE, 'hh24:mi:ss') "END_TIME",
                --
                CASE 
                      WHEN (TRUNC(START_DATE) = TRUNC(END_DATE) And To_Char(END_DATE, 'hh24:mi:ss') = '00:00:00') 
                          OR 
                            (TRUNC(END_DATE) - TRUNC(START_DATE) = 1 AND To_Char(END_DATE, 'hh24:mi:ss') = '00:00:00')
                      THEN TRUNC(START_DATE)
                ELSE  END_DATE
                END "NEW_END_DATE",
                CASE 
                      WHEN  (TRUNC(START_DATE) = TRUNC(END_DATE) And To_Char(END_DATE, 'hh24:mi:ss') = '00:00:00') 
                          OR 
                            (TRUNC(END_DATE) - TRUNC(START_DATE) = 1 AND To_Char(END_DATE, 'hh24:mi:ss') = '00:00:00')
                      THEN '24:00:00'
                ELSE  To_Char(END_DATE, 'hh24:mi:ss')
                END "NEW_END_TIME",
                --
                CASE 
                      WHEN  (TRUNC(END_DATE) - TRUNC(START_DATE) = 1 AND To_Char(END_DATE, 'hh24:mi:ss') != '00:00:00')
                      THEN '00:00:00'
                END "NEXT_DAY_TIME_FROM",
                CASE 
                      WHEN  (TRUNC(END_DATE) - TRUNC(START_DATE) = 1 AND To_Char(END_DATE, 'hh24:mi:ss') != '00:00:00')
                      THEN To_Char(END_DATE, 'hh24:mi:ss')
                END "NEXT_DAY_TIME_UNTIL"
            From 
                tbl 
        )

Main SQL resulting with events per DAY/ID with information about first and last event times (00 - 24), continuity and extention;

SELECT
    ID, 
    START_DATE,
    CALL_NO,
    START_TIME "CALL_START_TIME", 
    NEW_END_TIME "CALL_END_TIME",
    MIN(START_TIME) OVER(Partition By ID) "DAY_FIRST_TIME",
    MAX(NEW_END_TIME) OVER(Partition By ID) "DAY_LAST_TIME",
    CASE 
        WHEN LAG(NEW_END_TIME, 1, START_TIME) OVER(Partition By ID Order By START_DATE) > START_TIME THEN 'OVERLAP'
        WHEN LAG(NEW_END_TIME, 1, START_TIME) OVER(Partition By ID Order By START_DATE) < START_TIME THEN 'GAP'
    END "CONTINUITY",
    NEXT_DAY_TIME_UNTIL "EXTENDS_TO_NEXT_DAY_TILL"
FROM
    day_tbl
ORDER BY
    ID,
    START_DATE

Result:

ID	START_DATE	CALL_NO	CALL_START_TIME	CALL_END_TIME	DAY_FIRST_TIME	DAY_LAST_TIME	CONTINUITY	EXTENDS_TO_NEXT_DAY_TILL
4	25-NOV-22	1	00:00:00	12:00:00	00:00:00	24:00:00
4	25-NOV-22	2	12:00:00	24:00:00	00:00:00	24:00:00
40	25-NOV-22	1	00:00:00	06:00:00	00:00:00	24:00:00
40	25-NOV-22	2	06:00:00	12:00:00	00:00:00	24:00:00
40	25-NOV-22	3	11:30:00	18:00:00	00:00:00	24:00:00	OVERLAP
40	25-NOV-22	4	18:30:00	19:30:00	00:00:00	24:00:00	GAP
40	25-NOV-22	5	19:31:00	24:00:00	00:00:00	24:00:00	GAP
50	25-NOV-22	1	00:00:00	12:00:00	00:00:00	23:55:00
50	25-NOV-22	2	11:00:00	01:30:00	00:00:00	23:55:00	OVERLAP	01:30:00
50	25-NOV-22	3	23:10:00	23:30:00	00:00:00	23:55:00	GAP
50	25-NOV-22	4	23:30:00	23:45:00	00:00:00	23:55:00
50	25-NOV-22	5	23:50:00	23:55:00	00:00:00	23:55:00	GAP

CodePudding user response：

It's a typical job for MATCH_RECOGNIZE:

WITH  tbl(id, start_date, end_date) AS
(
    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 
),
merged_tbl(id,start_date,end_date) AS (
    SELECT * FROM (
        SELECT t.*, TRUNC(start_date) as sd FROM tbl t
    )   
    MATCH_RECOGNIZE (
        PARTITION BY ID
        ORDER BY start_date, end_date
        MEASURES FIRST(start_date) AS start_date, MAX(end_date)-1/(24*3600) AS end_date
        PATTERN( merged* strt )
        DEFINE
            merged AS MAX(end_date) >= NEXT(start_date)
    ) 
),
alldates(dat) as (
    select start_date level-1 
    from (select min(trunc(start_date)) as start_date, max(trunc(end_date)) as end_date from merged_tbl)
    connect by start_date level-1 <= end_date
)
select a.*, t.id 
from alldates a
join merged_tbl t on dat between start_date and end_date
where end_date < dat 1-1/(24*3600)
    ;


DAT                         ID
------------------- ----------
25-11-2022 00:00:00         40
26-11-2022 00:00:00         50