Query to Combine Time Records - SQL Server Forums

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)

Query to Combine Time Records

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2011-12-13 : 16:09:18

Hello all,

I am scratching my head over this complicated situation. We have a phone system here which logs our users time by the half hour. It also will create new records on each log in or log out of each phone code they use. For example:

UserID DateStamp BeginTime EndTime ReasonCode
5859 2011-12-01 00:00:00.000 2011-12-01 12:01:12.000 2011-12-01 12:30:00.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 12:30:00.000 2011-12-01 13:00:00.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 13:00:00.000 2011-12-01 13:30:00.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 13:30:00.000 2011-12-01 14:00:00.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 14:00:00.000 2011-12-01 14:07:47.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 14:30:41.000 2011-12-01 14:46:38.000 1
5859 2011-12-01 00:00:00.000 2011-12-01 15:10:18.000 2011-12-01 15:11:59.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 15:54:17.000 2011-12-01 15:55:36.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 16:01:19.000 2011-12-01 16:30:00.000 2
5859 2011-12-01 00:00:00.000 2011-12-01 16:30:00.000 2011-12-01 17:00:00.000 2
5859 2011-12-01 00:00:00.000 2011-12-01 16:59:59.000 2011-12-01 17:05:14.000 2
5859 2011-12-01 00:00:00.000 2011-12-01 17:30:21.000 2011-12-01 17:31:30.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 18:05:03.000 2011-12-01 18:26:44.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 19:01:34.000 2011-12-01 19:18:20.000 1
5859 2011-12-01 00:00:00.000 2011-12-01 20:19:12.000 2011-12-01 20:25:51.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 10:24:21.000 2011-12-05 10:28:58.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 11:39:04.000 2011-12-05 11:51:38.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 12:33:11.000 2011-12-05 12:49:24.000 1
5859 2011-12-05 00:00:00.000 2011-12-05 14:01:19.000 2011-12-05 14:30:00.000 2
5859 2011-12-05 00:00:00.000 2011-12-05 14:30:00.000 2011-12-05 15:00:00.000 2
5859 2011-12-05 00:00:00.000 2011-12-05 15:00:00.000 2011-12-05 15:02:25.000 2
5859 2011-12-05 00:00:00.000 2011-12-05 15:50:09.000 2011-12-05 15:53:08.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 17:00:46.000 2011-12-05 17:19:39.000 1
5859 2011-12-06 00:00:00.000 2011-12-06 14:30:53.000 2011-12-06 14:48:07.000 1
5859 2011-12-06 00:00:00.000 2011-12-06 15:38:01.000 2011-12-06 15:41:35.000 9
5859 2011-12-06 00:00:00.000 2011-12-06 16:01:25.000 2011-12-06 16:30:00.000 2
5859 2011-12-06 00:00:00.000 2011-12-06 16:30:00.000 2011-12-06 17:00:00.000 2
5859 2011-12-06 00:00:00.000 2011-12-06 17:00:00.000 2011-12-06 17:02:31.000 2
5859 2011-12-06 00:00:00.000 2011-12-06 18:04:44.000 2011-12-06 18:11:36.000 9

I would like to combine consecutive records where the reason code is the same and the BeginTime for that consecutive record matches the current record's EndTime so that I see the very last EndTime for that code and the exact begintime all in one record, basically undo that half hour split it does as you can see above with the ReasonCode 4 on 12/01. My desired result would look like this:

UserID DateStamp BeginTime EndTime ReasonCode
5859 2011-12-01 00:00:00.000 2011-12-01 12:01:12.000 2011-12-01 14:07:47.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 14:30:41.000 2011-12-01 14:46:38.000 1
5859 2011-12-01 00:00:00.000 2011-12-01 15:10:18.000 2011-12-01 15:11:59.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 15:54:17.000 2011-12-01 15:55:36.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 16:01:19.000 2011-12-01 17:05:14.000 2
5859 2011-12-01 00:00:00.000 2011-12-01 17:30:21.000 2011-12-01 17:31:30.000 9
5859 2011-12-01 00:00:00.000 2011-12-01 18:05:03.000 2011-12-01 18:26:44.000 4
5859 2011-12-01 00:00:00.000 2011-12-01 19:01:34.000 2011-12-01 19:18:20.000 1
5859 2011-12-01 00:00:00.000 2011-12-01 20:19:12.000 2011-12-01 20:25:51.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 10:24:21.000 2011-12-05 10:28:58.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 11:39:04.000 2011-12-05 11:51:38.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 12:33:11.000 2011-12-05 12:49:24.000 1
5859 2011-12-05 00:00:00.000 2011-12-05 14:01:19.000 2011-12-05 15:02:25.000 2
5859 2011-12-05 00:00:00.000 2011-12-05 15:50:09.000 2011-12-05 15:53:08.000 9
5859 2011-12-05 00:00:00.000 2011-12-05 17:00:46.000 2011-12-05 17:19:39.000 1
5859 2011-12-06 00:00:00.000 2011-12-06 14:30:53.000 2011-12-06 14:48:07.000 1
5859 2011-12-06 00:00:00.000 2011-12-06 15:38:01.000 2011-12-06 15:41:35.000 9
5859 2011-12-06 00:00:00.000 2011-12-06 16:01:25.000 2011-12-06 17:02:31.000 2
5859 2011-12-06 00:00:00.000 2011-12-06 18:04:44.000 2011-12-06 18:11:36.000 9

Where the first 4 records combined into one date/time stamp with a begin time and end time.

The challenge is, each of the ReasonCodes are used multiple times in one day, so I can't just use a Min and Max for the day, such as with ReasonCode 4 above on 12/01. I have to break it down like above. Does anyone have any suggestions?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:36:00

you need to use this logic

http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vassago
Starting Member

33 Posts

Posted - 2011-12-14 : 15:17:07

That seems to work perfectly! Thanks for your assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 00:07:52

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources