使用SQL查询，如何选择范围内的每个日期？

问题描述：

我正在查询支持票证数据库，每个票证都有一个列，分别表示打开日期和关闭日期。票证经常保持开放状态数天，因此我们需要能够拉出每天开放的票证数量。

例如，对于4/8/14，我们需要知道多少张票证门票在4/8开盘，加上未开票的总数，该总数在4/8之前开盘，但在4/8的12:00 am仍保持开盘状态（在4/8或之后关闭或未关闭））。

I'm querying a support ticket database, and each ticket has a column for "date opened" and "date closed." Tickets frequently remain open for multiple days, so we need to be able to pull the number of tickets that are OPEN on each day.
For example, for 4/8/14, we need to know how many tickets were opened on 4/8, combined with the total number of unclosed tickets that were opened prior to 4/8 but remained still open at 12:00am on 4/8 (may or may not have been closed during or after 4/8).

这对于单个日期来说似乎很简单，但是现在我需要编写一个查询，以提取完整日期范围。

例如，我们需要编写一个查询，该查询返回14年1月1日至14年10月4日之间的每个日期，以及每个日期打开的票证总数（包括打开0张票证的日期）。

This seems straightforward enough for a single date, but now I need to write a query that will pull a complete range of dates.
For example, we need to write a query that returns every date between 1/1/14 and 4/10/14 along with the total number of tickets open on each date (including dates on which 0 tickets were open).

是否可以仅使用查询或子查询，而不使用任何存储过程或临时数据表？

我们当前正在将数据拉入Excel并计算日期统计信息，但Excel并非可扩展的解决方案并且我们希望SQL执行此工作，以便我们可以将此报告迁移到将来的SSRS（SQL Server Reporting Services）。

Is this possible using only queries or subqueries, without using any stored procedures or temporary datatables?
We're currently pulling the data into Excel and calculating the date stats there, but Excel is not a scalable solution and we'd like to have SQL perform this work so we can migrate this report to SSRS (SQL Server Reporting Services) down the road.

答

您的问题对我来说不是很清楚，但是使用SQLServer 2005或更高版本（对于日期类型为SQLServer 2008或更高版本），可以创建日历。这是一种方法

Your question is not very clear to me, but with SQLServer 2005 or better (SQLServer 2008 or better for the type Date) you can create a calendar. This one the way to do it

WITH [counter](N) AS
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1)
, days(N) AS (SELECT row_number() over (ORDER BY (SELECT NULL)) FROM [counter])
, months (N) AS (SELECT N - 1 FROM days WHERE N < 13)
SELECT DISTINCT CAST(DATEADD(DAY, days.n, 
                             DATEADD(MONTH, months.n, '20131231')
                            ) AS date)
FROM months
     CROSS JOIN days
ORDER BY 1

如果您需要更多年，只需相应地添加新的CTE

if you need more year just add a new cte accordingly

SQLFiddle

使用SQL查询，如何选择范围内的每个日期？

相关推荐