finding numbers of days between two date to make a dynamic columns [closed]

Question

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

Answer 1

The basic rule in SQL is that any given constructed query will always return the same columns - in terms of how many there are, their names, and their types.

So you'd be looking at a dynamic SQL approach to construct the query on the fly, if you still want to go down that route. Otherwise, it might be worth looking at whether you can suppress empty columns at a higher level (is this going to some form of report processor - such as SQL reporting services or crystal reports?)

edit 1

You might want to add additional columns to your query such as:

CASE WHEN DATEPART(month,@StartDate) = DATEPART(month,DATEADD(day,29,@StartDate)) THEN 1 ELSE 0 END as ShowColumn29

(And similarly for the other numbers). How you then use that in Reporting services, I@m a bit vague, but I think you can add a hidden textbox somewhere on your report that binds to the ShowColumn29 value, and then set the visibility of the "29" column of the report to the value of this textbox.

Sorry - I'm not that good with reporting services, but hopefully you can play around with this sort of concept and make it work?

Answer 2

For part of you question working out the difference in days you can use DATEDIFF

DATEDIFF(day, @startDate, @endDate)