September 27, 2014 at 4:03 pm
An interesting scenario. Source Table stores history of events with TimeStamp:
CustomerID ChangeType OldValue NewValue TimeStamp
1 Change1 Off On 1/1/2014 0:00
2 Change1 Off On 1/2/2014 11:50
1 Change2 TRUE FALSE 1/2/2014 11:55
1 Change1 On Off 1/3/2014 9:00
2 Change1 On Off 1/3/2014 9:02
3 Change1 Off On 1/3/2014 9:06
4 Change6 1 2 1/3/2014 9:10
1 Change1 Off On 1/4/2014 9:11
1 Change4 N Y 1/5/2014 9:11
1 Change1 On Off 1/10/2014 0:01
2 Change1 Off On 1/10/2014 1:01
6 Change1 Off On 1/10/2014 2:02
3 Change1 On Off 1/12/2014 0:00
2 Change1 On Off 2/3/2013 2:04
I need to generate the following results in a single query without using temporary tables.
CustomerId ChangeType Start End
1 Change1 1/1/2014 0:00 1/3/2014 9:00
2 Change1 1/2/2014 11:50 1/3/2014 9:02
1 Change1 1/4/2014 9:11 1/10/2014 0:01
3 Change1 1/3/2014 9:06 1/12/2014 0:00
2 Change1 1/10/2014 1:01 2/3/2013 2:04
need to generate "Change1" records only where the new value is set to off with life span of the change i.e. then go to the previous record
where it got turned on and get the start date from there. The same customerId can have the same change type repeated mulitple times. So each off and then
the corresponding on for it.
Thanks in advance!
September 28, 2014 at 4:22 am
WITH cte AS
-- get all rows with OldValue ='Off' AND NewValue ='On'
-- this will include CustomerID=6
(
SELECT CustomerID, ChangeType, TimeStamp as Start
FROM Source_tbl
WHERE OldValue ='Off' AND NewValue ='On'
)
SELECT * FROM CTE
CROSS APPLY
-- for each row of the cte find the next corresponding TimeStamp with OldValue ='On' AND NewValue ='Off'
-- CROSS APPLY will exclude CustomerID=6 since there's no corresponding End value
-- to include CustomerID=6, use OUTER APPLY
(SELECT TOP 1 TimeStamp as End_
FROM Source_tbl s
WHERE cte.CustomerID = s.CustomerID
AND s.TimeStamp > cte.TimeStamp
AND OldValue ='On' AND NewValue ='Off'
ORDER BY TimeStamp
)x
September 28, 2014 at 5:43 am
Edit: not working.
An alternative window function based solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @CHANGE_TYPE VARCHAR(50) = 'Change1';
;WITH SAMPLE_DATA(CustomerID,ChangeType,OldValue,NewValue,TimeStamp) AS
( SELECT CustomerID,ChangeType,OldValue,NewValue,CONVERT(SMALLDATETIME,TimeStamp,0) FROM
(VALUES
(1,'Change1','Off','On','1/1/2014 0:00')
,(2,'Change1','Off','On','1/2/2014 11:50')
,(1,'Change2','TRUE','FALSE','1/2/2014 11:55')
,(1,'Change1','On','Off','1/3/2014 9:00')
,(2,'Change1','On','Off','1/3/2014 9:02')
,(3,'Change1','Off','On','1/3/2014 9:06')
,(4,'Change6','1','2','1/3/2014 9:10')
,(1,'Change1','Off','On','1/4/2014 9:11')
,(1,'Change4','N','Y','1/5/2014 9:11')
,(1,'Change1','On','Off','1/10/2014 0:01')
,(2,'Change1','Off','On','1/10/2014 1:01')
,(6,'Change1','Off','On','1/10/2014 2:02')
,(3,'Change1','On','Off','1/12/2014 0:00')
,(2,'Change1','On','Off','2/3/2013 2:04')
) AS X(CustomerID,ChangeType,OldValue,NewValue,TimeStamp)
)
,BASE_SET AS
(
SELECT
SD.CustomerID
,DENSE_RANK() OVER (PARTITION BY SD.CustomerID,SD.NewValue ORDER BY SD.TimeStamp ) AS CUST_DRNK
,SD.ChangeType
,CASE WHEN SD.NewValue = 'ON' THEN SD.TimeStamp END AS START_DATE
,CASE WHEN SD.NewValue = 'OFF' THEN SD.TimeStamp END AS END_DATE
FROM SAMPLE_DATA SD
WHERE SD.ChangeType = @CHANGE_TYPE
)
,FINAL_SET AS
(
SELECT
BS.CustomerID
,BS.ChangeType
,BS.START_DATE AS X_START_DATE
,MAX(BS.START_DATE) OVER (PARTITION BY BS.CustomerID, BS.CUST_DRNK) AS START_DATE
,MAX(BS.END_DATE) OVER (PARTITION BY BS.CustomerID, BS.CUST_DRNK) AS END_DATE
FROM BASE_SET BS
)
SELECT
FS.CustomerID
,FS.ChangeType
,FS.START_DATE
,FS.END_DATE
FROM FINAL_SET FS
WHERE FS.X_START_DATE IS NOT NULL
/* Comment out the next line to show open sessions */
AND FS.END_DATE IS NOT NULL
;
Results
CustomerID ChangeType START_DATE END_DATE
----------- ---------- ----------------------- -----------------------
1 Change1 2014-01-01 00:00:00 2014-01-03 09:00:00
1 Change1 2014-01-04 09:11:00 2014-01-10 00:01:00
2 Change1 2014-01-02 11:50:00 2013-02-03 02:04:00
2 Change1 2014-01-10 01:01:00 2014-01-03 09:02:00
3 Change1 2014-01-03 09:06:00 2014-01-12 00:00:00
September 29, 2014 at 2:30 am
Gagandeep Singh (9/27/2014)
2 Change1 1/10/2014 1:01 2/3/2013 2:04
Really Start > End is OK? Explain please.
Try this quey to enumerate all intervals, including open ones. Restriction: no 'On's after 'On' or 'Off's after 'Off' odered by TimeStamp within CustomerID, ChangeType.
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @CHANGE_TYPE VARCHAR(50) = 'Change1';
;WITH SAMPLE_DATA(CustomerID,ChangeType,OldValue,NewValue,TimeStamp) AS
( SELECT CustomerID,ChangeType,OldValue,NewValue,CONVERT(SMALLDATETIME,TimeStamp,0) FROM
(VALUES
(1,'Change1','Off','On','1/1/2014 0:00')
-- ,(1,'Change1','Off','On','1/1/2014 11:50')
,(2,'Change1','Off','On','1/2/2014 11:50')
,(1,'Change2','TRUE','FALSE','1/2/2014 11:55')
,(1,'Change1','On','Off','1/3/2014 9:00')
,(2,'Change1','On','Off','1/3/2014 9:02')
,(3,'Change1','Off','On','1/3/2014 9:06')
,(4,'Change6','1','2','1/3/2014 9:10')
,(1,'Change1','Off','On','1/4/2014 9:11')
,(1,'Change4','N','Y','1/5/2014 9:11')
,(1,'Change1','On','Off','1/10/2014 0:01')
,(2,'Change1','Off','On','1/10/2014 1:01')
,(6,'Change1','Off','On','1/10/2014 2:02')
,(3,'Change1','On','Off','1/12/2014 0:00')
,(2,'Change1','On','Off','2/3/2013 2:04')
) AS X(CustomerID,ChangeType,OldValue,NewValue,TimeStamp)
)
, y AS
(
SELECT
SD.CustomerID
, SD.NewValue
, SD.OldValue
, SD.TimeStamp
, (2*row_number() OVER (PARTITION BY SD.CustomerID ORDER BY SD.TimeStamp )
- row_number() OVER (PARTITION BY SD.CustomerID, SD.NewValue ORDER BY SD.TimeStamp )) grp
FROM SAMPLE_DATA SD
WHERE SD.ChangeType = @CHANGE_TYPE
)
SELECT
isnull(y1.CustomerID, y2.CustomerID) CustomerID
,isnull(y1.NewValue, y2.OldValue) Value
,y1.TimeStamp as StartDate
,y2.TimeStamp as EndDate
FROM y y1
FULL JOIN y y2 ON y1.CustomerID = y2.CustomerID
AND y2.grp between y1.grp + 1 and y1.grp + 2
ORDER BY isnull(y1.CustomerID, y2.CustomerID), y1.TimeStamp
Replace FULL JOIN with JOIN to see only closed intervals
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply