一段时间段内的星期几到星期几的集合

最新推荐文章于 2024-02-01 10:53:56 发布

原创最新推荐文章于 2024-02-01 10:53:56 发布 · 566 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #insert #join

本文介绍了一个使用T-SQL创建存储过程的例子，该过程用于查询指定日期范围内的工作日最大日期。通过创建临时表并利用`DATEADD`和`DATEPART`函数，此存储过程能够灵活地筛选出特定周几的最大日期。

CREATE PROC
    p @start_dt DATETIME,
    @end_dt DATETIME,
    @weekdays VARCHAR(20)
AS
    DECLARE @sSQL NVARCHAR(4000);
    DECLARE @columns NVARCHAR(1000)
    SELECT @sSQL='',@columns='';

    CREATE TABLE #weekday_table(ID TINYINT IDENTITY,[weekday] INT);
    INSERT #weekday_table([weekday])
        SELECT
            SUBSTRING(@weekdays,number,
                        CHARINDEX(',',@weekdays+',',number)-number)
        FROM master.dbo.spt_values AS A
        WHERE A.type='p'
            AND number BETWEEN 1 AND LEN(@weekdays)
            AND SUBSTRING(','+@weekdays,number,1)=',';

    SELECT
        @columns=@columns+N',MAX(CASE WHEN [weekday]='+
                          RTRIM([weekday])+N' THEN dt END) AS [周'+
                            CASE [weekday]
                                WHEN 0 THEN N'日'
                                WHEN 1 THEN N'一'
                                WHEN 2 THEN N'二'
                                WHEN 3 THEN N'三'
                                WHEN 4 THEN N'四'
                                WHEN 5 THEN N'五'
                                WHEN 6 THEN N'六' END +N']'
    FROM #weekday_table;

    SET @columns=STUFF(@columns,1,1,'');

    SET @sSQL=N'
        SELECT '+@columns+N'
        FROM (
            SELECT
               DATEADD(day,number,@start_dt) AS dt,
               (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],
               DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]
            FROM master.dbo.spt_values AS A
                JOIN #weekday_table AS B
                    ON (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7=B.[weekday]
            WHERE A.type=''p''
            AND DATEADD(day,number,@start_dt)<=@end_dt
        ) AS A
        GROUP BY [week]
    ';

    EXEC sp_executesql @sSQL,
             N'@start_dt DATETIME,@end_dt DATETIME',
             @start_dt,@end_dt;

GO

EXEC p '2009-05-01','2009-05-31','0,1,2,3,4,5,6'

GO
DROP PROC p