finding numbers of days between two date to make a dynamic columns
- by Chandradyani
Dear all, I have a select query that currently produces the following results:
DoctorName Team 1 2 3 4 5 6 7 ... 31 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
Using the following query:
DECLARE @startDate = '1/1/2010', @enddate = '1/31/2010'
SELECT d.doctorname,
t.teamname,
MAX(CASE WHEN ca.visitdate = 1 THEN 'x' ELSE NULL END) AS 1,
MAX(CASE WHEN ca.visitdate = 2 THEN 'x' ELSE NULL END) AS 2,
MAX(CASE WHEN ca.visitdate = 3 THEN 'x' ELSE NULL END) AS 3,
...
MAX(CASE WHEN ca.visitdate = 31 THEN 'x' ELSE NULL END) AS 31,
COUNT(*) AS visited
FROM CACTIVITY ca
JOIN DOCTOR d ON d.id = ca.doctorid
JOIN TEAM t ON t.id = ca.teamid
WHERE ca.visitdate BETWEEN @startdate AND @enddate
GROUP BY d.doctorname, t.teamname
the problem is I want to make the column of date are dynamic for example if ca.visitdate BETWEEN '2/1/2012' AND '2/29/2012'
so the result will be :
DoctorName Team 1 2 3 4 5 6 7 ... 29 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
Can somebody help me how to get numbers of days between two date and help me revised the query so it can looping MAX(CASE WHEN ca.visitdate = 1 THEN 'x' ELSE NULL END) AS 1 as many as numbers of days? Please please
