CREATE FUNCTION fun_gettotalhourbysometime (@TaskId NVARCHAR (), @Bu_trupstartDate NVARCHAR, @Bu_trupEndDate NVARCHAR (50))
RETURNS FLOAT
As
BEGIN
--Calculates the number of days in a workday
DECLARE @tStartDate DATE
DECLARE @tEndDate DATE
SET @tStartDate =convert (NVARCHAR (), @Bu_trupstartDate, 112)
SET @tEndDate =convert (NVARCHAR (), @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
--The days of the workday are counted less than one day;
SET @[email protected];
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 [email protected]
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 [email protected]
END
IF (@startTimeH <12 and @endTimeH >12)
BEGIN
SELECT @sResultHour = (DATEDIFF (hour,bu_trupstartdate,bu_trupenddate))-1 from View_apply_bu_tripreport WHERE [email Protected
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 [email protected]
END
END
IF (@Day >=1)
BEGIN
IF (@startTimeH <12 and @endTimeH <=12)
BEGIN
SELECT @sResultHour =cast ((@COUNT *7.5*60+ (@[email protected]) *60+ (@[email protected]) *1./60 as DECIMAL (18,1))
From View_apply_bu_tripreport WHERE [email protected]
END
IF (@startTimeH <12 and @endTimeH >12)
BEGIN
SELECT @sResultHour =cast ((@COUNT *7.5*60+ (@end [email protected]) *60+ (@[email protected]) *1./60 as DECIMAL (18,1))
From View_apply_bu_tripreport WHERE [email protected]
END
IF (@startTimeH >=12)
BEGIN
SELECT @sResultHour =cast ((@COUNT *7.5*60+ (@[email protected]) *60+ (@[email protected]) *1./60 as DECIMAL (18,1))
From View_apply_bu_tripreport WHERE [email protected]
END
END
RETURN @sResultHour
END
If there is a better solution, please also share the study, thank you!!!
SQL passes a certain period of time to find the time period of work, excluding working days