how to write count query sql server

Question

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 29-Mar-14 19:48pm

akhil.krish

Updated 29-Mar-14 22:15pm

Aarti Meswania

v2

Add a Solution

4 solutions

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

Posted 29-Mar-14 22:50pm

Aarti Meswania

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

Solution 1

Try:

SQL

WHERE LOCALTIMESTAMP between '2014-03-01' And '2014-03-30' and

Posted 29-Mar-14 20:16pm

OriginalGriff

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

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.

Posted 29-Mar-14 20:55pm

Peter Leow

Updated 29-Mar-14 21:39pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

akhil.krish · Accepted Answer · 2014-03-30T01:01:00

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