How do I code this? - 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 2005 Forums

Transact-SQL (2005)

How do I code this?

Author

Topic

Berggy
Starting Member

3 Posts

Posted - 2009-05-08 : 13:38:16

I need a query to select the Badge, record number of the person's last log entry, grouped by badge numbers.

1. All entries will have a time in.
2. Not all entries will have a time out.
3. The record number may or may not be in sequence:

Here I need it to return 1023, 325:
Table = Log
RecordNum, Badge_Num, Time_In, Time_Out
234, 1023, 5/8/2009 10:29:03, NULL
256, 1023, 5/8/2009 10:29:03, 5/8/2009 10:47:23
305, 1023, 5/8/2009 10:47:23, NULL
325, 1023, 5/8/2009 10:47:23, 5/8/2009 11:13:07

Here I need it to return 1023, 305:
Table = Log
RecordNum, Badge_Num, Time_In, Time_Out
234, 1023, 5/8/2009 10:29:03, NULL
256, 1023, 5/8/2009 10:29:03, 5/8/2009 10:47:23
305, 1023, 5/8/2009 10:47:23, NULL

Here I need it to return 1023, 225:
Table = Log
RecordNum, Badge_Num, Time_In, Time_Out
234, 1023, 5/8/2009 10:29:03, NULL
256, 1023, 5/8/2009 10:29:03, 5/8/2009 10:47:23
305, 1023, 5/8/2009 10:47:23, NULL
225, 1023, 5/8/2009 10:47:23, 5/8/2009 11:13:07

Here I need it to return 1023, 205:
Table = Log
RecordNum, Badge_Num, Time_In, Time_Out
234, 1023, 5/8/2009 10:29:03, NULL
256, 1023, 5/8/2009 10:29:03, 5/8/2009 10:47:23
205, 1023, 5/8/2009 10:47:23, NULL

REALLY messing with my brain ... Help!

Thanks !
Berggy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:53:10

[code]SELECT RecordNum, Badge_Num, Time_In, Time_Out
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Badge_num ORDER BY COALESCE(Time_out,Time_In) DESC,RecordNum ASC)AS Seq, RecordNum, Badge_Num, Time_In, Time_Out FROM YourTable
)t
WHERE t.Seq=1
[/code]

Berggy
Starting Member

3 Posts

Posted - 2009-05-08 : 18:55:14

WOW! ... took me a few hours to understand what this is doing ...
and a couple more to convert it to the actual use ... but man ... this is brilliant!

WITH TempTable AS (SELECT row_number() OVER (PARTITION BY Badge_Num
ORDER BY Time_In DESC, Time_Out DESC) AS Row, Badge_Num, ShopOrderLogKey AS RecordNum
FROM LastBeginSub1)
SELECT TOP (100) PERCENT Badge_Num, RecordNum
FROM TempTable
WHERE Row = 1
ORDER BY Badge_Num

When I save it to the view it gives me:
"The OVER SQL construct or statement is not supported."
But it does work!
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:51:28

Are you using sql 2005? whats the current compatibility level used? run below queries and report result
SELECT @@VERSION
exec sp_dbcmptlevel 'your db name'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-09 : 05:02:19

http://sqlblogcasts.com/blogs/madhivanan/archive/2009/01/05/compatibility-level-of-a-database.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources