请高手解决关于考勤统计时间方面的问题，不解决就有可能被炒了，谢谢高手 谢谢（分不够改天加）

drop table signinoutdata

drop function dbo.ConvertToDateTime

drop table #signinoutdata

go



create function dbo.ConvertToDateTime (@indate datetime, @intime varchar(50)) returns datetime as

begin

    --this function takes the day part of indate and concatenate intime which is of time format

    --and forms a datetime value

    return convert(datetime, convert(varchar(10), year(@indate))+'-'+convert(varchar(10), month(@indate))+'-' + convert(varchar(10), day(@indate))+ ' ' +@intime)

end

go

declare @shiftendtime varchar(10) 

--this defines the closing time of the shift. I am only assuming 1 shift for the purpose of this exericse



set @shiftendtime='21:00PM'



create table SignInOutData

(

    recid int identity(1, 1),

    employeeid int,

    recorddate datetime,

	recordtype tinyint --1 = entry, 2 = exit

)

set identity_insert signinoutdata on

/*

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (4, 2038375602, '2010-03-08 17:30:05', 2)

*/



/*

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)   

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)

*/



insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)   

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)   

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (4, 2038375602, '2010-03-08 15:30:05', 2)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (5, 2038375602, '2010-03-08 16:29:05', 1)

insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (6, 2038375602, '2010-03-08 17:30:05', 2)  



set identity_insert signinoutdata off



create table #signinoutdata

(

	signinrecid int,

	employeeid int,

	signintime datetime,

	signoutrecid int,

	signouttime datetime

)



insert into  #signinoutdata

select

	a.recid signinrecid,

	a.employeeid,

	a.recorddate signintime,

	(

		select

			recid

		from

			signinoutdata

		where

			employeeid=a.employeeid and

			day(recorddate)=day(a.recorddate) and

			recordtype=2 and

			recid>a.recid and

			recid<(

					select 

						min(recid) 

					from 

						signinoutdata 

					where 

						employeeid=a.employeeid and

						day(recorddate)=day(a.recorddate) and

						recordtype=1 and

						recid>a.recid

			)

	) signoutrecid,

	(

		select

			recorddate

		from

			signinoutdata

		where

			employeeid=a.employeeid and

			day(recorddate)=day(a.recorddate) and

			recordtype=2 and

			recid>a.recid and

			recid<(

					select 

						min(recid) 

					from 

						signinoutdata 

					where 

						employeeid=a.employeeid and

						day(recorddate)=day(a.recorddate) and

						recordtype=1 and

						recid>a.recid

			)

	) signouttime

from

	signinoutdata a

where

	a.recordtype=1



insert into #signinoutdata

select

	case when a.recordtype=1 then a.recid else null end,

	a.employeeid,

	case when a.recordtype=1 then a.recorddate else null end,

	case when a.recordtype=2 then a.recid else null end,

	case when a.recordtype=2 then a.recorddate else null end

from

	SignInOutData a left join

	(

		select

			signinrecid recid

		from

			#signinoutdata

		union

		select

			signoutrecid

		from

			#signinoutdata

	) b on

		a.recid=b.recid

where

	b.recid is null



update

	#signinoutdata

set

	signintime=(case when signintime is null then signouttime else signintime end),

	signouttime=(case 

					when signouttime is not null then signouttime 

					when signouttime is null and signintime>dbo.ConvertToDateTime(signintime, @shiftendtime) then signintime

					else dbo.ConvertToDateTime(signintime, @shiftendtime)/*signouttime is null and signintime<=dbo.ConvertToDateTime(signintime, @shiftendtime)*/

				end)

where

	signintime is null or

	signouttime is null



select

	employeeid, day(signintime) work_day, sum(datediff(mi, signintime, signouttime)) total_working_time_in_minutes

from

	#signinoutdata

group by

	employeeid, day(signintime)

---1

if object_id('tb') is not null drop table tb

go

create table tb(id int identity(1,1),人员卡号 varchar(20), 考勤时间 datetime )



---2

insert tb

select '2038375602',' 2010-03-08 9:21:05' union all   

select '2038375602',' 2010-03-08 14:25:05' union all      

select '2038375602',' 2010-03-08 14:29:05' union all   

select '2038375602',' 2010-03-08 17:30:05'



select rowid=count(1),a.人员卡号 , a.考勤时间  

into #t

from tb a left join tb b 

on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间

group by a.人员卡号 , a.考勤时间

order by a.人员卡号 , a.考勤时间



select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！'else '' end,

工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60

from #t a left join  #t b  

on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1

group by a.人员卡号



/*人员卡号                                                  工作时间                 

----------------------------------------------------- -------------------- 

2038375602                                            8.016666



（所影响的行数为 1 行）*/



---3

truncate table tb

go

insert tb

select '2038375602',' 2010-03-08 9:21:05' union all   

select '2038375602',' 2010-03-08 14:25:05' union all      

select '2038375602',' 2010-03-08 14:29:05' 

go

drop table #t

go



select rowid=count(1),a.人员卡号 , a.考勤时间  

into #t

from tb a left join tb b 

on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间

group by a.人员卡号 , a.考勤时间

order by a.人员卡号 , a.考勤时间



select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！'else ''end,

工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60

from #t a left join  #t b  

on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1

group by a.人员卡号



/*人员卡号                                                  工作时间                 

----------------------------------------------------- -------------------- 

2038375602该卡刷3次,请查明问题！                                5.000000



（所影响的行数为 1 行）*/





---4

truncate table tb

go

insert tb

select '2038375602',' 2010-03-08 9:21:05' union all   

select '2038375602',' 2010-03-08 14:25:05' union all      

select '2038375602',' 2010-03-08 14:29:05' union all 

select '2038375602',' 2010-03-08 15:30:05' union all 

select '2038375602',' 2010-03-08 16:29:05' union all   

select '2038375602',' 2010-03-08 17:30:05'





go

drop table #t

go



select rowid=count(1),a.人员卡号 , a.考勤时间  

into #t

from tb a left join tb b 

on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间

group by a.人员卡号 , a.考勤时间

order by a.人员卡号 , a.考勤时间



select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！' else ''end,

工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60

from #t a left join  #t b  

on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1

group by a.人员卡号

/*

人员卡号                                                  工作时间                 

----------------------------------------------------- -------------------- 

2038375602                                            6.050000



（所影响的行数为 1 行）

*/

create function dbo.ConvertToDateTime (@indate datetime, @intime varchar(50)) returns datetime as

begin

	--this function takes the day part of indate and concatenate intime which is of time format

	--and forms a datetime value

	return convert(datetime, convert(varchar(10), year(@indate))+'-'+convert(varchar(10), month(@indate))+'-' + convert(varchar(10), day(@indate))+ ' ' +@intime)

end



/*

drop table SignInOutData

drop table #entryexit

drop table #EntryExitTime

*/



declare @shiftendtime varchar(10) 

--this defines the closing time of the shift. I am only assuming 1 shift for the purpose of this exericse



set @shiftendtime='21:00PM'



create table SignInOutData

(

	recid int identity(1, 1),

	employeeid int,

	recorddate datetime

)



set identity_insert signinoutdata on



insert into SignInOutData (recid, employeeid, recorddate) values (1, 2038375602, '2010-03-08 9:21:05')

insert into SignInOutData (recid, employeeid, recorddate) values (2, 2038375602, '2010-03-08 14:25:05')

insert into SignInOutData (recid, employeeid, recorddate) values (3, 2038375602, '2010-03-08 14:29:05')

insert into SignInOutData (recid, employeeid, recorddate) values (4, 2038375602, '2010-03-08 17:30:05')



set identity_insert signinoutdata off



create table #entryexit

(

	RecID int,

	EmployeeID int,

	RecordDate datetime,

	EntryExitType bit --entry = 0 exit = 1

)





;with EntryExit(RecID, EmployeeID, RecordDate, RowNumber) as

(

	select

		recid, employeeid, recorddate, row_number() over(partition by employeeid, day(recorddate) order by recorddate) 

	from

		SignInOutData

)



insert into #entryexit

select

	recid, employeeid, recorddate, (case when (rownumber+1)%2=0 then 0 else 1 end)

from

	EntryExit



select

	recid, 

	employeeid, 

	recorddate entry_time, 

	(

		select

			top 1 recorddate

		from

			#entryexit

		where

			employeeid=a.employeeid and

			day(recorddate)=day(a.recorddate) and

			entryexittype=1 and

			recid>a.recid

		order by

			recid

	) exit_time

into

	#EntryExitTime

from

	#entryexit a

where

	entryexittype=0



update

	#EntryExitTime

set

	Exit_Time=case

				when entry_time>dbo.ConvertToDateTime(entry_time, @shiftendtime) then entry_time

				else dbo.ConvertToDateTime(entry_time, @shiftendtime)

			end

where

	exit_time is null



select

	employeeid, day(entry_time) entry_day, sum(datediff(mi,entry_time, exit_time)) total_working_time_in_minutes

from

	#EntryExitTime

group by

	employeeid, day(entry_time)