Get min and max dates based on end flag
-
Hi
I have a result set which produces EndFlag for an activity.
The resultset is like below.
CustNo, ActivityStart, ActivityEndDate, ActivityEnd
ActivityEnd is derived based on current ActivityEndDate and next row's ActivityStartDate is > 7 hours ActivityEnd = 1
Now, I need to generate final resultset with CustNo, MIN(ActivityStartDate), MAX(ActivityEndDate) for each ActivityEnd = 1
Means if the ActivityEnd = 1 then we need to MIN(ActivityStart) where ActivityStartDate <= ActivityEndDate of ActivityEnd = 1
I am struggling to get this done.
Can anyone give an example
Sample Resultset and expected output as below.
CustNo ActivityStartDate ActivityEndDate DiffHours ActivityEnd
42 12/21/2006 11:35 12/21/2006 13:40 1 0
42 12/21/2006 14:10 12/21/2006 16:30 1 0
42 12/21/2006 17:00 12/21/2006 18:15 0 0
42 12/21/2006 18:45 12/21/2006 20:00 33 1
42 12/23/2006 07:00 12/23/2006 10:00 0 0
42 12/23/2006 10:30 12/23/2006 13:35 15 1
42 12/24/2006 07:00 12/24/2006 10:00 0 0
42 12/24/2006 10:30 12/24/2006 13:35 1 0
42 12/24/2006 14:30 12/24/2006 19:30 5 0
42 12/25/2006 00:00 12/26/2006 00:00 0 1
Need output as
CustNo ActivityStartDate ActivityEndDate
42 12/21/2006 11:35 12/21/2006 20:00
42 12/23/2006 07:00 12/23/2006 13:35
42 12/24/2006 07:00 12/26/2006 00:00
-
This isn't too bad to write. Any chance you can post ddl and sample data in a consumable format? (Create tables and inserts so we can hit f5 and get rolling).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
CREATE TABLE CustActivity(CustNo INT ,
ActivityStartDate DATETIME ,
ActivityEndDate DATETIME ,
DiffHours INT ,
ActivityEnd INT )
INSERT INTO CustActivity (CustNo,ActivityStartDate,ActivityEndDate,DiffHours,ActivityEnd )
SELECT'42','12/21/2006 11:35','12/21/2006 13:40','1','0'UNION
SELECT'42','12/21/2006 14:10','12/21/2006 16:30','1','0'UNION
SELECT'42','12/21/2006 17:00','12/21/2006 18:15','0','0'UNION
SELECT'42','12/21/2006 18:45','12/21/2006 20:00','33','1'UNION
SELECT'42','12/23/2006 07:00','12/23/2006 10:00','0','0'UNION
SELECT'42','12/23/2006 10:30','12/23/2006 13:35','15','1'UNION
SELECT'42','12/24/2006 07:00','12/24/2006 10:00','0','0'UNION
SELECT'42','12/24/2006 10:30','12/24/2006 13:35','1','0'UNION
SELECT'42','12/24/2006 14:30','12/24/2006 19:30','5','0'UNION
SELECT'42','12/25/2006 00:00','12/26/2006 00:00','0','1'
BASED ON ACTIVITY ID CHANGES , WORKS ON SQL 2012
;WITH CustActivityCTE
AS
(
SELECT CustNo,ActivityStartDate,ActivityEndDate,DiffHours,ActivityEnd ,
SUM (ActivityEnd) OVER( PARTITION BY CustNo ORDER BY CustNo,ActivityStartDate
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as GrpNo
FROM CustActivity
ORDER BY ActivityStartDate
)
SELECT CustNo, MIN(ActivityStartDate) AS ActivityStartDate , MAX(ActivityEndDate) AS ActivityEndDate
FROM CustActivityCTE
GROUP BY CustNo, GrpNo
BASED ON STARTDATE CHANGES , WORKS FROM SQL 2005
;WITH CustActivityCTEAS
(
SELECT CustNo,ActivityStartDate,ActivityEndDate,DiffHours,ActivityEnd ,
ROW_NUMBER() OVER( ORDER BY CustNo,ActivityStartDate ) -
ROW_NUMBER() OVER( PARTITION BY CustNo,CONVERT(VARCHAR(10),ActivityStartDate ,120)
ORDER BY CustNo,ActivityStartDate
) as GrpNo
FROM CustActivity
)
SELECT CustNo, MIN(ActivityStartDate) AS ActivityStartDate , MAX(ActivityEndDate) AS ActivityEndDate
FROM CustActivityCTE
GROUP BY CustNo, GrpNo
-
WITH CTE AS (
SELECT CustNo, ActivityStartDate, ActivityEndDate, DiffHours, ActivityEnd,
ROW_NUMBER() OVER(PARTITION BY CustNo ORDER BY ActivityStartDate) AS rn1,
ROW_NUMBER() OVER(PARTITION BY CustNo,ActivityEnd ORDER BY ActivityStartDate) AS rn2
FROM CustActivity)
SELECT CustNo,
MIN(ActivityStartDate) AS ActivityStartDate,
MAX(ActivityEndDate) AS ActivityEndDate
FROM CTE
GROUP BY CustNo,CASE WHEN ActivityEnd=0 THEN rn1-rn2 ELSE rn2-1 END
ORDER BY CustNo,ActivityStartDate;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Perfect 🙂 ..Kudos Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply