sql 通过某段时间求得改段时间内的工作时长，排除工作日
CREATE FUNCTION Fun_GetTotalHourBySomeTime(@TaskId NVARCHAR(30),@Bu_trupstartDate NVARCHAR(50),@Bu_trupEndDate NVARCHAR(50))
RETURNS FLOAT
AS
BEGIN
--计算工作日的天数
DECLARE @tStartDate DATE
DECLARE @tEndDate DATE
SET @tStartDate=CONVERT(NVARCHAR(50),@Bu_trupstartDate,112)
SET @tEndDate=CONVERT(NVARCHAR(50),@Bu_trupEndDate,112)
DECLARE @DAYNum DATE ,
@COUNT INT;
SET @DAYNum = @tStartDate;
SET @COUNT = 0;
WHILE @DAYNum <= @tEndDate
BEGIN
SET @COUNT = @COUNT + ( CASE DATEPART(WEEKDAY, @DAYNum)
WHEN 1 THEN 0
WHEN 7 THEN 0
ELSE 1
END );
SET @DAYNum = DATEADD(DAY, 1, @DAYNum);
END
--工作日的天数先少计算一天;
SET @COUNT=@COUNT-1;
DECLARE @sResultHour FLOAT
DECLARE @Day INT
DECLARE @startTimeH INT
DECLARE @endTimeH INT
DECLARE @startTimeM INT
DECLARE @endTimeM INT
--SELECT @sResultHour=8
SELECT @Day=DATEDIFF(DAY,Bu_trupstartDate,Bu_trupEndDate),@startTimeH=startTimeH,@startTimeM=startTimeM,@endTimeH=endTimeH,@endTimeM=endTimeM
FROM dbo.VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
IF(@Day=0)
BEGIN
IF(@startTimeH<12 AND @endTimeH<=12)
BEGIN
SELECT @sResultHour=CAST(DATEDIFF(MINUTE,Bu_trupstartDate,Bu_trupEndDate)*1./60 AS DECIMAL(18,1)) FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
IF(@startTimeH<12 AND @endTimeH>12)
BEGIN
SELECT @sResultHour=(DATEDIFF(HOUR,Bu_trupstartDate,Bu_trupEndDate))-1 FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
IF(@startTimeH>=12)
BEGIN
SELECT @sResultHour=CAST(DATEDIFF(MINUTE,Bu_trupstartDate,Bu_trupEndDate)*1./60 AS DECIMAL(18,1)) FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
END
IF(@Day>=1)
BEGIN
IF(@startTimeH<12 AND @endTimeH<=12)
BEGIN
SELECT @sResultHour=CAST((@COUNT*7.5*60+(@endTimeH-@startTimeH)*60+(@endTimeM-@startTimeM))*1./60 AS DECIMAL(18,1))
FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
IF(@startTimeH<12 AND @endTimeH>12)
BEGIN
SELECT @sResultHour=CAST((@COUNT*7.5*60+(@endTimeH-@startTimeH-1)*60+(@endTimeM-@startTimeM))*1./60 AS DECIMAL(18,1))
FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
IF(@startTimeH>=12)
BEGIN
SELECT @sResultHour=CAST((@COUNT*7.5*60+(@endTimeH-@startTimeH)*60+(@endTimeM-@startTimeM))*1./60 AS DECIMAL(18,1))
FROM VIEW_Apply_Bu_tripReport WHERE taskId=@TaskId
END
END
RETURN @sResultHour
END
如有更好的解决思路，还请分享学习，谢谢！！！
最新文章
- ActiveMQ集群下的消息回流功能
- Centos7下用命令同步标准时间
- Oracle执行计划
- rm 删除带空格的文件或者目录
- CentOS配置FTP(VSFTPD)
- 我的css reset
- 关于css的优先级
- SQL Server带游标的SQL
- 关于scala环境配置详解
- msmtp+mutt发送邮件报警
- Android OpenGL ES（十三）通用的矩阵变换指令 .
- 在Window下安装Oracle
- QPS的计算方法
- 笔记：Jersey REST API 设计
- Loj #2542. 「PKUWC2018」随机游走
- HDU 1241 - Oil Deposits - [BFS]
- nmon监控数据分析
- 查看Windows系统里的进程已运行的时间
- python--BUG--python socket.error: [Errno 9] Bad file descriptor的解决办法
- Mongodb集群搭建之 Sharding+ Replica Sets集群架构