Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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
share|improve this answer
    
Of course for better performance, instead of the Dates generator you you could have a pre-filled dates table full of indexes and use that instead, but for this to work you should have a min and max date that can be handled (I have used this approach many times and it gives a HUGE performance boost) –  George Mavritsakis Oct 26 '12 at 16:54
    
Works like a dream. Thanks for your help. –  Davenport Oct 26 '12 at 19:27
add comment

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);
share|improve this answer
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.