I want to perform multiple date adds on a date range and select those individual dates

I have a table of schedules with an effective date, discontinue date, and seven boolean fields indicating days of the week when that schedule is in use. Here's a sample:

effDate    discDate   opMon opTue opWed opThu opFri opSat opSun
---------- ---------- ----- ----- ----- ----- ----- ----- -----
2012-10-28 2012-11-27 0     1     0     0     0     0     1

What I need to do is select, for each date range, a table of the individual dates where each record is a date in that range. For the above data set, it would look like this:

dates
----------
2012-10-28
2012-10-30
2012-11-04
2012-11-06
2012-11-11
2012-11-13
2012-11-18
2012-11-20
2012-11-25
2012-11-27

I can't seem to wrap my brain around this one for some reason.

Having a days generator function like the following:

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 
)

check the following query

--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

StackExchange Database Administrators Q#27682, answer score: 5