First and last of a particular record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

First and last of a particular record

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2014-04-08 : 17:36:23

I am looking for a way to show the first and last timestamp per type of activity per day in a list of records, so the first Start and last End per day:

DECLARE @DataTable TABLE
(
ActivityTime DATETIME,
ActivityType varchar(10),
UserName varchar(10)
)
INSERT INTO @DataTable VALUES
('2014-04-08 8:00:00.000', 'Start', 'Bob'),
('2014-04-08 8:03:00.000', 'Start', 'John'),
('2014-04-08 8:15:00.000', 'End', 'Bob'),
('2014-04-08 8:38:00.000', 'Start', 'Bob'),
('2014-04-08 8:41:00.000', 'End', 'John'),
('2014-04-08 8:42:00.000', 'End', 'Bob'),
('2014-04-08 8:50:00.000', 'Start', 'Bob'),
('2014-04-08 8:58:00.000', 'Start', 'John'),
('2014-04-08 9:10:00.000', 'End', 'John'),
('2014-04-08 9:12:00.000', 'End', 'Bob'),
('2014-04-09 8:01:00.000', 'Start', 'Bob'),
('2014-04-09 8:02:00.000', 'Start', 'John'),
('2014-04-09 8:10:00.000', 'End', 'Bob'),
('2014-04-09 8:22:00.000', 'Start', 'Bob'),
('2014-04-09 8:38:00.000', 'End', 'John'),
('2014-04-09 8:58:00.000', 'Start', 'John'),
('2014-04-09 9:25:00.000', 'End', 'John'),
('2014-04-09 9:33:00.000', 'End', 'Bob')

I would want the output to look like:

Bob, 2014-04-08 8:00:00.000, 2014-04-08 9:12:00.000
John, 2014-04-08 8:03:00.000, 2014-04-08 9:10:00.000
Bob, 2014-04-09 8:01:00.000, 2014-04-09 9:33:00.000
John, 2014-04-09 8:02:00.000, 2014-04-09 9:25:00.000

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-08 : 18:14:22

[code]

SELECT UserName,
MIN(CASE WHEN ActivityType = 'Start' THEN ActivityTime END) AS Min_Start,
MAX(CASE WHEN ActivityType = 'End' THEN ActivityTime END) AS Max_End
FROM @DataTable
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, ActivityTime), 0), UserName
ORDER BY
DATEADD(DAY, DATEDIFF(DAY, 0, ActivityTime), 0), UserName

[/code]

WebKill
Starting Member

32 Posts

Posted - 2014-04-08 : 18:27:32

Perfect, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources