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

Question

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.

Answer 1

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

Answer 2

Or even better without cases:

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)

Answer 3

Here is another option using Recursive CTE to expand out the dates from the table, and UNPIVOT to turn the bit columns into rows and finally DateName to compare against the days of week on which it occurs.

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

Tested using sample structure and data:

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