15,945,119 members
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

## Solution 3

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!
:)

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! :)

## Solution 1

Try:
SQL
`WHERE LOCALTIMESTAMP between '2014-03-01' And '2014-03-30' and `

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...

## Solution 2

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.

v3
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.......

## Solution 4

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)```