22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--开始结束同一天且不是周末
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) AND DATEPART(weekday, @begintime)NOT IN (7, 1)
THEN
CASE
--判断工作时间
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 AND DATENAME(hour, @endtime)>18 then 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 AND DATENAME(hour, @endtime)<=18 THEN DATEDIFF(hour,@begintime,@endtime)
WHEN DATENAME(hour, @begintime)<9 AND DATENAME(hour, @endtime)>18 THEN 9
WHEN DATENAME(hour, @begintime)<9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime)-9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
--开始结束同一天且是周末
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) AND DATEPART(weekday, @begintime) IN (7, 1) THEN 0
--开始/结束日期为空
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
--开始结束非同一天
ELSE
CASE
--开始时间为周末
WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
ELSE
--开始时间为非周末
CASE
--判断开始时间当天小时数
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)<9 THEN 9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
END
+
CASE
--结束时间为周末
WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
ELSE
--结束时间为非周末
CASE
--判断结束时间当天小时数
when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
WHEN DATENAME(hour, @endtime)<9 THEN 0
WHEN DATENAME(hour, @endtime)>18 THEN 9
END
END
END
+ b.cnt*9 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
ELSE
CASE
--判断开始时间
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)<9 THEN 9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
END
+
CASE
WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
ELSE
CASE
--判断结束时间
when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
WHEN DATENAME(hour, @endtime)<9 THEN 0
WHEN DATENAME(hour, @endtime)>18 THEN 9
END
END
END
+ b.cnt*9 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
INSERT INTO @tab
SELECT @cnt
RETURN
END
上面那个代码有个5和6还没改过来,这个代码应该改掉了[/quote]
大神,太谢谢你了,大部分能跑出来了,就是还有一个小问题,就是shenhetime如果不在工作时间内,‘2017-01-06 00:04:36.057’与‘2017-01-06 09:15:50.197’应该是小于1小时,但计出来9小时了
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
ELSE
CASE
--判断开始时间
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)<9 THEN 9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
END
+
CASE
WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
ELSE
CASE
--判断结束时间
when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
WHEN DATENAME(hour, @endtime)<9 THEN 0
WHEN DATENAME(hour, @endtime)>18 THEN 9
END
END
END
+ b.cnt*9 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
INSERT INTO @tab
SELECT @cnt
RETURN
END
上面那个代码有个5和6还没改过来,这个代码应该改掉了
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
ELSE
CASE
--判断开始时间
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)<9 THEN 9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
END
+
CASE
WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
ELSE
CASE
--判断结束时间
when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
WHEN DATENAME(hour, @endtime)<9 THEN 0
WHEN DATENAME(hour, @endtime)>18 THEN 9
END
END
END
+ b.cnt*9 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
INSERT INTO @tab
SELECT @cnt
RETURN
END
之前搞错了,礼拜六和礼拜天应该是对应7和1,我以为是5和6,所以出来有问题,你再用上面的代码试试。应该没问题了,不过你最好多测试几个不同的时间段看看。
有什么问题再说
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
ELSE
CASE
--判断开始时间
when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
WHEN DATENAME(hour, @begintime)<9 THEN 9
WHEN DATENAME(hour, @begintime)>18 THEN 0
END
END
+
CASE
WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
ELSE
CASE
--判断结束时间
when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
WHEN DATENAME(hour, @endtime)<9 THEN 0
WHEN DATENAME(hour, @endtime)>18 THEN 9
END
END
END
+ b.cnt*9 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
INSERT INTO @tab
SELECT @cnt
RETURN
END
一天按9个小时计算的,9点到18点之间的都算是工作时间,其他时间为非工作时间
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
ELSE 18 -DATENAME(hour, @begintime)
END
+ CASE
WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
ELSE DATENAME(hour, @endtime) -9
END
end
+ b.cnt*8 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
RETURN
END
[/quote]
修改完函数,但不知为什么没数据出来
ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
SELECT CASE
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime)
--新增一个判断开始结束日期是否有一个为空,如果是则间隔时间为0
WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
ELSE 18 -DATENAME(hour, @begintime)
END
+ CASE
WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
ELSE DATENAME(hour, @endtime) -9
END
end
+ b.cnt*8 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
--新增判断是否开始结束日期为空的
IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
BEGIN
INSERT INTO @tab
SELECT @cnt
END
ELSE
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
RETURN
END
Create FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
insert into @tab
--新增一个判断开始结束日期是否同一天,如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
SELECT CASE CAST(@begintime AS date) WHEN cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) ELSE
CASE
WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
ELSE 18 -DATENAME(hour, @begintime)
END
+ CASE
WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
ELSE DATENAME(hour, @endtime) -9
END
end
+ b.cnt*8 as hours
FROM dbo.getCnt(@begintime,@endtime) b
RETURN
END
把你的gethours改成这样再试试
--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
shenhetime DATETIME,
shoulitime DATETIME
)
INSERT INTO #tab
SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL
SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL
SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'
--测试数据结束
--创建函数获取两个日期间的间隔天数(除去周六日)
alter FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS
BEGIN
DECLARE @cnt INT
SET @cnt=0
SET @begintime=DATEADD(DAY,1,@begintime)
WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
BEGIN
IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
BEGIN
SET @cnt=@cnt
END
ELSE
BEGIN
SET @cnt=@cnt+1
END
SET @begintime=DATEADD(DAY,1,@begintime)
END
INSERT INTO @tab
SELECT @cnt
RETURN
END
--调用函数计算(前三个小时数相加就是你要的数据了)
SELECT a.*,
CASE
WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0
ELSE 18 -DATENAME(hour, shenhetime)
END AS [审核当天小时数当天周末的话就为0)],
CASE
WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0
ELSE DATENAME(hour, shoulitime) -9
END AS [受理当天总小时数(当天周末的话就为0)],
b.cnt*8 AS 间隔小时数, --一天八小时计算
b.cnt AS 间隔天数
FROM #tab A
OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b