blocks|key|590596|text|您需要一个Calendar表，也可以生成一个带有CTE的日历表。一旦你有了它，剩下的查询应该是相当琐碎的。由于递归问题，CTE方法可能有点复杂，并且不允许使用聚合，因此下面我使用了一个表变量。您还可以将其设置为保存在数据库中的永久表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|590597|SET+NOCOUNT+ON

DECLARE+@Calendar+TABLE+(my_date+DATETIME+NOT+NULL)
DECLARE+@date+DATETIME,+@max_date+DATETIME

SELECT+@date+=+MIN(StartDate),+@max_date+=+MAX(EndDate)+FROM+My_Table

WHILE+(@date+<=+@max_date)
BEGIN
++++INSERT+INTO+@Calendar+(my_date)+VALUES+(@date)
++++SELECT+@date+=+DATEADD(dy,+1,+@date)
END

SELECT
++++C.myDate,
++++M.RequestType,
++++M.Area,
++++COUNT(*)+AS+myCount
FROM
++++@Calendar+C
INNER+JOIN+My_Table+M+ON
++++M.StartDate+<=+C.myDate+AND
++++M.EndDate+>=+C.myDate
GROUP+BY
++++C.myDate,
++++M.RequestType,
++++M.Area
ORDER+BY
++++C.myDate,
++++M.RequestType,
++++M.Area|code-block|syntax|javascript|590598|根据您的潜在日期范围有多大，填充表变量可能需要一段时间。例如，如果范围跨越了十年或二十年。|590599|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You'll need either a Calendar table or you can generate one with a CTE. Once you have that, the rest of the query should be fairly trivial. The CTE approach can be a little complex due to recursion issues and not being allowed to use aggregates, so below I've used a table variable. You can also make this a permanent table that you keep in your database.

<pre><code>SET NOCOUNT ON

DECLARE @Calendar TABLE (my_date DATETIME NOT NULL)
DECLARE @date DATETIME, @max_date DATETIME

SELECT @date = MIN(StartDate), @max_date = MAX(EndDate) FROM My_Table

WHILE (@date &lt;= @max_date)
BEGIN
 INSERT INTO @Calendar (my_date) VALUES (@date)
 SELECT @date = DATEADD(dy, 1, @date)
END

SELECT
 C.myDate,
 M.RequestType,
 M.Area,
 COUNT(*) AS myCount
FROM
 @Calendar C
INNER JOIN My_Table M ON
 M.StartDate &lt;= C.myDate AND
 M.EndDate &gt;= C.myDate
GROUP BY
 C.myDate,
 M.RequestType,
 M.Area
ORDER BY
 C.myDate,
 M.RequestType,
 M.Area
</code></pre>

Depending on how large your potential date range is, filling the table variable could take awhile. For example, if the range spanned a decade or two.

blocks|key|590638|text|听起来你可能想要一个'Calendar'+file。特别是作为大型商业组织的一部分，这将变得非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|590639|在生成日历后，您可以使用以下内容获取表：|590640|SELECT+a.isoDate,+b.RequestType,+b.Area,+count(*)
FROM+calendar+as+a
JOIN+mySPTable+as+b
ON+a.isoDate+between+b.StartDate+and+b.EndDate
WHERE+a.isoDate+>=+[input_start_date]+
++++++AND+a.isoDate+<+[input_end_date]
GROUP+BY+a.isoDate,+b.RequestType,+b.Area|code-block|syntax|javascript|590641|这将为日历文件中至少一行mySPTable的开始日期和结束日期之间的每个日期生成一行。|590642|顺便说一下，也可以使用递归CTE生成日期范围，但从长远来看，我建议生成并使用日历文件。|590643|快速CTE：|590644|WITH+DateRange+(thisDate)+as+(SELECT+[input_start_date]
++++++++++++++++++++++++++++++UNION+ALL
++++++++++++++++++++++++++++++SELECT+DATEADD(dy,+1,+thisDate)
++++++++++++++++++++++++++++++FROM+DateRange
++++++++++++++++++++++++++++++WHERE+thisDate+<+[input_end_date])|590645|entityMap|0|LINK|mutability|MUTABLE|url|http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html^0|A|F|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|C|$]]|$1|Q|3|R|5|H|7|19|8|@]|9|@]|C|$I|J]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

Sounds like you may want a <a href="http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html" rel="nofollow">'Calendar' file</a>. Especially as part of a larger business organization, this will become extremely useful.

After generating the calendar, you can get your table with the following: 

<pre><code>SELECT a.isoDate, b.RequestType, b.Area, count(*)
FROM calendar as a
JOIN mySPTable as b
ON a.isoDate between b.StartDate and b.EndDate
WHERE a.isoDate &gt;= [input_start_date] 
 AND a.isoDate &lt; [input_end_date]
GROUP BY a.isoDate, b.RequestType, b.Area
</code></pre>

This will generate a row for every date in the calendar file that is between the start and end dates for at least one row of mySPTable.

As a side note, it's also possible to generate the range of dates with a recursive CTE, but especially over the long run, I'd recommend generating and using the calendar file. 
Quick CTE:

<pre><code>WITH DateRange (thisDate) as (SELECT [input_start_date]
 UNION ALL
 SELECT DATEADD(dy, 1, thisDate)
 FROM DateRange
 WHERE thisDate &lt; [input_end_date])
</code></pre>

blocks|key|590689|text|您可以使用数字表(从0开始)来完成此操作。这里我使用的是master..spt_values。SQL,+Auxiliary+table+of+numbers|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|590690|select+dateadd(day,+N.Number,+M.StartDate)+as+myDate,
+++++++RequestType,
+++++++Area,+
+++++++count(*)+as+myCount
from+mySPtable+as+M
++inner+join+master..spt_values+as+N
++++on+N.Number+<=+datediff(day,+M.StartDate,+M.EndDate)
where+N.type+=+'P'
group+by+dateadd(day,+N.Number,+M.StartDate),
+++++++++RequestType,
+++++++++Area
order+by+dateadd(day,+N.Number,+M.StartDate)|code-block|syntax|javascript|590691|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers^0|1B|V|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can do this with a number table (starting from 0). Here I use master..spt_values instead. <a href="https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers">SQL, Auxiliary table of numbers</a>

<pre><code>select dateadd(day, N.Number, M.StartDate) as myDate,
 RequestType,
 Area, 
 count(*) as myCount
from mySPtable as M
 inner join master..spt_values as N
 on N.Number &lt;= datediff(day, M.StartDate, M.EndDate)
where N.type = 'P'
group by dateadd(day, N.Number, M.StartDate),
 RequestType,
 Area
order by dateadd(day, N.Number, M.StartDate)
</code></pre>

First off, thanks to anyone who helps me solve this problem. I am using SQL 2005, but can use 2008 if no solution is available in 05. 

I have a rows of data that look like such:

<pre><code>select * from mySPtable

| myPK | Area | RequestType | StartDate | EndDate |
 1 SB ADD 8/14/2011 8/18/2011
 2 NB RMV 8/16/2011 8/16/2011
</code></pre>

So what I want to do is count up the total requests for each area by day. Results should be:

<pre><code>| myDate | RequestType | Area | myCount |
 8/14/2011 ADD SB 1
 8/15/2011 ADD SB 1
 8/16/2011 ADD SB 1
 8/16/2011 RMV NB 1
 8/17/2011 ADD SB 1
 8/18/2011 ADD SB 1
</code></pre>

How do I go about doing this? I'm stumped and no amount of googling has helped.

Interesting SQL Join on dates between dates

数据库

问有趣的SQL连接，连接日期之间的日期
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有趣的SQL连接，连接日期之间的日期EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有趣的SQL连接，连接日期之间的日期
EN