首先,感谢所有帮助我解决这个问题的人。我使用的是SQL 2005,但如果05中没有解决方案,我可以使用2008。
我有一行数据,看起来像这样:
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
所以我想要做的是每天计算每个区域的总请求数。结果应该是:
| 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
我该怎么做呢?我被难住了,再多的谷歌搜索也无济于事。
发布于 2011-08-19 02:30:31
您需要一个Calendar表,也可以生成一个带有CTE的日历表。一旦你有了它,剩下的查询应该是相当琐碎的。由于递归问题,CTE方法可能有点复杂,并且不允许使用聚合,因此下面我使用了一个表变量。您还可以将其设置为保存在数据库中的永久表。
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
根据您的潜在日期范围有多大,填充表变量可能需要一段时间。例如,如果范围跨越了十年或二十年。
发布于 2011-08-19 02:41:49
听起来你可能想要一个'Calendar' file。特别是作为大型商业组织的一部分,这将变得非常有用。
在生成日历后,您可以使用以下内容获取表:
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
这将为日历文件中至少一行mySPTable的开始日期和结束日期之间的每个日期生成一行。
顺便说一下,也可以使用递归CTE生成日期范围,但从长远来看,我建议生成并使用日历文件。
快速CTE:
WITH DateRange (thisDate) as (SELECT [input_start_date]
UNION ALL
SELECT DATEADD(dy, 1, thisDate)
FROM DateRange
WHERE thisDate < [input_end_date])
发布于 2011-08-19 03:03:00
您可以使用数字表(从0开始)来完成此操作。这里我使用的是master..spt_values。SQL, Auxiliary table of numbers
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)
https://stackoverflow.com/questions/7111927
复制相似问题