求一个存储过程 或者SQL语句 日期分解 本人新手 急急急！

qq_31336671 2016-06-10 01:40:00
表中有以下样子的数据
ID Name type lr_frm frm_hrs f_hrs_s lr_to to_hrs to_hrs_s lr_hrs
1 张三 事假 2016-05-18 8 0 2015-05-18 10 0 2.00
2 李四 病假 2016-05-19 8 0 2015-05-20 12 0 12.00
3 王五 事假 2016-05-21 15 30 2015-05-22 12 0 6.50

每天上班的正常时间是 8点到17点。

字段说明： lr_frm开始日期
frm_hrs开始时间(几点)
f_hrs_s 开始时间(几分)
lr_to 结束日期
to_hrs 结束时间(几点)
to_hrs_s 结束时间(几分)

我需要利用存储过程 或者是直接SQL语句 将上面三条数据查询成以下样子。

ID Name type lr_frm Postdate lr_to Enddate lr_hrs
1 张三 事假 2016-05-18 07:00 2015-05-18 09:00 2.00
2 李四 病假 2016-05-19 07:00 2015-05-19 15:00 8.00
2 李四 病假 2016-05-20 07:00 2015-05-20 11:00 4.00
3 王五 事假 2016-05-21 07:00 2016-05-21 08:30 1.50
3 王五 事假 2016-05-22 07:00 2016-05-22 12:00 5.00

Psotdate 要求必须从07:00开始 Enddate值根据 lr_hrs请假时长 加上Postdate的时间得来的

如果遇到日期跨天比如上面的第二条和第三条就需要分解数据，并且请假时长也需要分解。一条显示一天的记录 具体如上方样子。

本人新手刚进公司不久，现客户要求做一个这个样子的报表， 求大神帮帮忙。 万分感谢！
...全文
332 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2016-06-12
  • 打赏
  • 举报
 回复
少改一个位置，用以下去测试 e.g. 


USE Tempdb
go
--> --> 
 
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,9.50 union all
select 5,N'田七',N'婚假','2016-06-30',10,30,'2016-07-01',10,30,8.00
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
								  ELSE
									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')
											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
								  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
								  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
								  ELSE
								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00
2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00
2	李四	病假	2016-05-20	9	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	5.50	09:30	2.50
3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00
4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	9.50	13:30	6.50
4	赵六	病假	2016-05-11	10	07:00	2016-05-11	12	0	9.50	10:00	3.00
5	田七	婚假	2016-06-30	10	07:00	2016-06-30	10	30	8.00	13:30	6.50
5	田七	婚假	2016-07-01	10	07:00	2016-07-01	10	30	8.00	08:30	1.50
*/
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 19 楼 roy_88 的回复:

USE Tempdb
go
--> --> 
 
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,9.50 union all
select 5,N'田七',N'婚假','2016-06-30',10,30,'2016-07-01',10,30,8.00
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
								  ELSE
									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60
											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
								  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
								  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
								  ELSE
								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00
2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00
2	李四	病假	2016-05-20	9	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	5.50	15:00	2.50
3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00
4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	9.50	13:30	6.50
4	赵六	病假	2016-05-11	10	07:00	2016-05-11	12	0	9.50	10:00	3.00
5	田七	婚假	2016-06-30	10	07:00	2016-06-30	10	30	8.00	13:30	6.50
5	田七	婚假	2016-07-01	10	07:00	2016-07-01	10	30	8.00	08:30	1.50
*/
好像还是不对，看您贴的数据 王五第一条数据Enddate是15:00 07:00+2.50 因该是09:30
中国风 2016-06-12
  • 打赏
  • 举报
 回复 

USE Tempdb
go
--> --> 
 
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,9.50 union all
select 5,N'田七',N'婚假','2016-06-30',10,30,'2016-07-01',10,30,8.00
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
								  ELSE
									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60
											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
								  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
								  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
								  ELSE
								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00
2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00
2	李四	病假	2016-05-20	9	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	5.50	15:00	2.50
3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00
4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	9.50	13:30	6.50
4	赵六	病假	2016-05-11	10	07:00	2016-05-11	12	0	9.50	10:00	3.00
5	田七	婚假	2016-06-30	10	07:00	2016-06-30	10	30	8.00	13:30	6.50
5	田七	婚假	2016-07-01	10	07:00	2016-07-01	10	30	8.00	08:30	1.50
*/
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 17 楼 roy_88 的回复:
看#15说明，贴你的执行结果，看方法应该没问题




前面看了一下 #10 好像那个王五的第一条数据 Enddate时间好像也不对。
中国风 2016-06-12
  • 打赏
  • 举报
 回复
看#15说明，贴你的执行结果，看方法应该没问题
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 13 楼 roy_88 的回复:
把你的测试数据贴出来，09：00~18：00 休息时段没变12:00~13:00不计算？
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,9.50 union all
select 5,N'田七',N'婚假','2016-06-30',10,30,'2016-07-01',10,30,8.00
Go
--drop table #T
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
                                      WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
                                      ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
                                  ELSE
                                      CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60
                                            WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
                                            ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
                                  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
                                  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
                                  ELSE
                                  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0 --a
                                        WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
                                        ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09: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,'18:00')+a.[lr_hrs]*60) / 480.0) >= b.number 
        )
这是我测试的数据 已经把您给的代码中08:00和17:00替换成了09:00和18:00 刚发现没有替换之前 那个王五的时间 好像也是不对的。
中国风 2016-06-12
  • 打赏
  • 举报
 回复
看以上测试数据应该是你自己少改数据，[frm_hrs] 改为9就行了，不改的情况下在数字上加1

不改数据的情况下在这里加1
这一段改：
StartTime=CAST(RTRIM([frm_hrs]+1)+':'+RTRIM([f_hrs_s]) AS DATETIME)
中国风 2016-06-12
  • 打赏
  • 举报
 回复
把你的测试数据贴出来，09：00~18：00 休息时段没变12:00~13:00不计算？
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 9 楼 ptvbs 的回复:
结的够快的：
恩 这边已经结贴完了 但是能给我看下您的代码 让我学习一下 可以吗？
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 10 楼 roy_88 的回复:
[quote=引用 7 楼 qq_31336671 的回复:]
[quote=引用 5 楼 roy_88 的回复:]
每天上班的时间是8点到17点

-------------------
这是9小时每天，不是8小时


中午12点到13点不算上班时间 所以每天是8小时[/quote]
上面语句改改：
e.g.
--新增三种情况丙是从10:30开始请假

ID Name type lr_frm frm_hrs f_hrs_s lr_to to_hrs to_hrs_s lr_hrs
1 张三 事假 2016-05-18 8 0 2015-05-18 10 0 2.00
2 李四 病假 2016-05-19 8 0 2015-05-20 12 0 12.00
3 王五 事假 2016-05-21 15 30 2015-05-22 12 0 14.50
4 甲 事假 2016-05-21 8 0 2015-05-22 17 0 8.00
5 乙 事假 2016-05-21 15 30 2015-05-22 12 0 21.50
6 丙 事假 2016-05-21 10 30 2015-05-22 12 0 21.50


USE Tempdb

go

--> --> 

 

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 

		)



/*

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	04:00	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

4	甲	事假	2016-05-21	8	07:00	2016-05-21	17	0	8.00	15:00	8.00

5	乙	事假	2016-05-21	15	07:00	2016-05-21	12	0	21.50	11:00	1.50

5	乙	事假	2016-05-22	15	07:00	2016-05-22	12	0	21.50	15:00	8.00

5	乙	事假	2016-05-23	15	07:00	2016-05-23	12	0	21.50	15:00	8.00

5	乙	事假	2016-05-24	15	07:00	2016-05-24	12	0	21.50	11:00	4.00

6	丙	事假	2016-05-21	10	07:00	2016-05-21	12	0	21.50	12:30	5.50

6	丙	事假	2016-05-22	10	07:00	2016-05-22	12	0	21.50	15:00	8.00

6	丙	事假	2016-05-23	10	07:00	2016-05-23	12	0	21.50	15:00	8.00

*/
[/quote]

这边刚知道 他们那边上班的时间 是 9点到18点 我把您写的代码中的08:00 和17:00 替换成 09:00 和18:00
在王五这个Enddate时间出现了错误 这个时间变成了18:00 因该是09:30 我是新手 我看您的代码 有点看不懂 所以不知道在哪里改 能帮忙改一下吗



qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 23 楼 roy_88 的回复:

USE Tempdb
go
--> --> 
 
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,2.50 union all
select 5,N'田七',N'婚假','2016-06-30',17,0,'2016-07-01',10,30,1.00
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]*60
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
								  ELSE
									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')
											WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN  a.[lr_hrs]*60
											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
										  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]
										  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
								  ELSE
								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00
2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00
2	李四	病假	2016-05-20	9	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	5.50	09:30	2.50
3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00
4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	2.50	09:30	2.50
5	田七	婚假	2016-06-30	17	07:00	2016-06-30	10	30	1.00	08:00	1.00
*/
大哥 真的很感谢你 我刚进入公司不久 好多东西都不懂 我能加你QQ吗？ 以后有不懂的向你请教？
中国风 2016-06-12
  • 打赏
  • 举报
 回复 

USE Tempdb
go
--> --> 
 
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',9,0,'2016-05-18',18,0,8.00 union all
select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all
select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all
select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,2.50 union all
select 5,N'田七',N'婚假','2016-06-30',17,0,'2016-07-01',10,30,1.00
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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]*60
									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480
									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 
								  ELSE
									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')
											WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN  a.[lr_hrs]*60
											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480
											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0
										  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]
										  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 
								  ELSE
								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) = b.number AND b.number=1 THEN a.[lr_hrs]
										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8
										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00
2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00
2	李四	病假	2016-05-20	9	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	5.50	09:30	2.50
3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00
4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	2.50	09:30	2.50
5	田七	婚假	2016-06-30	17	07:00	2016-06-30	10	30	1.00	08:00	1.00
*/
qq_31336671 2016-06-12
  • 打赏
  • 举报
 回复
引用 21 楼 roy_88 的回复:
少改一个位置，用以下去测试
e.g.




USE Tempdb

go

--> --> 

 

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',9,0,'2016-05-18',18,0,8.00 union all

select 2,N'李四',N'病假','2016-05-19',9,0,'2016-05-20',12,0,12.00 union all

select 3,N'王五',N'事假','2016-05-21',15,30,'2016-05-22',12,0,5.50 union all

select 4,N'赵六',N'病假','2016-05-10',10,30,'2016-05-11',12,0,9.50 union all

select 5,N'田七',N'婚假','2016-06-30',10,30,'2016-07-01',10,30,8.00

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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN 480-DATEDIFF(n,'09:00',StartTime)

									  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 480

									  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60)-1)%480+1 END 

								  ELSE

									  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN  DATEDIFF(n,StartTime,'18:00')

											WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number    THEN 480

											ELSE ((480-DATEDIFF(n,StartTime,'18: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,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN (480-DATEDIFF(n,'09:00',StartTime))/60.0

								  WHEN CEILING((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8

								  ELSE ((DATEDIFF(n,'09:00',StartTime)+a.[lr_hrs]*60-1)%480+1)/60.0 END 

								  ELSE

								  CASE WHEN  CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number AND b.number=1 THEN DATEDIFF(n,StartTime,'18:00')/60.0

										WHEN CEILING((480-DATEDIFF(n,StartTime,'18:00')+a.[lr_hrs]*60) / 480.0) > b.number  THEN 8

										ELSE ((480-DATEDIFF(n,StartTime,'18: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]+1)+':'+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,'09: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,'18:00')+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	9	07:00	2016-05-18	18	0	8.00	15:00	8.00

2	李四	病假	2016-05-19	9	07:00	2016-05-19	12	0	12.00	15:00	8.00

2	李四	病假	2016-05-20	9	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	5.50	09:30	2.50

3	王五	事假	2016-05-22	15	07:00	2016-05-22	12	0	5.50	10:00	3.00

4	赵六	病假	2016-05-10	10	07:00	2016-05-10	12	0	9.50	13:30	6.50

4	赵六	病假	2016-05-11	10	07:00	2016-05-11	12	0	9.50	10:00	3.00

5	田七	婚假	2016-06-30	10	07:00	2016-06-30	10	30	8.00	13:30	6.50

5	田七	婚假	2016-07-01	10	07:00	2016-07-01	10	30	8.00	08:30	1.50

*/

这边刚测试了几条数据 发现只要有之前出现过的日期 lr_hrs2值就会出现错误 比如下面这两条，lr_hrs分别是2.50,1.00 但是查询出来的结果 显示的是4.00和8.00

中国风 2016-06-11
  • 打赏
  • 举报
 回复
引用 7 楼 qq_31336671 的回复:
[quote=引用 5 楼 roy_88 的回复:] 每天上班的时间是8点到17点 ------------------- 这是9小时每天，不是8小时
中午12点到13点不算上班时间 所以每天是8小时[/quote] 上面语句改改： e.g. --新增三种情况丙是从10:30开始请假 ID Name type lr_frm frm_hrs f_hrs_s lr_to to_hrs to_hrs_s lr_hrs 1 张三 事假 2016-05-18 8 0 2015-05-18 10 0 2.00 2 李四 病假 2016-05-19 8 0 2015-05-20 12 0 12.00 3 王五 事假 2016-05-21 15 30 2015-05-22 12 0 14.50 4 甲 事假 2016-05-21 8 0 2015-05-22 17 0 8.00 5 乙 事假 2016-05-21 15 30 2015-05-22 12 0 21.50 6 丙 事假 2016-05-21 10 30 2015-05-22 12 0 21.50 
USE Tempdb
go
--> --> 
 
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 
		)

/*
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	04:00	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
4	甲	事假	2016-05-21	8	07:00	2016-05-21	17	0	8.00	15:00	8.00
5	乙	事假	2016-05-21	15	07:00	2016-05-21	12	0	21.50	11:00	1.50
5	乙	事假	2016-05-22	15	07:00	2016-05-22	12	0	21.50	15:00	8.00
5	乙	事假	2016-05-23	15	07:00	2016-05-23	12	0	21.50	15:00	8.00
5	乙	事假	2016-05-24	15	07:00	2016-05-24	12	0	21.50	11:00	4.00
6	丙	事假	2016-05-21	10	07:00	2016-05-21	12	0	21.50	12:30	5.50
6	丙	事假	2016-05-22	10	07:00	2016-05-22	12	0	21.50	15:00	8.00
6	丙	事假	2016-05-23	10	07:00	2016-05-23	12	0	21.50	15:00	8.00
*/
qq_31336671 2016-06-11
  • 打赏
  • 举报
 回复
引用 2 楼 wmxcn2000 的回复:
每天所有的请假，都要从 7:00 开始？ 并且每天的时间，以实际为准吗？ PS：都在过节，没几个来这里了；
查询出来的结果 必须都要从7:00开始 结束时间是请假时长加上 07:00得来的。
qq_31336671 2016-06-11
  • 打赏
  • 举报
 回复
引用 1 楼 ap0405140 的回复:
请问请假时长(lr_hrs)的分解原则是什么? 如李四的12.00为什么分解为8.00和4.00, 王五的6.50为什么分解为1.50和5.00.
lr_hrs分解是根据表中的frm_hrs字段时间来的。 每天上班的时间是8点到17点，李四分解出来的时间一个8小时一个4小时 是因为李四是从早上8点请假到第二天的12点所以分解出来的第一天是8小时 第二天是4小时 而王五分解的1.50小时和5小时 是因为王五请假时间是从下午15点30分请假 到第二天的12点 所以15点30到17点 是1个半小时 所以分解出来的是 1.50和5小时
ptvbs 2016-06-11
  • 打赏
  • 举报
 回复
结的够快的：
qq_31336671 2016-06-11
  • 打赏
  • 举报
 回复
引用 6 楼 roy_88 的回复:
把王五小时数改为14.50，如以下效果 e.g. 
use Tempdb
go
--> --> 
 
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
*/
3Q 完美的解决了我的问题 非常感谢！
qq_31336671 2016-06-11
  • 打赏
  • 举报
 回复
引用 5 楼 roy_88 的回复:
每天上班的时间是8点到17点 ------------------- 这是9小时每天，不是8小时
中午12点到13点不算上班时间 所以每天是8小时
中国风 2016-06-11
  • 打赏
  • 举报
 回复
把王五小时数改为14.50，如以下效果

e.g.
use Tempdb

go

--> --> 

 

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

*/
加载更多回复（3）
MS-SQL Server

34,576

社区成员

254,586

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章