query for time attendance - 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)

query for time attendance

Author

Topic

prapto
Starting Member

6 Posts

Posted - 2011-08-21 : 17:15:12

Somebody can help me
I have code and not result query

SELECT LASTNAME, FIRSTNAME, EVENTIME, DEVID, Status FROM (SELECT ROW_NUMBER() OVER (PARTITION BY EMP.LASTNAME, EMP.FIRSTNAME, CASE WHEN EVENTS.DEVID IN ("1", "2", "3", "4", "5", "6", "7") THEN "OUT" ELSE "IN" END, DATEADD(dd, DATEDIFF(dd, 0, EVENTIME), 0) ORDER BY EVENTIME DESC) AS BSeq, ROW_NUMBER() OVER (PARTITION BY EMP.LASTNAME, EMP.FIRSTNAME, CASE WHEN EVENTS.DEVID IN ("1", "2", "3", "4", "5", "6", "7") THEN "OUT" ELSE "IN" END, DATEADD(dd, DATEDIFF(dd, 0, EVENTIME), 0) ORDER BY EVENTIME ASC) AS Seq, EMP.LASTNAME, EMP.FIRSTNAME, EVENTS.EVENTIME, EVENTS.DEVID, EVENTS.SERIALNUM, CASE WHEN EVENTS.DEVID IN ("1", "2", "3", "4", "5", "6", "7") 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, "2011-07-01 00:00:00", 120)) AND (EVENTS.EVENTIME < CONVERT(DATETIME, "2011-08-30 00:00:00", 101)) AND CARDNUM = "3370") t WHERE (Seq=1 AND Status="IN") OR (BSeq=1 AND Status="OUT") ORDER BY LASTNAME, EVENTIME

My problem why status "IN" not result in query?

please help me
sory my english very badly

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-21 : 17:24:55

Let's start by making that readable...

SELECT  LASTNAME ,
        FIRSTNAME ,
        EVENTIME ,
        DEVID ,
        Status
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY EMP.LASTNAME,
                                        EMP.FIRSTNAME,
                                        CASE WHEN EVENTS.DEVID IN ( "1", "2",
                                                              "3", "4", "5",
                                                              "6", "7" )
                                             THEN "OUT"
                                             ELSE "IN"
                                        END,
                                        DATEADD(dd, DATEDIFF(dd, 0, EVENTIME),
                                                0) ORDER BY EVENTIME DESC ) AS BSeq ,
                    ROW_NUMBER() OVER ( PARTITION BY EMP.LASTNAME,
                                        EMP.FIRSTNAME,
                                        CASE WHEN EVENTS.DEVID IN ( "1", "2",
                                                              "3", "4", "5",
                                                              "6", "7" )
                                             THEN "OUT"
                                             ELSE "IN"
                                        END,
                                        DATEADD(dd, DATEDIFF(dd, 0, EVENTIME),
                                                0) ORDER BY EVENTIME ASC ) AS Seq ,
                    EMP.LASTNAME ,
                    EMP.FIRSTNAME ,
                    EVENTS.EVENTIME ,
                    EVENTS.DEVID ,
                    EVENTS.SERIALNUM ,
                    CASE WHEN EVENTS.DEVID IN ( "1", "2", "3", "4", "5", "6",
                                                "7" ) 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, "2011-07-01 00:00:00", 120) )
                    AND ( EVENTS.EVENTIME < CONVERT(DATETIME, "2011-08-30 00:00:00", 101) )
                    AND CARDNUM = "3370"
        ) t
WHERE   ( Seq = 1
          AND Status = "IN"
        )
        OR ( BSeq = 1
             AND Status = "OUT"
           )
ORDER BY LASTNAME ,
        EVENTIME

Can you explain the problem more?

--
Gail Shaw
SQL Server MVP

prapto
Starting Member

6 Posts

Posted - 2011-08-21 : 18:23:33

Sory my code can't readable clearly
more my problem is
I have table from finger accesscontrol and I want to query :

1. time first entry every day in 1 month for every persone
2. time last entry (exit) every day in 1 month for every perone

my table is:
dbo.EMP ==> ID, Firstname, Lastname
dbo.EVENTs ==> eventime (time when somebody take finger)
devid => (id finger print)
empid => (id from dbo.EMP)

Sory GilaMonster can help me
Thank's

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-21 : 21:43:29

why do you need that convert? why not pass dates like below


...
WHERE     ( EVENTS.EVENTIME >= '2011-07-01 00:00:00'
                    AND EVENTS.EVENTIME < '2011-08-30 00:00:00')

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

prapto
Starting Member

6 Posts

Posted - 2011-08-21 : 22:50:52

Thank Visakh16
I am copy paste from "http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139384"
because my problem same.

Mr. Visakh16 why status IN not result in query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-21 : 23:29:04

can you show some sample data? without that we cant say why its not returning status of IN

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

prapto
Starting Member

6 Posts

Posted - 2011-08-22 : 03:06:07

Mr. Visak
this sample data:
dbo.EVENTS
EVENTIME DEVID CARDNUM EMPID
2011-08-03 12:56:36.000 5 3370 7
2011-08-03 15:10:39.000 6 3370 7
2011-08-03 15:10:51.000 6 3370 7
2011-08-05 08:58:00.000 1 3370 7
2011-08-05 15:21:29.000 6 3370 7
2011-08-05 15:40:34.000 6 3370 7
2011-08-08 08:35:50.000 3 3370 7
2011-08-08 14:50:43.000 5 3370 7
2011-08-08 14:57:01.000 6 3370 7
2011-08-08 15:10:01.000 6 3370 7
2011-08-08 15:15:50.000 6 3370 7
2011-08-09 09:15:53.000 1 3370 7
2011-08-09 12:28:19.000 5 3370 7
2011-08-09 14:04:17.000 5 3370 7

dbo.EMP:
ID LASTNAME FIRSTNAME MIDLENAME
1 Lake Lisa A
3 Purwanto Sri NULL
4 Yusuf NULL NULL
6 Trimarsanto Hidayat NULL
7 Sukirman NULL NULL
8 Deniarsah Erman NULL
9 Syafruddin Din NULL
10 Setia Asih Puji Budi
11 Coutrier Farah N

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 03:16:14

the below condition

CASE WHEN EVENTS.DEVID IN ( "1", "2",
"3", "4", "5",
"6", "7" )
THEN "OUT"
ELSE "IN"
END

suggests any eventid from 1 to 7 should be regarded as OUT and you've only these values (no value > 6 in your sample data) in your event table thats why all comes along as OUT and you dont get any IN events.

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

prapto
Starting Member

6 Posts

Posted - 2011-08-22 : 03:30:32

Mr. Visakh have any solution for me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 04:33:27

using your sample data you wont get any records with IN status as you've considering eventids from 1 to 7 as type OUT and it has only data having values in this range in your EVENTS table.

So unless you change your case when condition you will not get any records with IN. How to change depends on your business rules which we're not aware of and have no visibility.

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

prapto
Starting Member

6 Posts

Posted - 2011-08-22 : 11:07:21

OK Thank Mr. Visakh solution for me. See you other time with other question. sory very bad my english.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 12:13:55

thats ok. no problem

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources