Need further help please.. - 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)

Need further help please..

Author

Topic

Biscuithead
Starting Member

30 Posts

Posted - 2007-01-08 : 18:00:47

I have gotten to this point with some help but I am still not getting the data I need. Here is the code I have right now:
SELECT MAX(dbo.EVENTS.EVENTIME), dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, min(dbo.UDFEMP.EXT), min(dbo.READER.READERDESC),
min(dbo.EVENTS.DEVID), CASE WHEN (Min(dbo.EVENTS.DEVID) = '23' OR
min(dbo.EVENTS.DEVID) = '24' OR
min(dbo.EVENTS.DEVID) = '25' OR
min(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.EMP.LASTNAME, dbo.EMP.FIRSTNAME
ORDER BY dbo.EVENTS.EVENTIME

It yields the right # of rows but wrong data.
What I am trying to do is pull the LATEST row of data using EVENTS.EVENTIME to determine the latest time. However when I use the code above I dont get accurate data from the EVENTS.DEVID column. It seems I am getting the first record. I tried switchen MAX and MIN but to no avail.
Any Ideas?
TIA,
Stue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:43:37

This code does the same thing as above, but faster

SELECT		dbo.Emp.LastName,
		dbo.Emp.FirstName,
		MAX(dbo.Events.EvenTime) AS EvenTime,
		MIN(dbo.UdfEmp.Ext) AS Ext,
		MIN(dbo.Reader.ReaderDesc) AS ReaderDesc, 
		MIN(dbo.Events.DevID) AS DevID,
		CASE
			WHEN MIN(dbo.EVENTS.DEVID) IN ('23', '24', '25', '26') THEN 'OUT'
			ELSE 'IN'
		END AS Status
FROM		dbo.Reader
INNER JOIN	dbo.Events ON dbo.Events.DevID = dbo.Reader.ReaderID
INNER JOIN	dbo.Emp ON dbo.Emp.ID = dbo.Evens.EmpID
INNER JOIN	dbo.UdfEmp ON dbo.UdfEmp.ID = dbo.Emp.ID
WHERE		dbo.Events.EvenTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND dbo.Events.EvenTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
		AND dbo.Events.EmpID <> 0
GROUP BY	dbo.Emp.LastName,
		dbo.Emp.FirstName
ORDER BY	dbo.Events.EvenTime

Please define "last" !
Does EvenTime have time information embedded? Last days records only?

Please post some sample data, and your expected output based on your provided sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources