SQL passes a certain period of time to find the time period of work, excluding working days

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.