SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-10
-- Update date: 2019-05-10
-- Description: 获取指定日期相关周的数据
-- =============================================
 
CREATE FUNCTION [dbo].[tvf_WeekDatas]
(
    @SpecifiedDate DATETIME
)
RETURNS @dumpWeekTable TABLE
(
    [Week] NVARCHAR(MAX) NOT NULL,
    [First Date] DATETIME NULL,
    [Last Date] DATETIME NULL
)
AS
BEGIN    
    INSERT INTO @dumpWeekTable ([Week]) VALUES ('Previous Week'),('Current Week'),('Next Week')
    
    DECLARE @dd INT = DATEDIFF(dd, 0,@SpecifiedDate)
    DECLARE @WEEKDAY INT = DATEPART(WEEKDAY, @SpecifiedDate)
    DECLARE @FD INT = 1 - @WEEKDAY
    DECLARE @LD INT = 0 - @WEEKDAY
 
    DECLARE @FirstDate DATETIME = DATEADD(DAY, @FD, @dd)
    DECLARE @LastDate DATETIME = DATEADD(DAY, @LD, @dd)
    
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, -1,@FirstDate), [Last Date] = DATEADD(wk, 0,@LastDate) WHERE [Week] = 'Previous Week'
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, 0,@FirstDate), [Last Date] = DATEADD(wk, 1,@LastDate) WHERE [Week] = 'Current Week'
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, 1,@FirstDate), [Last Date] = DATEADD(wk, 2,@LastDate) WHERE [Week] = 'Next Week'
 
    RETURN
END