tsql - From a table of made up of rows of dates, how to count the number of rows where the date is in a certain date range -

i have table dbo.mydates:

date -------- '08/28/2012' '01/10/2013' '02/05/2013' 

and table dbo.people:

id     name      daterangestart    daterangeend ---    ------    --------------    ------------ 100    mike      '08/01/2012'      '11/15/2012' 101    john      '08/01/2012'      '02/01/2013' 102    claire    '12/01/2012       '03/15/2013' 103    mary      '03/01/2013'      '05/01/2013' 

what i'm trying check if each of dates in rows 1 -3 within specific date range total number of dates within range:

id     name      totaldayswithinrange ---    ------    -------------------- 100    mike      1 101    john      2 102    claire    2 103    mary      0 

so can use totaldayswithinrange in simple math calculation. know how use while loops other languages java , php, based on research seems in t-sql it's better use recursive cte. i've used ctes in past know how work i've never used recursive cte before. here's came with:

with cte  (   select     p.id personid,     p.name personname,     p.daterangestart drs,     p.daterangeend dre,     d.date checkeddate    dbo.mydates d, dbo.people p   d.date between p.daterangestart , p.daterangeend   union   select     cte.personid,     cte.personname,     cte.drs,     cte.dre,     cte.checkeddate     cte       inner join dbo.mydates d       on d.date = cte.checkeddate    )  select   p.id   p.name,   count(cte.personid)   cte c     inner join dbo.person p on p.id = c.personid) ; 

what can't figure out how calculate sums can use them in select cte. help.

this should work:

;with cte  (   select id, count(*) daycount   people p   join mydates d on d.[date] between p.daterangestart , p.daterangeend   group id ) select p.id, p.name, isnull(cte.daycount,0) totaldayswithinrange cte  right join people p on p.id = cte.id order p.id