You don't make it easy, but here is a working solution. In the future try to use this type of code in your question so we can focus on the query and solution. I have edited some of your dates to make the example work.
EDIT: New Code
declare @cal table (
calID int not null
, date_ date not null
, isWeekday bit not null
, isHoliday bit not null
, year_ int not null
);
insert into @cal (calID, date_, isWeekday, isHoliday, year_)
select 1, '1-Jan-2010', 1, 1, 2010 union all
select 2, '2-Jan-2010', 0, 0, 2010 union all
select 3, '3-Jan-2010', 0, 0, 2010 union all
select 4, '4-Jan-2010', 1, 0, 2010 union all
select 5, '5-Jan-2010', 1, 0, 2010 union all
select 6, '6-Jan-2010', 1, 0, 2010 union all
select 7, '7-Jan-2010', 1, 0, 2010 union all
select 8, '8-Jan-2010', 1, 0, 2010 union all
select 9, '9-Jan-2010', 0, 0, 2010 union all
select 10, '10-Jan-2010', 0, 0, 2010 union all
select 11, '11-Jan-2010', 1, 0, 2010 union all
select 12, '12-Jan-2010', 1, 0, 2010 union all
select 13, '13-Jan-2010', 1, 0, 2010 union all
select 14, '14-Jan-2010', 1, 0, 2010 union all
select 15, '15-Jan-2010', 1, 0, 2010 union all
select 16, '16-Jan-2010', 0, 0, 2010 union all
select 17, '17-Jan-2010', 0, 0, 2010 union all
select 18, '18-Jan-2010', 1, 1, 2010 union all
select 19, '19-Jan-2010', 1, 0, 2010 union all
select 20, '20-Jan-2010', 1, 0, 2010 union all
select 21, '21-Jan-2010', 1, 0, 2010 union all
select 22, '22-Jan-2010', 1, 0, 2010 union all
select 23, '23-Jan-2010', 0, 0, 2010 union all
select 24, '24-Jan-2010', 0, 0, 2010 union all
select 25, '25-Jan-2010', 1, 0, 2010 union all
select 26, '26-Jan-2010', 1, 0, 2010;
declare @date table(
dateID int identity(1,1) not null
, date2 date null
, date3 date null
, date4 date null
, date5 date null
);
insert into @date (date2, date3, date4, date5)
select '6/20/2009', NULL, NULL, '7/19/2009' union all
select '1/2/2010', NULL, NULL, '1/19/2010' union all
select '1/4/2010', NULL, NULL, '1/15/2010' union all
select '1/2/2010', NULL, NULL, '1/22/2010' union all
select '9/17/2009', NULL, NULL, '10/26/2009' union all
select '6/4/2009', NULL, NULL, '6/24/2009';
;with cte as (
select dateid
, b.date_
from @date
cross apply (
Select Top (DateDiff(DAY,date2,IsNull(date5,date2))+1) DateAdd(DAY, -1+Row_Number() Over (Order By 1/0),date2) date_
from master..spt_values n1
) b
)
select distinct b.dateID
, c.date2
, c.date5
, count(*) over(order by b.dateid) cnt
from @cal a
join cte b
on a.date_ = b.date_
join @date c
on b.dateid = c.dateid
where isWeekday = 1
and isHoliday = 0
you could change out the from master..spt_values n1
for something like this:
;with E00(n) as (select 1 union all select 1)
, E02(n) as (select 1 from E00 a, E00 b)
, E04(n) as (select 1 from E02 a, E02 b)
, E08(n) as (select 1 from E04 a, E04 b)
, E16(n) as (select 1 from E08 a, E08 b)
, E32(n) as (select 1 from E16 a, E16 b)
, cteTally(d) as (select row_number() over (order by n) from E32)
, cte as (
select dateid
, b.date_
from @date
cross apply (
select top (datediff(day,date2,isnull(date5,date2))+1) dateadd(day, -1+row_number() over(order by 1/0),date2) date_
from cteTally
) b
)
select distinct b.dateID
, c.date2
, c.date5
, count(*) over(order by b.dateid) cnt
from @cal a
join cte b
on a.date_ = b.date_
join @date c
on b.dateid = c.dateid
where isWeekday = 1
and isHoliday = 0