sql server两个时间段内,求出周末的量
公司有个表记录了出差(加班)的初始时间和截止时间,现在要计算出加班时间,之前的设计并没有考虑到这部分,因此本人通过sql重新计算周末数
表formmain
starttime
endtime
使用游标方式抓每行的数据进行分析,具体程序如下:
CREATE PROCEDURE [dbo].[shish] AS declare @starttime datetime declare @endtime datetime declare @id decimal(20,0) declare mycursor cursor for select id,field0007,field0008 from formmain_0896; open mycursor fetch next from mycursor into @id,@starttime,@endtime while(@@fetch_status = 0) begin declare @week varchar(20) declare @num int set @num = 0 while @starttime <= @endtime begin set @week = DATENAME(weekday, @starttime); if (@week = '星期六' or @week = '星期日') begin set @NUM=@NUM+1 end set @starttime = @starttime + 1; end INSERT INTO tbs_sl (id,starttime,endtime,sl) select @id,@starttime,@endtime,@num fetch next from mycursor into @id,@starttime,@endtime end close mycursor deallocate mycursor GO