September 23, 2015 at 8:55 am
I've been banging my head against this for days, and I'm getting nowhere. This seems like an obvious job for a window function, but I must not know enough about them. Consider this sample data, which I have simplified greatly. It shows who pressed the big red button, and when:
Name DateTimePressed
-------------------------------
Joe 2015-01-01 09:01:00.0000
Joe 2015-01-01 09:02:00.0000
Joe 2015-01-01 09:03:00.0000
Dave 2015-01-01 09:04:00.0000
Dave 2015-01-01 09:05:00.0000
Mary 2015-01-01 09:06:00.0000
Mary 2015-01-01 09:07:00.0000
Joe 2015-01-01 09:08:00.0000
Joe 2015-01-01 09:09:00.0000
I want to see the first and last time each person pressed the big red button consecutively. Like so:
Name PressedFirst PressedLast
--------------------------------------------------------
Joe 2015-01-01 09:01:00.0000 2015-01-01 09:03:00.0000
Dave 2015-01-01 09:04:00.0000 2015-01-01 09:05:00.0000
Mary 2015-01-01 09:06:00.0000 2015-01-01 09:07:00.0000
Joe 2015-01-01 09:08:00.0000 2015-01-01 09:09:00.0000
See how Joe has two groups, because Dave and Mary pressed the button in between his two sessions? That's where I'm getting tripped up. I have a feeling the answer is either very simple, or it's something I can't do in SQL 2008.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 23, 2015 at 9:22 am
This problem and its solution is explained in here: http://www.sqlservercentral.com/articles/T-SQL/71550/
The only difference is that the article uses days instead of minutes.
CREATE TABLE #RedButtonPress(
Name varchar(10),
DateTimePressed datetime2(4))
INSERT INTO #RedButtonPress
VALUES
('Joe ', '2015-01-01 09:01:00.0000'),
('Joe ', '2015-01-01 09:02:00.0000'),
('Joe ', '2015-01-01 09:03:00.0000'),
('Dave', '2015-01-01 09:04:00.0000'),
('Dave', '2015-01-01 09:05:00.0000'),
('Mary', '2015-01-01 09:06:00.0000'),
('Mary', '2015-01-01 09:07:00.0000'),
('Joe ', '2015-01-01 09:08:00.0000'),
('Joe ', '2015-01-01 09:09:00.0000');
WITH CTE AS(
SELECT *,
DATEADD(MI, - ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY DateTimePressed), DateTimePressed) AS DateGroup
FROM #RedButtonPress
)
SELECT Name,
MIN(DateTimePressed),
MAX(DateTimePressed)
FROM CTE
GROUP BY Name, DateGroup
GO
DROP TABLE #RedButtonPress
September 23, 2015 at 9:27 am
Thank you, Luis! I will be diving into that article and getting my head around it if it kills me! (It might.) 😀
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 23, 2015 at 9:32 am
Now that I'm starting to understand it, I can see that it relies on the quirk of my sample data that the timestamps are sequential by one minute. (That was me being lazy and short-sighted.) It doesn't work quite as well with this batch, which is more representative of what I'm working with:
('Joe ', '2015-01-01 09:01:01.0000'),
('Joe ', '2015-01-01 09:02:02.0000'),
('Joe ', '2015-01-01 09:03:03.0000'),
('Dave', '2015-01-01 09:06:06.0000'),
('Dave', '2015-01-02 09:09:09.0000'),
('Mary', '2015-01-02 09:15:15.0000'),
('Mary', '2015-01-02 09:22:22.0000'),
('Joe ', '2015-01-03 09:30:30.0000'),
('Joe ', '2015-01-04 09:45:45.0000');
But I'm guessing the article will get more into how to handle this. I'm still appreciative for the link! 😀
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 23, 2015 at 9:40 am
You'll need another ROW_NUMBER function.
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER ( ORDER BY DateTimePressed) - ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY DateTimePressed) AS DateGroup
FROM #RedButtonPress
)
SELECT Name,
MIN(DateTimePressed),
MAX(DateTimePressed)
FROM CTE
GROUP BY Name, DateGroup
Or you could find more solutions in here: https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
September 23, 2015 at 10:00 am
Okay, I have a lot of learning to do today. 🙂 Thank you, Luis!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply