Help with SQL command - 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

General SQL Server Forums

New to SQL Server Programming

Help with SQL command

Author

Topic

JAG2011
Starting Member

3 Posts

Posted - 2011-10-12 : 02:02:19

I am trying to write an SQL query which will provide the current status of employee attendance. For example, the raw database table looks like this:

TodayDate ScanTime Name Status
10/12/2011 10:00am Joe Bloggs In
10/12/2011 12:00pm Sam Day In
10/12/2011 12:00pm Joe Bloggs Out
10/12/2011 1:00pm Billy Blue In
10/12/2011 2:00pm Sam Day Out
10/12/2011 3:00pm Billy Blue Out

If I was to run the query at 3:10pm, this is what I would want returned. i.e. last transaction for each employee in the database

TodayDate ScanTime Name Status
10/12/2011 12:00pm Joe Bloggs Out
10/12/2011 2:00pm Sam Day Out
10/12/2011 3:00pm Billy Blue Out

This is the query I am starting with:

SELECT     TOP (100) PERCENT CONVERT(VARCHAR(10), date, 103) AS TodayDate, CONVERT(CHAR(8), time, 114) AS ScanTime, FirstName + LastName AS Name, ID, 
                      CASE TransType WHEN 8 THEN 'Out' WHEN 7 THEN 'In' END AS Status
FROM         dbo.TransactionReport AS TransactionReport
ORDER BY date, time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 02:07:21

[code]
SELECT TodayDate,ScanTime,Name,Status
FROM
(
SELECT TOP (100) PERCENT
ROW_NUMBER() OVER(pARTITION BY FirstName + LastName ORDER BY time DESC) AS Rn,date,time,
CONVERT(VARCHAR(10), date, 103) AS TodayDate, CONVERT(CHAR(8), time, 114) AS ScanTime, FirstName + LastName AS Name, ID,
CASE TransType WHEN 8 THEN 'Out' WHEN 7 THEN 'In' END AS Status
FROM dbo.TransactionReport AS TransactionReport
WHERE time<=@time and date=CAST(GETDATE() AS date)
)t
WHERE Rn=1
ORDER BY date, time
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JAG2011
Starting Member

3 Posts

Posted - 2011-10-12 : 03:20:59

Thanks, that worked perfectly. Now, are you able to modify it to work in a view? I am getting the message "order by clause is invalid in views"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 03:22:33

remove the ORDER BY clause when you create the view

KH
[spoiler]Time is always against us[/spoiler]

JAG2011
Starting Member

3 Posts

Posted - 2011-10-12 : 04:26:52

Excellent works great.

Now, hoping someone can extend the sql statement. At present, the SQL displays dates from today. What I would like is to remove the date restriction, but still maintain the most recent transaction for each employee.

So I guess, the most recent must be based on Date AND time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 05:06:09

[code]
SELECT TodayDate,ScanTime,Name,Status
FROM
(
SELECT TOP (100) PERCENT
ROW_NUMBER() OVER(pARTITION BY FirstName + LastName ORDER BY date DESC,time DESC) AS Rn,date,time,
CONVERT(VARCHAR(10), date, 103) AS TodayDate, CONVERT(CHAR(8), time, 114) AS ScanTime, FirstName + LastName AS Name, ID,
CASE TransType WHEN 8 THEN 'Out' WHEN 7 THEN 'In' END AS Status
FROM dbo.TransactionReport AS TransactionReport)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources