--Business hours	Monday – Wednesday	8:30	19:30
--After hours	Monday – Wednesday	19:31	8:29
--Business hours	Thursday - Friday	8:30	21:00
--After hours	Thursday - Friday	21:01	8:59
--Business hours	Saturday - Sunday	9:00	19:00
--After hours	Saturday - Sunday	19:01	8:29

set datefirst 1

declare @intervals table(ID int, Start time, Finish time, WeekDayStart tinyint, WeekDayFinish tinyint, IsWorking bit)


-- business hours
insert into @intervals values(1, '8:30', '8:59:59', 1, 5, 1)
insert into @intervals values(2, '9:00', '19:00:59', 1, 7, 1)
insert into @intervals values(5, '19:01', '19:30:59', 1, 5, 1)
insert into @intervals values(6, '19:30', '21:00:59', 4, 5, 1)

-- outside business hours
insert into @intervals values(9, '19:01', '23:59:59', 6, 7, 0)
insert into @intervals values(9, '19:31', '23:59:59', 1, 3, 0)
insert into @intervals values(9, '21:01', '23:59:59', 4, 5, 0)
insert into @intervals values(9, '00:00', '08:29:59', 1, 5, 0)
insert into @intervals values(9, '00:00', '08:59:59', 6, 7, 0);

select datediff(n, '09/15/2013 12:35:44', '09/22/2013 15:45:22')

declare @d1 datetime = '20130101 13:45:00'
      , @d2 datetime = '20130512 11:05:00'
      
declare @firstday datetime, @lastday datetime,
      , @weekcount int
      
      
set @weekcount = datediff(day, cast(@d1 as dateadd)+1, d2)/7;

set @firstday = @d1;
set @lastday = @d2;
set @d1 = dateadd(day, @weekcount*7, cast(@d1 as dateadd)+1);
set @d2 = cast(@d2 as dateadd)-1;

-- итого надо обработать
--1. @firstday
--2. @weekcount
--3. интервал @d1 .. @d2, состоящий из НЕ более 6 ПОЛНЫХ дней. 
--4. @lastday

set dateformat ymd;
set datefirst 1;
set nocount on;

-- рабочие часы. Зачем хранить ещё и нерабочие, не очень понятно.
declare @intervals table(
      id int, Start time, Finish time
    , WeekDayStart tinyint, WeekDayFinish tinyint
    , IsWorking bit);
insert into @intervals values(1, '8:30', '8:59:59', 1, 5, 1);
insert into @intervals values(2, '9:00', '19:00:59', 1, 7, 1);
insert into @intervals values(3, '19:01', '19:30:59', 1, 5, 1);
insert into @intervals values(4, '19:30', '21:00:59', 4, 5, 1);

-- время с/по
declare @startingDT datetime = '2013-09-01 15:30:00.000'
      , @endingDT datetime = '2013-09-10 11:00:00.000'
      ;
-- даты с/по (для удобства)
declare @startingDate datetime = dateadd (dd, datediff (dd, 0, @startingDT), 0)
      , @endingDate datetime = dateadd (dd, datediff (dd, 0, @endingDT), 0)
      ;

-- таблица с достаточно длинной последовательностью чисел (1, 2, 3, ..., N)
declare @num table (n int);
insert into @num select row_number() over (order by t.id) from sysobjects t;

-- раскрываем рабочие часы по каждому дню недели
declare @workHours table (weekdayNum int, startingTime time, endingTime time);
insert into @workHours 
    select n.n as WeekDayNum, i.Start, i.Finish
    from @intervals i
    join @num n on (n.n between i.WeekDayStart and i.WeekDayFinish)
    ;

-- собственно, результат
select tt.dt, tt.WeekdayNum, tt.st, tt.et, tt.startingTime, tt.endingTime
from (
    select nn.dt, nn.WeekdayNum, wh.startingTime as st, wh.endingTime as et
          -- приводим в порядок границы интервала слева
        , case when nn.dt + wh.startingTime < @startingDT 
            then @startingDT 
            else nn.dt + wh.startingTime 
          end as startingTime
          -- ... и справа
        , case when nn.dt + wh.endingTime > @endingDT 
            then @endingDT 
            else nn.dt + wh.endingTime 
          end as endingTime
    from (
        -- каждый день из интервала @startingDT .. @endingDT
        select dateadd (dd, n.n - 1, @startingDate) as dt
         , datepart (weekday, dateadd (dd, n.n - 1, @startingDate)) as WeekdayNum
        from @num n
        where n.n <= datediff (dd, @startingDate, @endingDate) + 1
        ) nn
    join @workHours wh on (nn.weekdayNum = wh.WeekdayNum)
    ) tt
-- на границах может получиться что попало, выкидываем это безобразие
where tt.startingTime < endingTime 
order by dt

set datefirst 1

declare @intervals table(WeekDay tinyint, Start datetime, Finish datetime)

-- business hours
insert into @intervals values(1, '8:30', '19:30')
insert into @intervals values(2, '8:30', '19:30')
insert into @intervals values(3, '8:30', '19:30')
insert into @intervals values(4, '9:00', '21:00')
insert into @intervals values(5, '9:00', '21:00')
insert into @intervals values(6, '9:00', '19:00')
insert into @intervals values(7, '9:00', '19:00')

declare @dt1 datetime, @dt2 datetime
set @dt1='20130715 12:35:44'
set @dt2='20130922 15:45:22'

;with cte1 as (
select 
	 CONVERT(datetime,CONVERT(varchar(8), @dt1, 112)) as dt1_d
	,CONVERT(datetime,CONVERT(varchar(8), @dt1, 108)) as dt1_t
	,CONVERT(datetime,CONVERT(varchar(8), @dt2, 112)) as dt2_d
	,CONVERT(datetime,CONVERT(varchar(8), @dt2, 108)) as dt2_t
),
cte2 as (
select
	 t.dt1_d, i.WeekDay
	,case when i.Start>t.dt1_t then i.Start else t.dt1_t end as Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,t.dt1_d)=i.WeekDay
where t.dt1_t<i.Finish
),
cte3 as (
select
	 t.dt2_d, i.WeekDay
	,case when i.Start>t.dt2_t then i.Start else t.dt2_t end as Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,t.dt2_d)=i.WeekDay
where t.dt2_t<i.Finish
),
cte4 as (
select 
	 dateadd(day,1,t.dt1_d) as dt
	,datepart(weekday,dateadd(day,1,t.dt1_d)) as WeekDay
	,i.Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,dateadd(day,1,t.dt1_d))=i.WeekDay
where dateadd(day,1,t.dt1_d)<t.dt2_d

union all

select 
	 dateadd(day,1,t.dt) as dt
	,datepart(weekday,dateadd(day,1,t.dt)) as WeekDay
	,i.Start
	,i.Finish
from cte4 t inner join @intervals i on datepart(weekday,dateadd(day,1,t.dt))=i.WeekDay cross join cte1 tt
where dateadd(day,1,t.dt)<tt.dt2_d
)

--select sum(datediff(minute,Start,Finish)) as total
--from
--(
select * from cte2
union all
select * from cte4 
union all
select * from cte3
--) a