Narrowing data down... - 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)

Narrowing data down...

Author

Topic

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-04 : 17:51:27

I have this code:


SELECT     TOP (100) PERCENT EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM
FROM         dbo.EMP AS EMP INNER JOIN
                      dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE     (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
ORDER BY EMP.LASTNAME, EVENTS.EVENTIME

which returns this sample set of data:


FIRST	LAST	EVENTIME			Status	SERIALNUM
David	Apples	2010-01-04 08:52:27.000	IN	1261125289
David	Apples	2010-01-04 11:50:19.000	IN	1261127354
David	Apples	2010-01-04 13:04:56.000	IN	1261128778
David	Apples	2010-01-04 13:21:58.000	OUT	1261129082
Michael	Bel	2010-01-04 08:47:18.000	IN	1261125215
Michael	Bel	2010-01-04 11:21:16.000	IN	1261127036
Michael	Bel	2010-01-04 12:00:54.000	OUT	1261127577

I cant seem to figure out how to widdle this down so that I get just 2 entries per person. 1 being the 1st IN of the day and the second being the last OUT of the day.
The data returned from my original query can have multiple days and will have multiple employees.
Any help would be appreciated.
TIA

Kanwulf
Starting Member

11 Posts

Posted - 2010-02-05 : 05:57:36

UNION ALL can help (I assume the first select you already have in place is in a table - instead of testx table you can put FROM ( your select ) alias_name:

First solution

select * from testx t1 where t1.eventime = (select min(eventime) from testx t2 where t2.firstname=t1.firstname and t2.lastname = t1.lastname
and t2.status = 'IN')
UNION ALL
select * from testx t1 where t1.eventime = (select mAX(eventime) from testx t2 where t2.firstname=t1.firstname and t2.lastname = t1.lastname
and t2.status = 'OUT')
order by firstname, lastname

Second One:
select firstname, lastname, min(eventime), status
from testx
where status = 'IN'
group by firstname, lastname, status
union all
select firstname, lastname, max(eventime), status
from testx
where status = 'OUT'
group by firstname, lastname, status
order by firstname, lastname

I suspect there might be a better way to do it, I will think about it. Hope this helps

Ionut Hrubaru

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 09:37:45

So you're saying I should run another separate query off the results from my original code? If so I would rather not do that. I would prefer to have one query statement containing all the code needed to do what I asked above.
Is this not possible?
TIA

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:44:55

You might be able to use CROSS APPLY to get the First and Last entries, for each person.

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 09:58:25

Im not an expert by any means so excuse my ignorance, but doesnt a CROSS APPLY only apply if you have a function involved? Since Im only pulling data from est. tables and then trying to manipulate that data I dont think CROSS APPLY applies. Perhaps im not fully grasping the little that I have read on it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:07:51

quote:
Originally posted by Biscuithead

I have this code:

SELECT TOP (100) PERCENT EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM
FROM dbo.EMP AS EMP INNER JOIN
dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
ORDER BY EMP.LASTNAME, EVENTS.EVENTIME

which returns this sample set of data:

FIRST LAST EVENTIME Status SERIALNUM
David Apples 2010-01-04 08:52:27.000 IN 1261125289
David Apples 2010-01-04 11:50:19.000 IN 1261127354
David Apples 2010-01-04 13:04:56.000 IN 1261128778
David Apples 2010-01-04 13:21:58.000 OUT 1261129082
Michael Bel 2010-01-04 08:47:18.000 IN 1261125215
Michael Bel 2010-01-04 11:21:16.000 IN 1261127036
Michael Bel 2010-01-04 12:00:54.000 OUT 1261127577

I cant seem to figure out how to widdle this down so that I get just 2 entries per person. 1 being the 1st IN of the day and the second being the last OUT of the day.
The data returned from my original query can have multiple days and will have multiple employees.
Any help would be appreciated.
TIA


SELECT FIRSTNAME,LASTNAME,EVENTIME,DEVID,SERIALNUM
FROM
(
SELECT     TOP (100) PERCENT 
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME,Status ORDER BY EVENTIME DESC) AS BSeq,
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME,Status ORDER BY EVENTIME) AS Seq ,
EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM
FROM         dbo.EMP AS EMP INNER JOIN
                      dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE     (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
ORDER BY EMP.LASTNAME, EVENTS.EVENTIME
)t
WHERE (Seq=1 AND Status='IN')
OR (BSeq=1 AND Status='OUT')

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 10:13:39

I suggest remove TOP 100 PERCENT (doesn't perform any useful function) and move the ORDER BY to the outer SELECT

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 10:34:45

I just noticed something my original code doesnt contain the edit I made to reflect the 'Status' column. I must have selected the code from the wrong query tab.:( sorry. The code below reflects what I used to generate that column. Perhaps that will help clear up any confusion:


SELECT     TOP (100) PERCENT EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status,
                       EVENTS.SERIALNUM
FROM         dbo.EMP AS EMP INNER JOIN
                      dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE     (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
ORDER BY EMP.LASTNAME, EVENTS.EVENTIME

Working with visakh16s code and Kristens suggestion I get:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'OVER'.
And Im sure it has to do with the Status column but I cant figure out how to incorporate the "case" and get it to work.
Sorry about the mixup.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:45:40

[code]
SELECT FIRSTNAME,LASTNAME,EVENTIME,DEVID,SERIALNUM
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END ORDER BY EVENTIME DESC) AS BSeq,
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END ORDER BY EVENTIME) AS Seq ,
EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status
FROM dbo.EMP AS EMP INNER JOIN
dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
)t
WHERE (Seq=1 AND Status='IN')
OR (BSeq=1 AND Status='OUT')
ORDER BY LASTNAME,EVENTIME
[/code]

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 11:01:01

AH, I was adding "AS Status" in the 1st 2 cases thats why it was barfing. Whats curious now is that I get what I was after however there is no status column but instead a DEVID column with #s instead of INs and OUTs. I see that you aliased DEVID to Status so Im confused as to why this is occuring?
Thanks for your help BTW.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:10:15

quote:
Originally posted by Biscuithead

AH, I was adding "AS Status" in the 1st 2 cases thats why it was barfing. Whats curious now is that I get what I was after however there is no status column but instead a DEVID column with #s instead of INs and OUTs. I see that you aliased DEVID to Status so Im confused as to why this is occuring?
Thanks for your help BTW.

if you need status instead devid do the tweak below


SELECT FIRSTNAME,LASTNAME,EVENTIME,DEVIDStatus,SERIALNUM
FROM
(
SELECT    
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END ORDER BY EVENTIME DESC) AS BSeq,
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END ORDER BY EVENTIME) AS Seq ,
EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status
FROM         dbo.EMP AS EMP INNER JOIN
                      dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE     (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
)t
WHERE (Seq=1 AND Status='IN')
OR (BSeq=1 AND Status='OUT')
ORDER BY LASTNAME,EVENTIME

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 11:12:34

Arg! Cant believe I missed that! Thanks so much visakh16 that got me exactly what I was looking for. You were a huge help!
One last question. If I wanted to get the 1st in and last out for each person for each day within the range specified would that be an easy modification?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:16:19

no problem
you're welcome
Glad that I could help you out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:25:09

quote:
Originally posted by Biscuithead

Arg! Cant believe I missed that! Thanks so much visakh16 that got me exactly what I was looking for. You were a huge help!
One last question. If I wanted to get the 1st in and last out for each person for each day within the range specified would that be an easy modification?

yup. that should be a small modification like


SELECT FIRSTNAME,LASTNAME,EVENTIME,Status,SERIALNUM
FROM
(
SELECT    
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END
,DATEADD(dd,DATEDIFF(dd,0,EVENTIME),0) ORDER BY EVENTIME DESC) AS BSeq,
ROW_NUMBER() OVER (PARTITION BY EMP.FIRSTNAME, EMP.LASTNAME, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END
,DATEADD(dd,DATEDIFF(dd,0,EVENTIME),0) ORDER BY EVENTIME) AS Seq ,
EMP.FIRSTNAME, EMP.LASTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM, CASE WHEN EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status
FROM         dbo.EMP AS EMP INNER JOIN
                      dbo.EVENTS AS EVENTS ON EMP.ID = EVENTS.EMPID
WHERE     (EVENTS.EVENTIME >= CONVERT(DATETIME, '2010-01-04 00:00:00', 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, '2010-01-05 00:00:00', 120))
)t
WHERE (Seq=1 AND Status='IN')
OR (BSeq=1 AND Status='OUT')
ORDER BY LASTNAME,EVENTIME

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-05 : 12:02:23

You're awesome! Thanks a ton visakh16! Have a great weekend!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:04:25

welcome again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources