问我想在一个日期范围上执行多个日期添加，并选择那些单独的日期。

EN

create FUNCTION [DateFuncs].[DateRange]
(
    @DateFrom date,
    @DateTo date
)
RETURNS TABLE
AS
RETURN 
(
    WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
    Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
    Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    SELECT 
        dateadd(d,n-1,@DateFrom) as Date,
        datepart(yyyy,dateadd(d,n-1,@DateFrom)) as YearNum,
        datepart(mm,dateadd(d,n-1,@DateFrom)) as MonthNum,
        datepart(dd,dateadd(d,n-1,@DateFrom)) as DayNum,
        datepart(dw,dateadd(d,n-1,@DateFrom)) as WeekDayNum,
        n-1 as Offset
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
    FROM Nbrs ) 
    D ( n )
    WHERE n <= datediff(d,@DateFrom,@DateTo)+1 
)

--preparation code
declare @data table
(
    effDate date,
    discDate date,
    opMon bit,
    opTue bit,
    opWed bit,
    opThu bit,
    opFri bit,
    opSat bit,
    opSun bit
)

insert into @data (effDate,discDate,opMon,opTue,opWed,opThu,opFri,opSat,opSun)
values  ('2012-10-28','2012-11-27',0,1,0,0,0,0,1)


-- your query   
select
     dr.Date
from
    @data as da
cross apply
    DateFuncs.DateRange(da.effDate,da.discDate) as dr
where
    case 
        when dr.WeekDayNum=1 and da.opSun=1 then 1
        when dr.WeekDayNum=2 and da.opMon=1 then 1
        when dr.WeekDayNum=3 and da.opTue=1 then 1
        when dr.WeekDayNum=4 and da.opThu=1 then 1
        when dr.WeekDayNum=5 and da.opWed=1 then 1
        when dr.WeekDayNum=6 and da.opFri=1 then 1
        when dr.WeekDayNum=7 and da.opSat=1 then 1
    end = 1

select
     dr.Date
from
    @data as da
cross apply
    DateFuncs.DateRange(da.effDate,da.discDate) as dr
where
    (dr.WeekDayNum=1 and da.opSun=1)
or
    (dr.WeekDayNum=2 and da.opMon=1)
or
    (dr.WeekDayNum=3 and da.opTue=1)
or
    (dr.WeekDayNum=4 and da.opThu=1)
or
    (dr.WeekDayNum=5 and da.opWed=1)
or
    (dr.WeekDayNum=6 and da.opFri=1)
or
    (dr.WeekDayNum=7 and da.opSat=1)

;with dates as (
  select id, effDate occur, discDate
    from schedule
      -- table filter here
    union all
  select id, occur+1, discDate
    from dates
   where discDate > occur
)
  select id, occur
    from (select d.occur, s.* 
            from schedule s
            join dates d on d.id=s.id) p
  unpivot (is_occur for DayWeek in (opMon,opTue,opWed,opThu,opFri,opSat,opSun)) up
    where is_occur = 1
      and 'op' + left(datename(dw,occur),3) = DayWeek
 order by id, occur;

create table schedule (
    id int,
    effDate datetime,
    discDate datetime,
    opMon bit,
    opTue bit,
    opWed bit,
    opThu bit,
    opFri bit,
    opSat bit,
    opSun bit);
insert schedule values (1, '2012-10-28', '2012-11-27', 0, 1, 0, 0, 0, 0, 1);
insert schedule values (2, '2012-10-07', '2012-11-30', 0, 0, 1, 1, 0, 0, 1);