Distinct problems.

OK heres what I have so far:

SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
ORDER BY dbo.EVENTS.EVENTIME

Works great, however, I need to display only one instance of each employee and that instance should be the latest instance found.

So instead of several differant emplyees with several different "IN" and "OUT" times:
EvenTime FirstName Last Name Ext ReaderDesc DevID Status

12/28/2006 7:22:01 AM Rechie Michael 75766 SE Glass Door 21 IN

12/28/2006 7:10:01 AM Rechie Michael 75766 SE Glass Door 21 OUT

12/28/2006 7:01:01 AM Rechie Michael 75766 SE Glass Door 21 IN

I just want the latest record for any given employee regardless of wether its status is "IN" or "OUT":

EvenTime FirstName Last Name Ext ReaderDesc DevID Status

12/28/2006 7:22:01 AM Rechie Michael 75766 SE Glass Door 21 IN

EVENTS.EMPID would be what I would want to be DISTINCT but I dont know how to use it in the code above and I dont know how Id specify DISTINCT based on latest time found.

Any help/direction would be greatly appreciated.

TIA,
Stue

You can do a group by on the firstname, lastname, and do a MAX(eventtime) and either a MIN or MAX on the status.|||

Thanks ndinakar,

but I am still having problems:

SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, MAX(dbo.EVENTS.EMPID) AS EMPI, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
GROUP BY dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID

I am using MAX(dbo.EVENTS.EMPID) instead of LASTNAME because we have 2 employees with the same last name and EMPID is unique.

With the above I am still getting the same results... Im totally confused.
Any ideas on what I am doing wrong?

TIA,
Stue

|||PS. using "MAX(eventime)" in place of MAX(dbo.EVENTS.EMPID) gave me the same results as well.|||Remove the EVENTIME from the GROUP BY list and use a MAX(EVENTIME) in the SELECT.|||

ndinakar:

Remove the EVENTIME from the GROUP BY list and use a MAX(EVENTIME) in the SELECT.

SELECT TOP (100) PERCENT dbo.EVENTS.EMPID, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, MAX(dbo.EVENTS.EVENTIME) AS TIME, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
GROUP BY dbo.EVENTS.EMPID, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID

Thanks Almost there. I still get dupe Employees but the difference is it just lists one instance per door entered. So if the employee enters one door A 10 times and door B 5 it list 2 records for that one employee 1 for each door. I am trying to cut it down to just one (the latest) instance regardless of door.

Thanks ndinakar,

Stue

|||Remove that column too from the GROUP BY and use a MAX or MIN on it in the SELECT.|||

OK here is what I did.(And I'm not impressed.)

View A:
SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS, dbo.EVENTS.EMPID, dbo.DEPT.NAME
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID INNER JOIN
dbo.DEPT ON dbo.UDFEMP.DEPT = dbo.DEPT.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
ORDER BY dbo.EVENTS.EVENTIME

View B:
SELECT TOP (100) PERCENT InOut.EMPID, InOut.EVENTIME, InOut.LASTNAME, InOut.FIRSTNAME, InOut.EXT, InOut.READERDESC, InOut.DEVID, InOut.STATUS,
InOut.NAME AS MName
FROM dbo.[VW-InOut] AS InOut INNER JOIN
(SELECT MAX(EVENTIME) AS maxET, EMPID
FROM dbo.EVENTS
GROUP BY EMPID) AS maxresults ON InOut.EMPID = maxresults.EMPID AND InOut.EVENTIME = maxresults.maxET
ORDER BY InOut.EVENTIME DESC

I couldnt figure out the double MAX that you suggested ndinakar so thats why I have the 2 views.

Problem is it works.... but works sloooooooooooooow... Im assuming due to the nested query.
Any ideas on how I can speed it up?

Any help would be much appreciated,
Thanks,

Stue

|||

How about this:

SELECT MAX(dbo.EVENTS.EVENTIME), dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID,CASEWHEN (dbo.EVENTS.DEVID ='23'OR dbo.EVENTS.DEVID ='24'OR dbo.EVENTS.DEVID ='25'OR dbo.EVENTS.DEVID ='26')THEN'OUT'ELSE'IN'END AS STATUSFROM dbo.READERINNERJOIN dbo.EVENTSON dbo.READER.READERID = dbo.EVENTS.DEVIDINNERJOIN dbo.UDFEMPINNERJOIN dbo.EMPON dbo.UDFEMP.ID = dbo.EMP.IDON dbo.EVENTS.EMPID = dbo.EMP.IDWHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) =CONVERT(CHAR,GETDATE(), 101))AND (dbo.EVENTS.EMPID <> 0)GROUP BY dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVIDORDER BY dbo.EVENTS.EVENTIME

|||

:( nope.

I get the error:

"Column "dbo.EVENTS.EVENTIME" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP By clause."

So I drop dbo.EVENTS.EVENTIME into the GROUP BY line and execute and it runs but with dupe names and a total of 641 records (should only be about 50 returned). I cant see what, if anything, the code did here.

I also tried getting rid of the ORDER BY dbo.EVENTS.EVENTIME line while making no other changes and it still returns dupes and a total of 207 rows (no dupe READERDESC data per employee, but still dupe employee records)

Thanks again ndinakar,
Stue

|||Can you post some sample data (15-20 rows)?|||

Eventime in the GROUP BY delete ORDER BY line:

Time LNAME FNAME EXT READERDESC DEVID STATUS

1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:07:55 AM BADGE (VickyT) TEMPORARY 79777 Front Door 22 IN
1/8/2007 10:16:07 AM BADGE (VickyT) TEMPORARY 79777 IT Room 15 IN
1/8/2007 10:01:33 AM BADGE (VickyT) TEMPORARY 79777 NW Glass Dr 14 IN
1/8/2007 9:10:01 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 10:05:39 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 11:50:57 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 11:48:43 AM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 8:39:16 AM Belt Michael 75721 Front Door 22 IN
1/8/2007 12:43:00 PM Belt Michael 75721 Front Door 22 IN
1/8/2007 10:03:39 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 10:46:40 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 8:39:44 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 9:02:10 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 10:47:23 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 12:36:26 PM Belt Michael 75721 Rdr 20 out Front Door 24 OUT
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 8:34:11 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 9:43:01 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 10:52:38 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 12:08:49 PM Berm Ricardo 75750 NE Glass Doors 5 IN

Delete EVENTIME reference from GROUP BY and take out ORDER BY as well:

Time LNAME FNAME EXT READERDESC DEVID STATUS

1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:07:55 AM BADGE (VickyT) TEMPORARY 79777 Front Door 22 IN
1/8/2007 10:16:07 AM BADGE (VickyT) TEMPORARY 79777 IT Room 15 IN
1/8/2007 10:01:33 AM BADGE (VickyT) TEMPORARY 79777 NW Glass Dr 14 IN
1/8/2007 11:50:57 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:43:00 PM Belt Michael 75721 Front Door 22 IN
1/8/2007 10:46:40 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 10:47:23 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 12:36:26 PM Belt Michael 75721 Rdr 20 out Front Door 24 OUT
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 12:08:49 PM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 12:14:40 PM Berm Ricardo 75750 NW Glass Dr 14 IN
1/8/2007 12:32:08 PM Berm Ricardo 75750 Rdr 20 out Front Door 24 OUT
1/8/2007 12:31:29 PM Berm Ricardo 75750 Rdr 7 out North Stairwell 25 OUT
1/8/2007 8:23:57 AM Berm Ricardo 75750 SE Glass Door 21 IN
1/8/2007 9:18:10 AM Boley Rick 75723 Front Door 22 IN
1/8/2007 11:53:19 AM Boley Rick 75723 IT Room 15 IN
1/8/2007 11:54:34 AM Boley Rick 75723 Rdr 20 out Front Door 24 OUT
1/8/2007 9:18:37 AM Boley Rick 75723 SW Glass Dr 1 IN

Thanks again,
Stue

|||And what is the result you expect from your query based on your second set of data?|||

The 1st and second sets were results I got while trying to implement the suggestion you had given.

Ultimately I would like to get from my view (see second set above as reference):
1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 12:32:08 PM Berm Ricardo 75750 Rdr 20 out Front Door 24 OUT
1/8/2007 11:54:34 AM Boley Rick 75723 Rdr 20 out Front Door 24 OUT

So just the latest date/time instance for ea employee, regardless of where, status, etc... is displayed. So if there are 50 employees that day there should only be a total of 50 records returned.

Thanks,
Stue

|||

Looks like you only need to group by first and last names and put min or max around the other columns.

sample:

SELECT MAX(dbo.EVENTS.EVENTIME), dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME,min(dbo.UDFEMP.EXT),min(dbo.READER.READERDESC), min(dbo.EVENTS.DEVID),CASEWHEN (Min(dbo.EVENTS.DEVID) ='23'ORmin(dbo.EVENTS.DEVID) ='24'ORmin(dbo.EVENTS.DEVID) ='25'ORmin(dbo.EVENTS.DEVID) ='26')THEN'OUT'ELSE'IN'END AS STATUSFROM dbo.READERINNERJOIN dbo.EVENTSON dbo.READER.READERID = dbo.EVENTS.DEVIDINNERJOIN dbo.UDFEMPINNERJOIN dbo.EMPON dbo.UDFEMP.ID = dbo.EMP.IDON dbo.EVENTS.EMPID = dbo.EMP.IDWHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) =CONVERT(CHAR,GETDATE(), 101))AND (dbo.EVENTS.EMPID <> 0)GROUP BY dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAMEORDER BY dbo.EVENTS.EVENTIME