USE [new_db]
GO
/****** Object:  UserDefinedFunction [dbo].[sfGetBankHolidayDateUser]    Script Date: 2/18/2020 10:11:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[sfGetBankHolidayDateUser]
(
    @ParUserId INT,
    @ParWeeksAgo INT,
    @ParDateStart DATETIME,
    @ParDateEnd DATETIME
    --@ParPeriodName VARCHAR(100)
)
RETURNS DECIMAL(12,2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @BankHolidayDate DATETIME
    DECLARE @StartPeriodDate DATETIME
    DECLARE @EndPeriodDate DATETIME

    --Set @StartPeriodDate =
    --(
    --  SELECT Top 1 PeriodStart
    --  FROM tblPeriods
    --  WHERE PeriodName = @ParPeriodName
    --)
    --Set @EndPeriodDate =
    --(
    --  SELECT Top 1 PeriodEnd
    --  FROM tblPeriods
    --  WHERE PeriodName = @ParPeriodName
    --)

    SET @BankHolidayDate =
    (SELECT TOP 1
                                        bhd.BankHolidayDayDate
                                    FROM
                                        tblBankHolidayDay bhd
                                    WHERE
                                        bhd.BankHolidayDayDate BETWEEN @ParDateStart AND @ParDateEnd
                                        )
    DECLARE @HW DECIMAL(12,2)
    SET @HW =
        (SELECT
            (ISNULL(SUM(dbo.sfMinutesGet(T1.DateTimeStart,T1.DateTimeEnd) - dbo.sfStocktakeUserTimesheetBreaks(T1.StockTakeUserTimesheetId, 0)),0) / CONVERT(DECIMAL(12,2),60))
            -- T.StockTakeUserId, T.StockTakeUserTimesheetId, STU.UserId
            FROM tblStockTakeUserTimesheets AS T1
            INNER JOIN tblStockTakeUsers STU1   ON  T1.StockTakeUserId = STU1.StockTakeUserId
        WHERE STU1.UserId=@ParUserId
        AND T1.DateTimeStart BETWEEN DATEADD(week, @ParWeeksAgo, @BankHolidayDate) AND @BankHolidayDate
        )
        RETURN @HW

END