USE [new_db]
GO
/****** Object:  UserDefinedFunction [dbo].[sfGetHouseWorkedPriorBankHolidayDate]    Script Date: 2/18/2020 10:11:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[sfGetHouseWorkedPriorBankHolidayDate]
(
    @ParUserId INT,
    @ParWeeksAgo INT,
    @ParDateStart DATETIME,
    @ParDateEnd DATETIME
    --@ParPeriodName VARCHAR(100)
)
RETURNS DECIMAL(12,2)
AS
BEGIN
        RETURN
        (SELECT
            SUM(HoursWorked)
        FROM
            (SELECT
                (SELECT
                    (ISNULL(SUM(dbo.sfMinutesGet(T1.DateTimeStart,T1.DateTimeEnd) - dbo.sfStocktakeUserTimesheetBreaks(T1.StockTakeUserTimesheetId, 0)),0) / CONVERT(DECIMAL(12,2),60))
                FROM tblStockTakeUserTimesheets AS T1
                    INNER JOIN tblStockTakeUsers STU1   ON  T1.StockTakeUserId = STU1.StockTakeUserId
                WHERE
                    STU1.UserId=@ParUserId
                    AND T1.DateTimeStart BETWEEN DATEADD(week, -13,  DATEADD(DAY,-1,bhd.BankHolidayDayDate)) AND bhd.BankHolidayDayDate) AS 'HoursWorked'
            --into #TempTable
            FROM
                tblBankHolidayDay bhd
            WHERE
                bhd.BankHolidayDayDate BETWEEN @ParDateStart AND @ParDateEnd
                AND (SELECT
                        (ISNULL(SUM(dbo.sfMinutesGet(T1.DateTimeStart,T1.DateTimeEnd) - dbo.sfStocktakeUserTimesheetBreaks(T1.StockTakeUserTimesheetId, 0)),0) / CONVERT(DECIMAL(12,2),60))
                    FROM tblStockTakeUserTimesheets AS T1
                        INNER JOIN tblStockTakeUsers STU1   ON  T1.StockTakeUserId = STU1.StockTakeUserId
                    WHERE
                        STU1.UserId=@ParUserId
                        AND T1.DateTimeStart BETWEEN DATEADD(week, -5,  DATEADD(DAY,-1,bhd.BankHolidayDayDate)) AND bhd.BankHolidayDayDate) >= CONVERT(DECIMAL(12,2),40)) TT)

END