I have a table dbo.MyDates
:
date
--------
'08/28/2012'
'01/10/2013'
'02/05/2013'
and a 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 to do is check if each of the dates in rows 1 -3 are within a specific date range then total the number of dates that are within the range:
id name totalDaysWithinRange
--- ------ --------------------
100 Mike 1
101 John 2
102 Claire 2
103 Mary 0
so that I can use the totalDaysWithinRange
in a simple math calculation. I know how to use while loops from other languages like Java and PHP, but based on my research it seems that in T-SQL it's better to use a recursive CTE. I've used CTEs in the past so I know how they work but I've never used a recursive CTE before. Here's what I came up with:
WITH cte AS
(
SELECT
p.id AS personID,
p.name AS personName,
p.dateRangeStart AS drs,
p.dateRangeEnd AS dre,
d.date AS checkedDate
FROM dbo.MyDates AS d, dbo.People AS p
WHERE d.date BETWEEN p.dateRangeStart AND p.dateRangeEnd
UNION ALL
SELECT
cte.personID,
cte.personName,
cte.drs,
cte.dre,
cte.checkedDAte
FROM cte
INNER JOIN dbo.MyDates AS d
ON d.date = cte.checkedDate
)
SELECT
p.id
p.name,
COUNT(cte.personID)
FROM cte AS c
INNER JOIN dbo.Person AS p ON p.id = c.personID)
;
What I can't figure out is how to calculate the sums so that I can use them in a SELECT from cte
. Thanks for your help.
This should work:
;with cte as
(
select id, count(*) as dayCount
from People p
join MyDates d on d.[Date] between p.dateRangeStart and p.dateRangeEnd
group by id
)
select p.id, p.name, isnull(cte.dayCount,0) as totalDaysWithinRange
from cte
right join People p on p.id = cte.id
order by p.id