一些比较难的sql问题：2(二)
between '14:00' and '18:00'
and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)
else 0
end +
case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
or
(convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)
else 0
end
/*
注意下面的计算逻辑是，如果这天不是假日，同时与开始日期相同
那么就要计算时间间隔，如果时间是在上午的工作时间范围内，
那么用当前日期的12点，减去开始日期，就是时间间隔，但还必须要加上下午的工作时间，
也就是4个小时，转化为秒数，就是4*3600
*/
when h_date IS null and
convert(varchar(10),t.start_time_temp,120) = c.r
then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600
else 0
end +
case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'
then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')
else 0
end
when h_date IS null and
CONVERT(varchar(10),t.end_time_temp,120) = c.r
then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'
then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp)
else 0
end +
case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600
else 0
end
when h_date is null and
convert(varchar(10),t.start_time_temp,120) < c.r and
CONVERT(varchar(10),t.end_time_temp,120) > c.r
then 7.5 * 3600
when h_date IS null
then 0
end as seconds
FROM
(
/*
这里之所以要转换，是由于有些时间比如 start_time为2013-09-18 08:21:32.037,
不在正常工作时间（上午8:30--12:00 下午14:00--18:00）内，
所以要先转化为正常工作时间，否则后面的case when的逻辑判断就太复杂了。
*/
SELECT start_time,
end_time,
case when CONVERT(varchar(5),start_time,114) < '08:30'
then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)
when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'
then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)
else start_time
end as start_time_temp,
case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'
then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)
when CONVERT(varchar(5),end_time,114) > '18:00'