复杂的考勤时间算法，高手进来看看！(gz,up没分)

[codingfans]

DataSet:
TBill_UseVechile

Fields：
FID Int, 自动增长的ID
FName Varchar(6), 姓名
FDate DateTime , 日期
FStartTime DateTime, 本日开始时间
FEndTime DateTime, 本日结束时间
FIsHoliday Int, 置1为节假日，0为平时

要求统计一段时间内所有人的正常班时，加班工时，和节假日工时
其中正常班时范围可以从其它表中得到，假设为 8:30 - 17:30

请问能否用SQL一次完成？因为表很大，还有其它关联表，请不要选用循环。

[codingfans]

最好再加上一个总工时。

[johnsonrao]

的确是个有趣的问题，我的想法是将时间完全转成浮点数进行计算。

计算公式如下：

总工时 = Sum(FEndTime-FStartTime);
节假日 = Sum(FEndTime-FStartTime) Where FIsHoliday=1

8:30 作为Float = 17/48
17:30 作为Float = 35/48

正常时间取值就有时间段跨度问题，要好好想想......

[johnsonrao]

真的很麻烦，要动手建个表试试，不过这种题才100分是不是少了点？

其中还有不少问题，象开始时间和结束时间之间是否会跨天？如果是就更麻烦了！

QQ：171083285

[codingfans]

to johnsonrao(johnson) ,如果能解决，分数不成问题。

开始时间和结束时间我在输入时就有控制，绝对不会跨天，

并且FEndTime > FStartTime

我不用QQ。

[codingfans]

试过了，

总工时 = Sum(FEndTime-FStartTime);
节假日 = Sum(FEndTime-FStartTime) Where FIsHoliday=1

可以正确算出结果，得到的是小时数，先谢过了！

不过

8:30 作为Float = 17/48
17:30 作为Float = 35/48

还不明白有什么作用，能不能解释清楚一点？

[hammer_shi]

呵呵，up!看看你写出的sql语句是什么样的。





/*1、MY TOOL:*************************/
/*2、Delphi,Oracle,Sybase,C++/C*******/
/*3、PROJECT:*************************/
/*3、Boss*****************************/
/*4、为了五星的目标希望你早点结贴*********/
/*5、在线技术讨论(QQ)：68123095*********/

[johnsonrao]

这么早就开始了？

你如果知道前面两句可以得到小时数，为什么会想不通后两句？都是同一原理
只不过将 FStartTime 和 FEndTime 的日期部分缺省为0而已。

我昨天为你这问题想了一晚，今天总算解决了。

下面是完整的SQL语句,其中把 FStartTime 和 FEndTime 设置为0输入后，还需要调整+2，不知道为什么，可能是数据库的问题。

应该还可以用 Case 语句进行优化，

Select t1.Name, isnull(ds.SumAll,0) as FTotalTime, isnull(d0.Sum0,0) as FHolidayTime,
(isnull(d1.Sum1,0)+isnull(d2.Sum2,0)+isnull(d3.Sum3,0)+isnull(d4.Sum4,0)) as FNormalTime,
isnull(ds.SumAll,0)-isnull(d0.Sum0,0)-(isnull(d1.Sum1,0)+isnull(d2.Sum2,0)+isnull(d3.Sum3,0)+isnull(d4.Sum4,0)) as FAddTime

From table1 t1,

(Select name, (Sum(convert(float,FEndTime)-convert(float,FStartTime))*24) as SumAll
From table1
Group by name
) ds,

(Select name, (Sum(convert(float,FEndTime)-convert(float,FStartTime))*24) as Sum0
From table1
Where FIsHoliday=1
Group by name
) d0,

(Select name, Count(*)*9 as Sum1
From table1
Where FIsHoliday=0
and ((convert(float,FStartTime+2)<17/48.0)
and (convert(float,FEndTime+2)>35/48.0))
Group by name
) d1,

(Select name, Sum(35/48.0-convert(float,FStartTime+2))*24 as Sum2
From table1
Where FIsHoliday=0
and ((convert(float,FStartTime+2)>=17/48.0)
and (convert(float,FStartTime+2)<=35/48.0)
and (convert(float,FEndTime+2)>35/48.0))
Group by name
) d2,

(Select name, Sum(convert(float,FEndTime+2)-17/48.0)*24 as Sum3
From table1
Where FIsHoliday=0
and ((convert(float,FStartTime+2)<17/48.0)
and (convert(float,FEndTime+2)>=17/48.0)
and (convert(float,FEndTime+2)<=35/48.0))
Group by name
) d3,

(Select name, Sum(convert(float,FEndTime)-convert(float,FStartTime))*24 as Sum4
From table1
Where FIsHoliday=0
and ((convert(float,FStartTime+2)>=17/48.0)
and (convert(float,FEndTime+2)<=35/48.0))
Group by name
) d4
Where T1.name *= ds.name
and T1.name *= d0.name
and T1.name *= d1.name
and T1.name *= d2.name
and T1.name *= d3.name
and T1.name *= d4.name
Order by T1.name

连空值都帮你一并处理了，记得要多给点分！

[lj_csdn]

select 姓名，节假日标志，count(*),sum(end-start) from xxx where date>=开始日期 and date<=结束日期 group by 姓名，节假日标志

可以得到 姓名，节假日，节假日天数，总工时
姓名，正常日，正常日天数，总工时

select 姓名，sum(start-8:30) from xxx where date>=开始日期 and date<=结束日期 and start>8:30 and 节假日=0 group by 姓名

可以得到 姓名，正常日迟到时间

select 姓名，sum(17:30-end) from xxx where date>=开始日期 and date<=结束日期 and end<17:30 and 节假日=0 group by 姓名

可以得到 姓名，正常日早退时间

根据正常日天数、迟到时间、早退时间，正常日总工时 再算

如果支持临时表，可以用临时表处理

[codingfans]

to johnsonrao(johnson)
不好意思，有答案了？！
前面的问题是我想乱了，还要 * 24。

不过我要试试，如果行一定给分！

to lj_csdn(大笨蛋) : 感谢你的回答，但你的答案太简单了，好好想想再回答吧。

[codingfans]

结果是有了，不过数目很大，我用临时表再减去天数的时数是正确的。
原因想是我数据表的时间型都包括了当天的日期，不过这个我自己能做，
日期范围我也可以加参数进去处理。

算是解决了！给分！

[codingfans]

为什么我不能给超过100分？

[johnsonrao]

可以开个新贴子呀！

你的专家分要超过1000分才能给到200分，多答点问题吧，其实你也很好啊，很快就会到的。

[lj_csdn]

to codingfans(Jaklly)
大笨蛋1、2分钟内也只能想这么多了,哈哈