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.
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.
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.
In Image 4
I would want the date 24/03/2011 for OFF and 11/04/2011 for ON.
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
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 |