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.
| Author |
Topic |
|
tvlach
Starting Member
3 Posts |
 Posted - 2012-03-12 : 07:38:12
|
| Hi to all,Iam begginer to SQL programming and want your help.I have a table rc which holds data from a time attentant, with the following columnsfname = First namelname = Last Namedatet = Day and time the employee scaned his entrance cardrtype = takes 2 values A if it is an entry and B if it is an exitI want to make a query to bring results with firstname, lastname time, of entry, time of exit of each employee, for the previous day.I made the followng script but I am sure the nested select i am using has a logical problem, but can not understand which one.SELECT fname, lname, (select datet from rc where rtype = A), (select datet from rc where rtype = B)FROM rcWHERE ([datet] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE()) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-12 : 08:06:08
|
You could do it like this:SELECT fname, lname, MIN(CASE WHEN rtype = 'A' THEN datet END) AS Arrival, MAX(CASE WHEN rtype = 'B' THEN datet END) AS DepartureFROM RCWHERE datet >= DATEADD(dd,DATEDIFF(dd,'19000101',GETDATE())-1,0) AND datet < DATEADD(dd,DATEDIFF(dd,'19000101',GETDATE()),0)GROUP BY fname, lname; Couple of observations though:1. If you have more than one employee with the same lastname/firstname combination this would break.2. If the employee checks in and out multiple times during the day, what do you want to get? This gives you the first check in time and the last check out time.3. The way you had written the query, you were calculating the check-in and check-out in the previous 24 hours. I assumed you want for the previous day rather than the 24 hour period.4. Won't you need to take into account weekends/holidays? If you are running this on a Monday morning and if Sunday was a holiday, what do you want to get? This would return you nothing at all if the employee did not come in on that day. |
 |
|
|
tvlach
Starting Member
3 Posts |
Posted - 2012-03-12 : 08:38:56
|
| Dear Sunita,Iam thankful for your immediate respose and assistanceThis a very basic and begginer approach to that I intend to do.For now is enough to run this query and take the results from 24 hours back.I intend to make a very simple application for that, in order a user to type the date he wants and to take the appropiarte results. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-12 : 09:24:29
|
| If you want the 24-hour period instead of yesterday, you should change the WHERE clause to what you had in your original query. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
tvlach
Starting Member
3 Posts |
Posted - 2012-03-12 : 10:12:35
|
| Now I test this query and i see that is not working properly.When using min and max funtions appears arrival time only to some of the employess who checked in . To the others arrival time is null. Also null is departure time to all of them.When I take out min and max functions and grouping beyond, appears the arrival time to all, but the departure time is again null. Plus creates additional rows for the same employee for departure time.Can not figure out what it is. Any help? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-12 : 10:14:33
|
| please elaborate issue with some sample data. Then we will able to understand your issue. Cant make out much from your explain as we dont know how data is------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Select twice the same column based in a condition