January 30, 2014 at 6:57 am
I have this example
DECLARE
@statusTABLE
(
AccountINT,
AStatusVARCHAR(20),
SDateDATETIME
)
INSERT INTO @status VALUES
(322702,'Active','2012-07-31 09:36:21.697'),
(322702,'Active','2012-08-24 04:57:42.890'),
(322702,'Active','2012-08-24 10:11:34.730'),
(322702,'Hold','2012-08-24 10:11:53.013'),
(322702,'Hold','2012-09-24 05:29:17.357'),
(322702,'Active','2012-09-24 07:06:49.580'),
(322702,'Active','2012-09-24 07:07:06.660'),
(322702,'Drop','2014-07-31 09:36:21.697')
;WITH Islands( Account, AStatus, SDate, RowNum ) AS
(
SELECT Account, AStatus, SDate, ROW_NUMBER() OVER ( PARTITION BY SDate ORDER BY AStatus )
FROM @status
)
SELECT *
FROM [Islands]
I'd like to end up with below - I cannot figure out how to correctly incorporate datetime into the partition logic.
Account StatusStartEnd
322702Active2012-07-31 09:36:21.6972012-08-24 10:11:53.013
322702Hold2012-08-24 10:11:53.0132012-09-24 07:06:49.580
322702Active2012-09-24 07:06:49.5802014-07-31 09:36:21.697
322702Drop2014-07-31 09:36:21.697NULL
January 30, 2014 at 9:05 am
Here's an option you could use to create your groups.
;WITH Islands( Account, AStatus, SDate, grouper ) AS
(
SELECT Account,
AStatus,
SDate,
ROW_NUMBER() OVER( ORDER BY SDate) -
ROW_NUMBER() OVER( PARTITION BY AStatus ORDER BY SDate) grouper
FROM @status
),
Grouped AS(
SELECT Account, AStatus, MIN(SDate) startdate, ROW_NUMBER() OVER( ORDER BY MIN(SDate)) rn
FROM [Islands]
GROUP BY Account, AStatus, grouper
)
SELECT g1.Account, g1.AStatus, g1.startdate, g2.startdate enddate
FROM Grouped g1
LEFT
JOIN Grouped g2 ON g1.rn = g2.rn - 1
ORDER BY g1.startdate
January 30, 2014 at 9:12 am
Using the analytic functions:
DECLARE
@statusTABLE
(
AccountINT,
AStatusVARCHAR(20),
SDateDATETIME
)
INSERT INTO @status VALUES
(322702,'Active','2012-07-31 09:36:21.697'),
(322702,'Active','2012-08-24 04:57:42.890'),
(322702,'Active','2012-08-24 10:11:34.730'),
(322702,'Hold','2012-08-24 10:11:53.013'),
(322702,'Hold','2012-09-24 05:29:17.357'),
(322702,'Active','2012-09-24 07:06:49.580'),
(322702,'Active','2012-09-24 07:07:06.660'),
(322702,'Drop','2014-07-31 09:36:21.697')
;WITH Islands( Account, AStatus, SDate, pStatus ) AS
(
SELECT Account, AStatus, SDate, LAG(AStatus,1,0) OVER (ORDER BY SDATE)
FROM @status
)
SELECT Account, AStatus, SDate AS [Start], LEAD(SDate) OVER (ORDER BY SDATE) AS [End]
FROM [Islands]
WHERE AStatus <> pStatus
About what you were trying. You can't partition by date .... partition is like grouping so that would not do much. You kind of want it partitioned by status and ordered by date, but it still doesn't work until you have island recognition (or you'd hop the islands).
To do it without analytics, you'd row_number over order by date with no partition, query with self joins to look at previous and next records (to find island starts and ends), and then build it that way. Basically the same as I did above, but with more steps.
EDIT: Or do it Luis's way, which is again. The main thing to know is that you need to order by date and then check for changes in status. Lots of ways to do that, but they all involve looking at adjacent records.
January 30, 2014 at 9:16 am
Nevyn (1/30/2014)
Using the analytic functions...
I need a 2012 instance at work 😀
January 30, 2014 at 10:23 am
Luis Cazares (1/30/2014)
Nevyn (1/30/2014)
Using the analytic functions...I need a 2012 instance at work 😀
SQL 2012 and the improved Windowing Functions TOTALLY ROCK from a functionality and coding perspective (once you learn them). They are unfortunately not so good from a performance standpoint, ESPECIALLY if you use the default RANGE windowing clause.
The OP has the quite common but RARELY COVERED Gaps/Islands scenario where there is no END POINT for each event. Almost all samples and spiffy solutions out there expect start/end pairs. Much of the world these days runs on just streaming data that only says "something started now", "something started now", etc. LAG/LEAD and GROUPING can get you to a place where you have start/end pairs as needed to use known Gaps/Island solutions. I wouldn't be surprised to learn there are some solutions that work directly with start-only data that are significantly more efficient. Maybe I will ask Itzik to use that in one of his SQL Mag articles (if he or others haven't already covered it).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2014 at 5:24 pm
Guys,
Those were great suggestions.
I solved it in a different way - which I will post - thanks for all of examples.
We are on 2008R ourselves, we are hopefully going right to 2014.
Prost,
Doug
January 30, 2014 at 5:48 pm
Douglas Osborne-229812 (1/30/2014)
Guys,Those were great suggestions.
I solved it in a different way - which I will post - thanks for all of examples.
We are on 2008R ourselves, we are hopefully going right to 2014.
Prost,
Doug
1) Look forward to your follow-up solution post!
2) Envious of your quick move to 2014. Need some reduced-rate consulting so I can have some fun in your new playground?!? 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply