grouping datetime results - 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

General SQL Server Forums

New to SQL Server Programming

grouping datetime results

Author

Topic

mmcfar
Starting Member

8 Posts

Posted - 2009-09-16 : 09:43:43

I have a list of datetimes when users check in and out I would
like to order these by userID, TimeMark. If there is an H,P or
V in the modified column (not a space or an *) they should be
grouped together for the date. This would make the rows go IN,
OUT, IN, OUT... Not IN, IN, OUT, OUT... I could do it after
getting the table back in C# but it would be nice to eliminate
the extra code with the right select.

CREATE TABLE #testit (
TimeMark datetime NOT NULL,
SiteID int NOT NULL,
UserID int NOT NULL,
InOut nchar(3) NOT NULL,
Modified char(1))

INSERT INTO #testit (TimeMark, SiteID, UserID, InOut, Modified)

SELECT 'Aug 15 2009 2:15PM','6','15638','IN ',' ' UNION ALL
SELECT 'Aug 15 2009 2:30PM','6','15638','OUT',' ' UNION ALL
SELECT 'Aug 18 2009 9:00AM','6','15638','IN ',' ' UNION ALL
SELECT 'Aug 18 2009 5:00PM','6','15638','OUT',' ' UNION ALL
SELECT 'Aug 18 2009 9:00AM','3','15641','IN ','H' UNION ALL
SELECT 'Aug 18 2009 5:00PM','3','15641','OUT','H' UNION ALL
SELECT 'Aug 21 2009 9:00AM','6','15638','IN ',' ' UNION ALL
SELECT 'Aug 21 2009 5:00PM','6','15638','OUT',' ' UNION ALL
SELECT 'Aug 28 2009 9:00AM','6','15638','IN ',' ' UNION ALL
SELECT 'Aug 28 2009 5:00PM','6','15638','OUT',' ' UNION ALL
SELECT 'Aug 20 2009 8:00AM','6','15638','IN ','*' UNION ALL
SELECT 'Aug 20 2009 2:00PM','6','15638','OUT','*' UNION ALL
SELECT 'Aug 20 2009 5:00PM','6','15638','OUT','*' UNION ALL
SELECT 'Aug 20 2009 3:00PM','6','15638','IN ','*' UNION ALL
SELECT 'Aug 18 2009 9:00AM','7','15644','IN ',' ' UNION ALL
SELECT 'Aug 18 2009 5:00PM','7','15644','OUT',' ' UNION ALL
SELECT 'Aug 18 2009 9:00AM','3','15647','IN ','*' UNION ALL
SELECT 'Aug 18 2009 5:15PM','3','15647','OUT','*' UNION ALL
SELECT 'Aug 18 2009 9:00AM','3','15641','IN ',' ' UNION ALL
SELECT 'Aug 18 2009 5:00PM','3','15641','OUT',' ' UNION ALL
SELECT 'Aug 18 2009 9:00AM','6','15638','IN ','H' UNION ALL
SELECT 'Aug 18 2009 5:00PM','6','15638','OUT','H'

Select * FROM #testIt ORDER BY UserID, TimeMark, SiteID

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-16 : 09:46:44

if you are looking at grouping by datetimes you can always just use -

SELECT convert(datetime, convert(varchar(8), dbo.fLocalTime(DateColumnGoesHere),112))

That will group by Day?

Not sure if thats what you are after, or exactly what you are after.

I would use a temporary table for everything ever

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 09:47:04

So hw shud the output look?

PBUH

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 09:56:48

Maybe this
Select * FROM #testIt ORDER BY UserID, datepart(dd,TimeMark), SiteID

PBUH

mmcfar
Starting Member

8 Posts

Posted - 2009-09-16 : 10:04:48

The ouput should look like the this, with the records that have an H grouped together. Still by ordered by userID, then date, then site.

8/15/2009 2:15:00 PM 6 15638 IN
8/15/2009 2:30:00 PM 6 15638 OUT
8/18/2009 9:00:00 AM 6 15638 IN
8/18/2009 5:00:00 PM 6 15638 OUT
8/18/2009 9:00:00 AM 6 15638 IN H
8/18/2009 5:00:00 PM 6 15638 OUT H
8/20/2009 8:00:00 AM 6 15638 IN *
8/20/2009 2:00:00 PM 6 15638 OUT *
8/20/2009 3:00:00 PM 6 15638 IN *
8/20/2009 5:00:00 PM 6 15638 OUT *
8/21/2009 9:00:00 AM 6 15638 IN
8/21/2009 5:00:00 PM 6 15638 OUT
8/28/2009 9:00:00 AM 6 15638 IN
8/28/2009 5:00:00 PM 6 15638 OUT
8/18/2009 9:00:00 AM 3 15641 IN
8/18/2009 5:00:00 PM 3 15641 OUT
8/18/2009 9:00:00 AM 3 15641 IN H
8/18/2009 5:00:00 PM 3 15641 OUT H
8/18/2009 9:00:00 AM 7 15644 IN
8/18/2009 5:00:00 PM 7 15644 OUT
8/18/2009 9:00:00 AM 3 15647 IN *
8/18/2009 5:15:00 PM 3 15647 OUT *

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 10:10:27

Did u try my last suggestion?

Select * FROM #testIt ORDER BY UserID, datepart(dd,TimeMark), SiteID

I guess it will gets u the desired results.

PBUH

mmcfar
Starting Member

8 Posts

Posted - 2009-09-16 : 10:35:09

It is very close, in a larger dataset which span a month or when there are multiple IN or Out times per day they are out of order 8/18 comes before 7/26. In another case with multiple time.

7/26/2009 1:00 PM IN
7/26/2009 5:00 PM OUT
7/26/2009 12:00 PM OUT
7/26/2009 9:00 AM IN

I can provide data if it helps. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources