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