如何编写计数查询sql server [英] how to write count query sql server

问题描述

你好...



我的表格名称access_event_logs列名是EventID。



员工每日出勤每日 IN和OUT 在事件ID中存储值...



表数据如同这个..

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

我的问题是如何计算相同的日期进出..我正在开发Windows应用程序...

现在的价值......进出1天计算那天......怎么样？



这个

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

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

推荐答案

试试这个......

try this...

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！

：）

Happy Coding!
:)

尝试：

Try:

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

试试这个：

Try this:

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.

这篇关于如何编写计数查询sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！