查找两个日期之间的天数，以制作动态列 [英] finding numbers of days between two date to make a dynamic columns

问题描述

亲爱的，我有一个选择查询，目前产生以下结果：

DoctorName团队1 2 3 4 5 6 7 ... 31已访问

dr。作为  A                            x   x ...     2次

dr。 Sc  A                         x         ...     1次

dr。 Gh  B                                 x ...     1次

dr。钕C                                    ... x   1次

使用以下查询：

  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访问
 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 
  

问题是我想使日期列是动态的，例如，如果ca.visitdate BETWEEN'2/1/2012'AND'2/29/2012'
，那么结果将是：

DoctorName团队1 2 3 4 5 6 7 ... 29已访问

dr。作为  A                            x   x ...     2次

dr。 Sc  A                         x         ...     1次

dr。 Gh  B                                 x ...     1次

dr。钕C                                    ... x   1次

有人可以帮助我如何获得两个日期和帮助之间的天数我修改了查询，所以它可以循环MAX（CASE WHEN ca.visitdate = 1 THEN'x'ELSE NULL END）AS 1多达几天？请请

解决方案

SQL中的基本规则是任何给定的构造查询将始终返回相同的列 - 在多少有，他们的名字和他们的类型。

所以你会看动态的SQL方法来构建查询，如果你还想去沿那条路线否则，可能值得一提的是，您是否可以在较高级别抑制空列（这将是某种形式的报表处理器 - 例如SQL报表服务或Crystal报表？）

编辑1

您可能需要在查询中添加其他列，例如：

CASE WHATE DATEPART（month，@ StartDate）= DATEPART（month，DATEADD（day，29，@ StartDate））THEN 1 ELSE 0 END as ShowColumn29

（和其他数字相似）。那么如何在报表服务中使用这个功能，我@ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @％这个文本框的值。

对不起 - 我对报告服务不是很好，但希望你能玩这种概念，使其工作吗？ / p>

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

解决方案

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?

这篇关于查找两个日期之间的天数，以制作动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！