if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(22),[type] nvarchar(22),[lr_frm] Date,[frm_hrs] int,[f_hrs_s] int,[lr_to] Date,[to_hrs] int,[to_hrs_s] int,[lr_hrs] decimal(18,2))
Insert #T
select 1,N'张三',N'事假','2016-05-18',8,0,'2015-05-18',10,0,2.00 union all
select 2,N'李四',N'病假','2016-05-19',8,0,'2015-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2015-05-22',12,0,14.50
Go
SELECT a.ID ,
a.Name ,
a.type ,
CAST(CAST(a.lr_frm AS DATETIME) + b.number - 1 AS DATE) AS lr_frm ,
a.frm_hrs ,
'07:00' AS [Postdate] ,
CAST(CAST(a.lr_frm AS DATETIME)+ b.number - 1 AS DATE) AS lr_to ,
a.to_hrs ,
a.to_hrs_s ,
a.lr_hrs,
[Enddate] = CONVERT(VARCHAR(5), DATEADD(n,
CASE WHEN a.[frm_hrs]=8 AND a.[f_hrs_s]=0 THEN
CASE WHEN a.[lr_hrs]*60 / 480.0 >= b.number
THEN 480
ELSE a.[lr_hrs]*60 % 480
END
ELSE
CASE WHEN ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) / 480.0 >= b.number AND b.number=1
THEN DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME))
WHEN ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) / 480.0 >= b.number
THEN 480
ELSE ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) % 480
END
END ,
CAST(a.lr_frm AS DATETIME) +'07:00'
+ b.number - 1), 8) ,
[lr_hrs2] = CAST(CASE WHEN a.[frm_hrs]=8 AND a.[f_hrs_s]=0 THEN CASE WHEN [lr_hrs] / 8.0 >= b.number THEN 8
ELSE [lr_hrs] % 8.0
END
ELSE
CASE WHEN ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) / 480.0 >= b.number AND b.number=1
THEN DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME))/60.0
WHEN ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) / 480.0 >= b.number
THEN 8
ELSE ((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) % 480.0/60.0
END
END
AS DECIMAL(18,2))
FROM #T AS a ,
master.dbo.spt_values AS b
WHERE b.type = 'P'
AND b.number >= 1
AND(
CEILING(a.[lr_hrs] / 8.0) >= b.number AND a.[frm_hrs]=8 AND a.[f_hrs_s]=0
OR
(a.[frm_hrs]!=8 OR a.[f_hrs_s]!=0 ) AND CEILING(((480-DATEDIFF(n,RTRIM(a.[frm_hrs])+':'+RTRIM(a.[f_hrs_s]),CAST('17:00' AS DATETIME)))+a.[lr_hrs]*60) / 480.0) >= b.number
)
/*
ID Name type lr_frm frm_hrs Postdate lr_to to_hrs to_hrs_s lr_hrs Enddate lr_hrs2
1 张三 事假 2016-05-18 8 07:00 2016-05-18 10 0 2.00 09:00 2.00
2 李四 病假 2016-05-19 8 07:00 2016-05-19 12 0 12.00 15:00 8.00
2 李四 病假 2016-05-20 8 07:00 2016-05-20 12 0 12.00 11:00 4.00
3 王五 事假 2016-05-21 15 07:00 2016-05-21 12 0 14.50 08:30 1.50
3 王五 事假 2016-05-22 15 07:00 2016-05-22 12 0 14.50 15:00 8.00
3 王五 事假 2016-05-23 15 07:00 2016-05-23 12 0 14.50 12:00 5.00
*/
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(22),[type] nvarchar(22),[lr_frm] Date,[frm_hrs] int,[f_hrs_s] int,[lr_to] Date,[to_hrs] int,[to_hrs_s] int,[lr_hrs] decimal(18,2))
Insert #T
select 1,N'张三',N'事假','2016-05-18',8,0,'2015-05-18',10,0,2.00 union all
select 2,N'李四',N'病假','2016-05-19',8,0,'2015-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2015-05-22',12,0,14.50 UNION ALL
select 4,N'甲',N'事假','2016-05-21',8,0,'2015-05-22',17,0,8.00 UNION ALL
select 5,N'乙',N'事假','2016-05-21',15,30,'2015-05-22',12,0,21.50 UNION ALL
select 6,N'丙',N'事假','2016-05-21',10,30,'2015-05-22',12,0,21.50
GO
SELECT a.ID ,
a.Name ,
a.type ,
CAST(CAST(a.lr_frm AS DATETIME) + b.number - 1 AS DATE) AS lr_frm ,
a.frm_hrs ,
'07:00' AS [Postdate] ,
CAST(CAST(a.lr_frm AS DATETIME)+ b.number - 1 AS DATE) AS lr_to ,
a.to_hrs ,
a.to_hrs_s ,
a.lr_hrs,
[Enddate]=CONVERT(VARCHAR(5),
DATEADD(n,
CASE WHEN a.StartTime<='12:00'
THEN CASE WHEN CEILING((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'08:00',StartTime)
WHEN CEILING((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number THEN 480
ELSE ((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END
ELSE
CASE WHEN CEILING((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60
WHEN CEILING((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60) / 480.0) > b.number THEN 480
ELSE ((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60)-1)%480+1 END
END ,CAST(a.lr_frm AS DATETIME) +'07:00'+ b.number - 1), 8),
[lr_hrs2]=CAST(CASE WHEN a.StartTime<='12:00'
THEN CASE WHEN CEILING((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'08:00',StartTime))/60.0
WHEN CEILING((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number THEN 8
ELSE ((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END
ELSE
CASE WHEN CEILING((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'17:00')/60.0
WHEN CEILING((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60) / 480.0) > b.number THEN 8
ELSE ((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60-1)%480+1)/60.0 END
END AS DECIMAL(18,2))
FROM (SELECT *,StartTime=CAST(RTRIM([frm_hrs])+':'+RTRIM([f_hrs_s]) AS DATETIME),EndTime=CAST(RTRIM([to_hrs])+':'+RTRIM([to_hrs_s]) AS DATETIME) FROM #T ) AS a
,master.dbo.spt_values AS b
WHERE b.type = 'P'
AND b.number >= 1
AND(
CEILING((DATEDIFF(n,'08:00',StartTime)+a.[lr_hrs]*60) / 480.0) >= b.number AND a.StartTime<='12:00'
OR
a.StartTime>='12:00' AND CEILING((480-DATEDIFF(n,StartTime,'17:00')+a.[lr_hrs]*60) / 480.0) >= b.number
)
第一种方法：查询2018年1月到2018年10月的数据 select * from 表名 where times between “2018-01-01” and “2018-10-31”; 第二种方法：查询2018年1月到2018年10月的数据（使用 “%”） select * from 表名 where...
--这里演示的是杀掉正在执行的指定sql declare num number := 1; sql_tem Varchar2(100); begin while num <= 5 loop for v_rlt in ( SELECT b. SID, b.serial# FROM v$process A, v$session b, v$sqlarea c...
---------------------------------------------------------------建表 ----------------判断student表是否存在 declare num number; --声明 参数num 类型number begin --开始 select count(1) into num from ...
