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