SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[svf_Week]
(
    @StartYear INT,
    @EndYear INT
)
RETURNS @Week TABLE([ID] INT IDENTITY(1,1) PRIMARY KEY,[Year] [INT] NULL,[Week] [INT] NULL,[StartDate] [DATETIME] NULL,[EndDate] [DATETIME] NULL)
AS
BEGIN
    DECLARE @StartDateOfYear DATETIME 
    DECLARE @LastDateOfYear DATETIME 
    DECLARE @WeekStartDate DATETIME 
    DECLARE @WeekEndDate DATETIME 
    DECLARE @Weeks INT 
 
    WHILE @StartYear <= @EndYear
    BEGIN
        SET @StartDateOfYear = CAST((CAST(@StartYear AS VARCHAR(4)) + '-01-01') AS DATETIME)
        SET @LastDateOfYear= CAST((CAST(@StartYear AS VARCHAR(4))+ '-12-31') AS DATETIME)
        SET @Weeks = 1         
        
        DECLARE @WeekStartDateOfYear DATETIME
        IF DATEPART(DW,@StartDateOfYear) > 4
            SET @WeekStartDateOfYear = DATEADD(DAY,(8 - DATEPART(DW,@StartDateOfYear)) ,@StartDateOfYear)
        ELSE 
            SET @WeekStartDateOfYear = DATEADD(DAY,(-(DATEPART(DW,@StartDateOfYear)-1)),@StartDateOfYear)
        SET @WeekStartDate = @WeekStartDateOfYear
        SET @WeekEndDate = DATEADD(DAY,6,@WeekStartDate)
        WHILE DATEDIFF(DAY,@WeekStartDate,@LastDateOfYear) >= 4
        BEGIN
            INSERT INTO @Week([Year],[Week],[StartDate],[EndDate]) VALUES (@StartYear,@Weeks,@WeekStartDate,@WeekEndDate)
            SET @Weeks = @Weeks + 1
            SET @WeekStartDate = @WeekStartDate + 7
            SET @WeekEndDate = @WeekEndDate + 7
        END
        SET @StartYear = @StartYear + 1
    END  
        RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-12
-- Update date: 2019-05-12
-- Description: 获取母亲节或父亲节日期
-- =============================================
CREATE FUNCTION [dbo].[svf_Parents_Festival]
(
    @StartYear INT,
    @EndYear INT
)
RETURNS @tempTable TABLE([ID] INT IDENTITY(1,1) PRIMARY KEY,[Year] [INT] NOT NULL,[Mother's Day] [DATETIME] NULL,[Father's Day] [DATETIME] NULL)
AS
BEGIN       
    DECLARE @Weeks AS TABLE([Year] INT,[StartDateOfWeek] DATETIME)
    INSERT INTO @Weeks ([Year],[StartDateOfWeek]) SELECT [Year],[StartDate] FROM [dbo].[svf_Week] (@StartYear,@EndYear)
    
    WHILE @StartYear <= @EndYear
    BEGIN
        INSERT INTO @tempTable ([Year]) VALUES(@StartYear)      
        UPDATE @tempTable SET [Mother's Day] = (
            SELECT [StartDateOfWeek] FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY [StartDateOfWeek] ASC) AS [RowNumber], [StartDateOfWeek] FROM @Weeks 
                WHERE [Year] = @StartYear AND MONTH([StartDateOfWeek]) = 5) AS m 
            WHERE [RowNumber] = 2)
        WHERE [Year] = @StartYear
 
        UPDATE @tempTable SET [Father's Day] = (
            SELECT [StartDateOfWeek] FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY [StartDateOfWeek] ASC) AS [RowNumber], [StartDateOfWeek] FROM @Weeks 
                WHERE [Year] = @StartYear AND MONTH([StartDateOfWeek]) = 6) AS f 
            WHERE [RowNumber] = 3)
        WHERE [Year] = @StartYear
 
        SET @StartYear = @StartYear + 1
    END  
 
    RETURN
END