insert into User_Trip_Days select datediff(day,fromdate,todate) +case when fromtime<='12:00' then 0.5 else 0 end +case when totime>='12:00' then 0.5 else 0 end from User_Trip_Count
--上面少写了一个Emp_ID,改一下:insert into User_Trip_Days select Emp_ID,datediff(day,fromdate,todate) +case when fromtime<='12:00' then 0.5 else 0 end +case when totime>='12:00' then 0.5 else 0 end from User_Trip_Count
--数据测试 select Emp_ID,days=datediff(day,fromdate,todate) +case when fromtime<='12:00' then 0.5 else 0 end +case when totime>='12:00' then 0.5 else 0 end from( select Emp_ID=1,FromDate='20030901',FromTime='13:30',ToDate='20030915',ToTime='11:00' union all select 2,'20030912','10:00','20030918','10:00' union all select 3,'20030913','14:00','20030920','12:00' union all select 4,'20030918','12:00','20030922','11:00' union all select 5,'20030921','12:00','20030930','12:00' ) a/*--测试结果 Emp_ID days ----------- --------------- 1 14.0 2 6.5 3 7.5 4 4.5 5 10.0(所影响的行数为 5 行) --*/
select EMP_ID,Days=(DateDiff(Day,convert(datetime,FromDate),convert(datetime,ToDate))-1)+(case when FromTime<'12:00' then 1 else 0.5 end)+case when ToTime>'12:00' then 1 else 0.5 end) from User_Trip_Count
回来时间在 12:00 之后算 1 天也就是老板的理想状态Emp_ID FromDate FromTime ToDate ToTime
------ -------- -------- -------- ------
6 20030911 11:30 20030915 12:30
7 20030917 10:00 20030919 14:00Emp_ID Days
------ ----
6 5
7 3
select datediff(day,fromdate,todate)
+case when fromtime<='12:00' then 0.5 else 0 end
+case when totime>='12:00' then 0.5 else 0 end
from User_Trip_Count
select Emp_ID,datediff(day,fromdate,todate)
+case when fromtime<='12:00' then 0.5 else 0 end
+case when totime>='12:00' then 0.5 else 0 end
from User_Trip_Count
select Emp_ID,days=datediff(day,fromdate,todate)
+case when fromtime<='12:00' then 0.5 else 0 end
+case when totime>='12:00' then 0.5 else 0 end
from(
select Emp_ID=1,FromDate='20030901',FromTime='13:30',ToDate='20030915',ToTime='11:00'
union all select 2,'20030912','10:00','20030918','10:00'
union all select 3,'20030913','14:00','20030920','12:00'
union all select 4,'20030918','12:00','20030922','11:00'
union all select 5,'20030921','12:00','20030930','12:00'
) a/*--测试结果
Emp_ID days
----------- ---------------
1 14.0
2 6.5
3 7.5
4 4.5
5 10.0(所影响的行数为 5 行)
--*/