Search code examples
sqlsql-serverrow-number

Find minimum date after maximum of different date

I have a table that contains all the dates of when a Gas supply was turned on or turned off. I want to obtain the minimum date it was turned off, between the most recent turn on date and the next turn on date before that, and the minimum date it was turned on, between the most recent turn off date and the next turn off date before that. I also want it to ignore the lines where the date for on and off is the same. (Not even sure I'm explaining it properly it's become that convoluted in my head, sorry!) Probably better showing examples than anything.

In image 1
I would want to return 02/11/2015 for the OFF and 23/12/2015 for the ON value.
IMAGE1

In Image 2
I want it to ignore the last two lines as they are the same date, and pull 28/07/2021 as OFF and 22/11/2021 as ON.
IMAGE2

In Image 3
I would want the date 13/08/2007 for when it was originally turned ON, and 26/06/2023 for the OFF Date.
IMAGE3

In Image 4
I would want the date 24/03/2011 for OFF and 11/04/2011 for ON.
IMAGE4

I have the data stored as per the images although they are all in the same table so each ID in those images are different but they are all in the one table.

Got as far as below then just lost it. Hope someone can help.

IF OBJECT_ID('TEMPDB.dbo.##tempROWS') IS NOT NULL DROP TABLE  ##tempROWS
SELECT ROW_NUMBER() OVER (ORDER BY ID)
your text`ROW_NUM, ID, Date, Status
INTO ##TEMPROWS
FROM ##tempunion  ---the main table`

CREATE TABLE ##temptablefin  (
[RId]        int ,
[ID]   nvarchar(9),
    [Date]  datetime,
[Status]   nvarchar(12),
    [MAXOFFDATE] datetime NULL,
    [MAXONDATE] datetime NULL,
    [MinRowNo] int
)

INSERT INTO ##temptablefin
SELECT 
ROW_NUM AS RID, 
ID, Date, Status
, '01/01/1990' AS MAXOFFDATE
, '01/01/1990' AS MAXONDATE
NULL AS MinRowNo

FROM ##TEMPROWS

IF OBJECT_ID('TEMPDB.dbo.##mincount') IS NOT NULL DROP TABLE  ##mincount;
Select MIN(Id) AS MINCount, ID , Status
INTO ##mincount 
FROM  ##temptablefin  GROUP BY ID, Status

UPDATE ##temptablefin 
SET MinRowNo = (SELECT MINCount FROM ##mincount mm WHERE mm.ID = ##temptablefin.ID and mm.Status =  ##temptablefin.Status)

select cc.ID, cc.Rid, cc.CertDate, cc.Status, cc.MAXOFFDATE, cc.MAXONDATE, cc.MinRowNo
,CASE WHEN cc.Status = 'ON' AND ff.Status = 'ON' THEN cc.Date ELSE ff.Date END AS ffs
,CASE WHEN cc.Status = 'CAPPED OFF' AND ff.Status = 'OFF' THEN cc.Date ELSE ff.Date END AS ffS2
,ff.Nid,ff.ID, ff.Date, ff.Status

from ##temptablefin cc
INNER JOIN (SELECT CASE WHEN (id) = MinRowNo THEN (id) Else (Rid+1) END AS Nid, ID, Status, CDate FROM ##temptablefin) ff
ON CC.RId = ff.Nid and cc.ID = ff.ID

ORDER BY cc.ID, cc.RId
Solution

  • The core of this solution is the use of partition by Lead and Lag. This allows looking at the next a previous values of a properly created record order within an id. In part, the code looks for sequential events of OFF and ON pairs that contain the most recent off. The Where clause at the end of the code removes all of the event that were related to generating the date pairs of interest.

    The temptablefin table in my MS SQL SERVER

    RId ID Date Status MAXOFFDATE MAXONDATE MinRowNo
    1 10010040 2007-08-13 00:00:00.000 ON NULL NULL NULL
    2 10010040 2008-07-17 00:00:00.000 ON NULL NULL NULL
    3 10010040 2009-02-23 00:00:00.000 OFF NULL NULL NULL
    4 10010040 2009-04-02 00:00:00.000 ON NULL NULL NULL
    5 10010040 2015-11-02 00:00:00.000 OFF NULL NULL NULL
    6 10010040 2015-12-23 00:00:00.000 ON NULL NULL NULL
    7 10010040 2016-11-02 00:00:00.000 ON NULL NULL NULL
    8 10071081 2021-07-28 00:00:00.000 OFF NULL NULL NULL
    9 10071081 2021-11-22 00:00:00.000 ON NULL NULL NULL
    10 10071081 2022-10-12 00:00:00.000 OFF NULL NULL NULL
    11 10071081 2022-10-12 00:00:00.000 ON NULL NULL NULL
    12 10140100 2007-08-13 00:00:00.000 ON NULL NULL NULL
    13 10140100 2012-05-29 00:00:00.000 ON NULL NULL NULL
    14 10140100 2018-07-26 00:00:00.000 ON NULL NULL NULL
    15 10140100 2020-02-01 00:00:00.000 ON NULL NULL NULL
    16 10140100 2023-06-26 00:00:00.000 OFF NULL NULL NULL
    17 NULL NULL NULL NULL NULL NULL 
    WITH
data
as
(
SELECT  [RId]
      ,[ID]
      ,[Date]
      ,[Status],

     LAG(date,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC) next_date,  -- if off status and part of an Off/On pair then next date is the on pair.
     LAG(id,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC) next_id,  -- if at most recent link, the next_id is 0
      (CASE when ROW_NUMBER() OVER (Partition By ID Order by (CASE WHEN STATUS = 'ON' then 0 else 1 end) DESC ) = 1 and status='OFF' then 1 else 0 end)as recent_off,  -- retrieve most recent off, sort off higher than on
      MIN(date) OVER (PARTITION BY ID  ) MIN_ON ,  -- minimum date calculated for use in business rule case when the most recent event for id is OFF

      case when
       (
        LEAD(status,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC) = 'OFF' and [Status] = 'ON'    -- the on is next to an off
        OR 
        LAG (status,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC) = 'ON' and [Status] = 'OFF')   -- the off is next to an on
     
        AND NOT ( date = LEAD(date,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC) or  date = LAG(date,1,0) OVER (PARTITION BY ID  ORDER BY DATE DESC,STATUS DESC))  -- not on same day
     
          then 1  else 0 end off_on_pair  -- looking for OFF/ON pair that are adjacent but not on the same day


  FROM [dbo].[temptablefin]
)
,
get_id_pairs
as
(
Select
-- get pair of results   pr_id1 and pr_id2
ID,
 DATE  pr_id1,   -- always select the most recent off status for an ID
case when  next_id = 0  then MIN_ON 
                 when  off_on_pair = 1  then next_date
                 end pr_id2 -- select the other status pair of the following ON event or the first ON event if the Id is currently off
from
data
where
recent_off = 1 and date is not null
)

select
fin.*
from
[temptablefin] fin
inner join
get_id_pairs gp
on
fin.id = gp.id and
fin.date in (pr_id1,pr_id2)

    Output:

    RId ID Date Status MAXOFFDATE MAXONDATE MinRowNo
    5 10010040 2015-11-02 00:00:00.000 OFF NULL NULL NULL
    6 10010040 2015-12-23 00:00:00.000 ON NULL NULL NULL
    8 10071081 2021-07-28 00:00:00.000 OFF NULL NULL NULL
    9 10071081 2021-11-22 00:00:00.000 ON NULL NULL NULL
    12 10140100 2007-08-13 00:00:00.000 ON NULL NULL NULL
    16 10140100 2023-06-26 00:00:00.000 OFF NULL NULL NULL