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

Question

假设时间段为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 即,超过起始日期的星期几不算在内. 使用C#或是Sql给的算法都行.... 
在线急等..先谢了! 

Answer 1

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt='2009-04-01',@end_dt='2009-04-30';SELECT 
    [1] AS 周一,
    [2] 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
    PIVOT(MAX(dt) FOR [weekday] IN([1],[2])) AS pvt
/*
周一                      周二
----------------------- -----------------------
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 行受影响)
*/

Answer 2

--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 行受影响)*/

Answer 3

感谢liangCK先.周一,周二我想要传参数进去的,这里只是举个例子说是周一到周二,也有可能其它的.

Answer 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 [周'+RTRIM([weekday])+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;
             
GOEXEC p '2009-04-01','2009-04-30','1,2'GO
DROP PROC p/*
周1                      周2
----------------------- -----------------------
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 行受影响)
*/

Answer 5

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;
             
GOEXEC 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 行受影响)
*/

Answer 6

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 行受影响)
*/