Click here to Skip to main content
15,945,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello...

my table table name "access_event_logs" column name is EventID.

the employee daily attendance Daily IN and OUT store value in Event ID...

table data like this..
1207 012   I.T    2014-03-02 08:47:07.000 IN  2014-03-24 10:47:02.000 2014-03-02 
1238 012   I.T    2014-03-02 14:49:57.000 OUT 2014-03-24 10:47:03.000 2014-03-02

my problem is how to count same date in and out .. iam developing windows application...
present-days value... in and out 1day calculate the day... how?

this
SQL
select * FROM access_event_logs
WHERE LOCALTIMESTAMP between '3/1/2014' And '3/30/2014' and (eventid ='in' and eventid = 'out')
and USERID ='012'


this query not working...
Posted
Updated 29-Mar-14 22:15pm
v2

hi, try this...
SQL
SELECT LOCALTIMESTAMP, SUM([IN]) as CountOfIn, SUM([OUT]) as CountOfOut
(
SELECT 
    LOCALTIMESTAMP, 
    Case when eventid ='in' then 1 else 0 end as [IN],  
    Case when eventid ='out' then 1 else 0 end as [OUT]
FROM 
    access_event_logs
WHERE 
    LOCALTIMESTAMP between '3/1/2014' And '3/30/2014' 
    and (eventid ='in' OR eventid = 'out')
    and USERID ='012'
) as a
Group by LOCALTIMESTAMP

Happy Coding!
:)
 
Share this answer
 
Comments
Maciej Los 30-Mar-14 6:43am    
Good job, Aarti!
Aarti Meswania 31-Mar-14 3:06am    
Thank you! Maciej Los :)
akhil.krish 30-Mar-14 6:59am    
Thanks for rply arathi.....
Aarti Meswania 31-Mar-14 3:06am    
Most welcome! :)
Try:
SQL
WHERE LOCALTIMESTAMP between '2014-03-01' And '2014-03-30' and 
 
Share this answer
 
Comments
akhil.krish 30-Mar-14 2:28am    
Thanks for rply...

same problem.. i want between the date present days.....some employee only (in) sum employee only (out) he forget thumb..so employee (in and out) then 1 present day...

so i want count query between date (in and out) same day...
Try this:
SQL
SELECT COUNT(*) FROM access_event_logs t1
WHERE  t1.userid='012' AND t1.eventid ='in' AND t1.LOCALTIMESTAMP BETWEEN '2014-03-01' And '2014-03-30'
AND EXISTS
(
SELECT * FROM access_event_logs t2
WHERE
t2.userid = t1.userid
AND
t2.eventid ='out'
AND
t2.LOCALTIMESTAMP = t1.LOCALTIMESTAMP
)

This will count days of the person who has 'in' and 'out' on same dates.
 
Share this answer
 
v3
Comments
akhil.krish 30-Mar-14 3:24am    
Thanks For rply Peter Leow..

i tried this query. output no column name.....

012 user id betwwen the date 2days only in for that dates....

u r query execute output is no column name......
Peter Leow 30-Mar-14 3:30am    
say we want the column to be 'days present' then:
SELECT COUNT(*) AS [days present] FROM access_event_logs t1...

Basically, I am giving you the direction and idea, you got to adapt it to your actual setup.
akhil.krish 30-Mar-14 3:36am    
actually my company using actatek thumb machine ...machine give database..

(acees_event_log) table this columns only...
,[USERID]
,[DEPARTMENT]
,[EVENTID]
,[TERMINALSN]
,[ACCESSMETHOD]
,[REMARKS]
,[LOCALTIMESTAMP]

type of this columns only
Peter Leow 30-Mar-14 3:41am    
I have just noticed one typo in my solution, it is t1.userid not t1.user.id
amended in solution
akhil.krish 30-Mar-14 3:44am    
yes.peter last time iam notice change the query.......
this solution working fine...

database change the datatype ... previous datatime... iam changing to "Date" so now working fine...

SQL
select count(LOCALTIMESTAMP) FROM access_event_logs t1
WHERE  t1.eventid ='in'  and userid ='012' and t1.LOCALTIMESTAMP between '3/1/2014' And '3/30/2014'
AND EXISTS(SELECT t1.LOCALTIMESTAMP FROM access_event_logs t2 WHERE t2.userid = t1.userid AND t2.eventid ='OUT'AND t2.LOCALTIMESTAMP = t1.LOCALTIMESTAMP)



Thanks reply for all.....:-)
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900