May 8, 2009 at 11:51 am
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 ... Helo!
Thanks !
Berggy
May 8, 2009 at 12:00 pm
(For future reference, it's helpful if you provide the kind of test set-up that I have at the beginning of the solution below.)
Try this:
-- Set up Test Data
create table #T (
RecordNumber int primary key,
Badge_Num int not null,
Time_In datetime not null,
Time_Out datetime null);
insert into #T (RecordNumber, Badge_Num, Time_In, Time_Out)
select 234, 1023, '5/8/2009 10:29:03', NULL union all
select 256, 1023, '5/8/2009 10:29:03', '5/8/2009 10:47:23' union all
select 305, 1023, '5/8/2009 10:47:23', NULL union all
select 325, 1023, '5/8/2009 10:47:23', '5/8/2009 11:13:07' union all
select 434, 1024, '5/8/2009 10:29:03', NULL union all
select 456, 1024, '5/8/2009 10:29:03', '5/8/2009 10:47:23' union all
select 505, 1024, '5/8/2009 10:47:23', NULL;
--
-- Test Query
;with CTE as
(select row_number() over
(partition by Badge_Num
order by Time_In desc, Time_Out desc) as Row,
Badge_Num,
RecordNumber
from #T)
select Badge_Num, RecordNumber
from CTE
where Row = 1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2009 at 4:53 pm
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!
May 8, 2009 at 5:23 pm
Did you try creating the view by issuing the CREATE VIEW statement? Or are you using the view designer?
Also, you should know that the TOP (100) PERCENT and ORDER BY statements will be ignored when selecting from your view. The order by is only used to satisfy the TOP - but that does not guarantee the order of the rows returned from the view. To make sure the rows returned from the view have a specific order - you need to include the order by in the query that selects from the view.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 8, 2009 at 6:28 pm
I'm using view designer (New View) in management studio
I'm used to it not likeing my ORDER BYs ... but it does remember them and use them.
May 8, 2009 at 8:04 pm
Right - so, to get around the issue with the view designer - open a new query window and write the create statement yourself. You won't get the error and you can remove the TOP/ORDER BY.
I just want to be clear - selecting from the view will not return results in the order specified all the time. It may appear that way right now, but tomorrow it can (and will) change depending upon a lot of factors. The order is only guaranteed when you use the ORDER BY on the query selecting from the view.
Example:
CREATE VIEW dbo.MyNewView AS
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
GO
Now, if we do this:
SELECT ... FROM dbo.MyNewView;
The results are not guaranteed to be ordered. The only way to guarantee an order is to do the following:
SELECT ... FROM dbo.MyNewView ORDER BY Badge_Num;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2009 at 2:07 pm
Glad I could help. Sorry about it taking a while to get. That possibility didn't occur to me, or I'd have explained it a bit.
As already mentioned, don't both with Order By in view definitions. Doesn't actually do what you would think it does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2009 at 10:55 am
And after a few more hours on the massage table we get the final product:
SELECT Badge_Num, RecordNum
FROM (SELECT (row_number() OVER (PARTITION BY Badge_Num
ORDER BY Time_In DESC, Time_Out DESC, TIME_STAMP DESC)) AS Row, Badge_Num, ShopOrderLogKey AS RecordNum
FROM (SELECT TOP (100) PERCENT BADGE_NUM, TIME_IN, TIME_OUT, TIME_STAMP, SHOPORDERLOGKEY
FROM dbo.ShopOrderLog
WHERE (LUNCH IS NULL) AND (TIME_IN > GETDATE() - 0.59 OR
TIME_IN IS NULL) AND (TIME_OUT > GETDATE() - 0.58 OR
TIME_OUT IS NULL) AND (BADGE_NUM IS NOT NULL) AND (TIME_STAMP > GETDATE() - 0.58 OR
TIME_STAMP IS NULL)
ORDER BY BADGE_NUM) AS Wilma) AS Fred
WHERE Row = 1
May 12, 2009 at 11:48 am
Take the Top and Order By out of the sub-query. They're not doing anything useful in there, and SQL will ignore them anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply