Search code examples
sqlloopsdatechronological

Iterating through chronological data using loop in SQL

enter image description here

I am trying to find a way of iterating through some chronological data, applying certain parameters along the way. I have a table containing Event Numbers, joined to each subsequent Service the customer received (in date order) following the Event. Initially I was using a Partition code and taking the earliest sequel Service to be the final sequel to each event, but I have since been instructed that the full outcome to an Event can contain multiple Services, provided the Services were no more than 3 days apart. So I now need some code that works through the chronology and finds each cluster of Services related to each Event.

So for Event 1: the first Service was provided the day after the Event ended and finished on 16/01/2022. Service 2 then started two days after this so is also in scope, Service 3 started the day after the end of Service 2 so needs including, but then there is a gap of two months before the fourth service the customer received so this would be deemed as unrelated to Event 1.

The ‘In Scope’ column is what I would want to create, to allow me to move each Event and it’s related follow-on Services into a new table, disregarding the rest. Is there any way of automating this process using a loop or some other feature? After dealing with Event 1, the code would need to move onto Event 2, Event 3, Event 4 etc in turn finding the relevant cluster of Services.

Code to build table above:

create table #Table
(Event_Number int
,Event_Start date
,Event_End date
,Sequel_Service_Number int
,Sequel_Service_Start_Date date
,Sequel_Service_End_Date date
,In_Scope Int
)

insert into #Table
(Event_Number
,Event_Start
,Event_End
,Sequel_Service_Number
,Sequel_Service_Start_Date
,Sequel_Service_End_Date
,In_Scope
)
values
(1, '2022-01-01', '2022-01-10', 1, '2022-01-11', '2022-01-16', 1)
,(1, '2022-01-01', '2022-01-10', 2, '2022-01-18', '2022-01-25', 1)
,(1, '2022-01-01', '2022-01-10', 3, '2022-01-26', '2022-01-30', 1)
,(1, '2022-01-01', '2022-01-10', 4, '2022-03-31', '2022-04-05', 0)
,(2, '2022-03-20', '2022-03-30', 1, '2022-03-31', '2022-04-05', 1)
,(2, '2022-03-20', '2022-03-30', 2, '2022-04-08', '2022-04-12', 1)
,(2, '2022-03-20', '2022-03-30', 3, '2023-01-01', '2023-01-30', 0)
,(2, '2022-03-20', '2022-03-30', 4, '2023-02-01', '2023-02-06', 0)
,(2, '2022-03-20', '2022-03-30', 5, '2023-04-05', '2023-04-12', 0)
,(2, '2022-03-20', '2022-03-30', 6, '2023-05-09', '2023-05-10', 0)
,(2, '2022-03-20', '2022-03-30', 7, '2023-05-10', '2023-05-15', 0)
;
Solution

  • You could use the LAG function to check if the difference between the service start date and the previous service end date is greater than 3, and set 0 if that is true and 1 if not. Then, use a running min function to get the desired output, (once the difference is more than 3 days, the flag is set to 0, and the running min will set the rest to 0).

    WITH check_gap AS
(
  SELECT *,
    CASE WHEN 
     DATEDIFF(
       DAY, 
       LAG(Sequel_Service_End_Date) OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date) , 
       Sequel_Service_Start_Date
     ) > 3 THEN 0 ELSE 1 END AS flag
  FROM #Table
)
SELECT Event_Number, 
       Event_Start, 
       Event_End, 
       Sequel_Service_Number, 
       Sequel_Service_Start_Date, 
       Sequel_Service_End_Date,
       MIN(flag) OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date) AS In_Scope
FROM check_gap
ORDER BY Event_Number, Sequel_Service_Start_Date

    Demo