求算法:一段时间段内的星期几到星期几的集合

来源：互联网发布：淘宝手机助手登录失败编辑：程序博客网时间：2024/05/16 10:51

假设时间段为2009-04-01到2009-04-30.
我想得到这段时间内,每逢周一至周二的日期,要返回的结果集可以为DataTable(两列-周二日期和周三日期),或是二维数组(周二日期,周三日期)

另外:因为4月1号是星期三,所以周一至周二只有如下数据:
周一周二
2009-04-06 2009-04-07
2009-04-13 2009-04-14
2009-04-20 2009-04-21
2009-04-27 2009-04-28

即,超过起始日期的星期几不算在内.

--2000
DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt='2009-04-01',@end_dt='2009-04-30';

SELECT
    MAX(CASE WHEN [weekday]=1 THEN dt END) AS 周一,
    MAX(CASE WHEN [weekday]=2 THEN dt END) AS 周二
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
   WHERE A.type='p'
       AND DATEADD(day,number,@start_dt)<=@end_dt
       AND (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 IN(1,2)
) AS A
GROUP BY [week]

/*
周一                      周二
----------------------- -----------------------
2009-04-06 00:00:00.000 2009-04-07 00:00:00.000
2009-04-13 00:00:00.000 2009-04-14 00:00:00.000
2009-04-20 00:00:00.000 2009-04-21 00:00:00.000
2009-04-27 00:00:00.000 2009-04-28 00:00:00.000

(4 行受影响)

*/

declare @bt datetime,@et datetime
select @bt='2009-04-01',@et='2009-04-30'
select a.dt 周一,b.dt 周二
from
   (
     select dt=dateadd(dd,number,@bt) from master..spt_values where type='p' and number<=datediff(dd,@bt,@et) and datepart(w,dateadd(dd,number,@bt)+@@datefirst-1)=1
   ) a,
   (
     select dt=dateadd(dd,number,@bt) from master..spt_values where type='p' and number<=datediff(dd,@bt,@et) and datepart(w,dateadd(dd,number,@bt)+@@datefirst-1)=2
   ) b
where a.dt+1=b.dt

/*
周一                      周二
----------------------- -----------------------
2009-04-06 00:00:00.000 2009-04-07 00:00:00.000
2009-04-13 00:00:00.000 2009-04-14 00:00:00.000
2009-04-20 00:00:00.000 2009-04-21 00:00:00.000
2009-04-27 00:00:00.000 2009-04-28 00:00:00.000

(4 行受影响)
*/

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-04-01','2009-04-30','0,1,2,3,4,5,6'

GO
DROP PROC p

/*
周日                      周一                      周二                      周三                      周四                      周五                      周六
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
NULL                    NULL                    NULL                    2009-04-01 00:00:00.000 2009-04-02 00:00:00.000 2009-04-03 00:00:00.000 2009-04-04 00:00:00.000
2009-04-05 00:00:00.000 2009-04-06 00:00:00.000 2009-04-07 00:00:00.000 2009-04-08 00:00:00.000 2009-04-09 00:00:00.000 2009-04-10 00:00:00.000 2009-04-11 00:00:00.000
2009-04-12 00:00:00.000 2009-04-13 00:00:00.000 2009-04-14 00:00:00.000 2009-04-15 00:00:00.000 2009-04-16 00:00:00.000 2009-04-17 00:00:00.000 2009-04-18 00:00:00.000
2009-04-19 00:00:00.000 2009-04-20 00:00:00.000 2009-04-21 00:00:00.000 2009-04-22 00:00:00.000 2009-04-23 00:00:00.000 2009-04-24 00:00:00.000 2009-04-25 00:00:00.000
2009-04-26 00:00:00.000 2009-04-27 00:00:00.000 2009-04-28 00:00:00.000 2009-04-29 00:00:00.000 2009-04-30 00:00:00.000 NULL                    NULL

(5 行受影响)
*/