有趣的SQL加入日期之间的日期 [英] Interesting SQL Join on dates between dates
问题描述
我有一排数据如下:
select * from mySPtable
| myPK |区域| RequestType | StartDate | EndDate |
1 SB ADD 8/14/2011 8/18/2011
2 NB RMV 8/16/2011 8/16/2011
所以我想做的是每天计算每个区域的总请求。结果应该是:
| myDate | RequestType |区域| myCount |
8/14/2011 ADD SB 1
8/15/2011 ADD SB 1
8/16/2011添加SB 1
8/16/2011 RMV NB 1
8/17/2011 ADD SB 1
8/18/2011添加SB 1
我该如何去做这个?我被骗了,没有数量的谷歌搜索有帮助。
你需要一个日历表,或者你可以生成一个与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
根据您的潜在日期范围有多大,填写表格变量可能需要一段时间。例如,如果范围跨越了十年或者两年。
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:
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
So what I want to do is count up the total requests for each area by day. Results should be:
| 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
How do I go about doing this? I'm stumped and no amount of googling has helped.
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.
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
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.
这篇关于有趣的SQL加入日期之间的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!