查询以及时和时间查找总数 [英] Query to findout total in time and out time

问题描述

大家好



每日打击一名员工

Hi All

Daily punching of an employee

Type            Date                    Punch time
INPUNCH	        2015-01-02 00:00:00.000	1900-01-01 10:55:40.000
OUTPUNCH	2015-01-02 00:00:00.000	1900-01-01 14:35:36.000
INPUNCH	        2015-01-02 00:00:00.000	1900-01-01 15:15:10.000
OUTPUNCH	2015-01-02 00:00:00.000	1900-01-01 21:42:30.000
.
.
.

如何找到 intime 即（（OUTPUNCH1-INPUNCH1）+（OUTPUNCH2-INPUNCH2） .. +（OUPUNCHN-INPUNCHN）

停机时间即（（INPUNCH2-OUTPUNCH1）+（INPUNCH3-OUTPUNCH2） .. +（INPUNCHN-OUTPUNCHN-1）



如何查找总的时间和时间？

How to findout intime ie((OUTPUNCH1-INPUNCH1)+(OUTPUNCH2-INPUNCH2)..+(OUPUNCHN-INPUNCHN)
Outtime ie((INPUNCH2-OUTPUNCH1)+(INPUNCH3-OUTPUNCH2)..+(INPUNCHN-OUTPUNCHN-1)

How to findout total intime and out time ??

推荐答案

Try this and please note that it is not tested as I am away from my computer. This is to give you an idea for going about solving your problem.

with cte_inpunch(intime, rowid)
as
select dateadd(nanosecond, datepart(nanosecond,PunchTime), 
                          dateadd(second, datepart(second,PunchTime),
                                          dateadd(minute,datepart(minute,PunchTime),
                                                         dateadd(hour,datepart(hour , PunchTime),date)
                                          )
                          )
       ) as in_datetime,
       Row_Number() Over (Partition By PunchTime order By PunchTime) Rowid         
from tblTimeRecords

where type = 'INPUNCH'
;
with cte_outpunch(outtime, rowid)
as
select dateadd(nanosecond, datepart(nanosecond,PunchTime), 
                          dateadd(second, datepart(second,PunchTime),
                                          dateadd(minute,datepart(minute,PunchTime),
                                                         dateadd(hour,datepart(hour , PunchTime),date)
                                          )
                          )
       ) as out_datetime,
       Row_Number() Over (Partition By PunchTime order By PunchTime) Rowid         
from tblTimeRecords
where type = 'OUTPUNCH'

select sum(out.outtime - in.intime) as total_in_time , sum(in.intime - out.outtime) as total_out_time
from cte_intime in , cte_outtime out
where in.rowid = out.rowid

这篇关于查询以及时和时间查找总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！