grouping info that appears sequentially and then has a break
-
Hello, I'm trying to figure out how best way to group records that are consecutive. For example, I take service calls for my company, and I put an entry in my time database each day on the days that I am covering the phones. I would like a query that groups and totals the days that I am doing service. So, suppose this is my data:
•4/1/12 - Covering Phones
•4/2/12 - Covering Phones
•4/3/12 - Covering Phones
•4/5/12 - Covering Phones
•4/6/12 - Covering Phones
•4/9/12 - Covering Phones
•4/10/12 - Covering Phones
•4/11/12 - Covering Phones
The report would show:
•3 days from 4/1/12-4/3/12
•2 days from 4/5/12-4/6/12
•3 days from 4/9/12-4/11/12
There are other places that I'd like to use a similar report (but for minutes rather than days). For example, we track error on conveyor locations for a customer. if there is an error, we output the timestamp and the location every 10 seconds for when the error is active. I'd like to do the similar grouping here where I show the length that an error stayed on and the start and stop time for each occurance. Thanks in advance for any help you can provide!
Ben
-
Welcome to sqlservercentral.com! I see that you are very new to this site. Please take time to read the general forum etiquittes on how to post your question to milk optimized, tested code from SQL stalwarts here[/url]
Now coming to your question, check if the following would suffice.
DECLARE @tab TABLE ( DatePresent DATETIME , Status VARCHAR(100))INSERT INTO @tab (DatePresent , Status)
SELECT '4/1/12' ,'Covering Phones'
UNION ALL SELECT '4/2/12' ,'Covering Phones'
UNION ALL SELECT '4/3/12' ,'Covering Phones'
UNION ALL SELECT '4/5/12' ,'Covering Phones'
UNION ALL SELECT '4/6/12' ,'Covering Phones'
UNION ALL SELECT '4/9/12' ,'Covering Phones'
UNION ALL SELECT '4/10/12' ,'Covering Phones'
UNION ALL SELECT '4/11/12' ,'Covering Phones'
; WITH Numbered AS
(
SELECT RN = ROW_NUMBER() OVER (ORDER BY T.DatePresent)
,T.DatePresent , T.Status
FROM @tab T
)
, rCTE AS
(
SELECT N.RN , N.DatePresent , Grp = 1
FROM Numbered N
WHERE N.RN = 1
UNION ALL
SELECT Base.RN , Base.DatePresent
, rC.Grp + CASE WHEN DATEDIFF(DD, rC.DatePresent, Base.DatePresent) = 1 THEN 0
ELSE 1
END
FROM Numbered Base
INNER JOIN rCTE rC
ON rC.RN + 1 = Base.RN
)
SELECT NumOfDays = COUNT(*)
,FromDate = MIN(R.DatePresent)
,ToDate = MAX(R.DatePresent)
FROM rCTE R
GROUP BY R.Grp
I must say this is one of the slow performing ugly solution, but there are far better super fast methods. Your request falls under the category of "Group Islands of dates". Pleae read this article (http://www.sqlservercentral.com/articles/T-SQL/71550/) which explains the different ways to tackles problems like this!
-
Here's another way
WITH CTE AS (
SELECT DatePresent, Status,
DatePresent - ROW_NUMBER() OVER(ORDER BY DatePresent) AS rnDiff
FROM @tab)
SELECT COUNT(*) AS Days,
MIN(DatePresent) AS FromDate,
MAX(DatePresent) AS ToDate
FROM CTE
GROUP BY Status,rnDiff;
____________________________________________________
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
-
Very cool. It's somewhat confusing to me, but it seems to work. To throw another little kink in there, I have multiple employees that do this. Is there a way to group by employee (which is part of the action record)?
Additionally, I tried running this i SSRS and got the error that "OVER" isn't supported (but it does still work). Thanks again,
Ben
-
Ignore my last post, I figured out that you need to use "partition by" as well. It's starting to make more sense. Thanks,
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply