在没有返回值的查询的返回值中插入日期 [英] Insert Dates in the return from a query where there is none
问题描述
我们正在建立一个查询，以计算每天每小时的事件数.在大多数日子里，有几小时没有任何活动，因此在运行查询的情况下，每小时的活动计数会显示出来，但是存在空白，查询会排除这些空白.我们仍然希望显示没有活动的小时数，并显示零，以便可以绘制零值.我们使用的查询看起来像这样……
We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hour show up but there are gaps and the query excludes these. We still want to show the hours that do not have activity and display a zero so that zero value can then be charted. The query we using looks like this …
select datepart(Year, dev_time) as Year,
datepart(Month, dev_time) as Month,
datepart(Day, dev_time) as Day,
datepart(Hour, dev_time) as Hour,
count(tdm_msg) as Total_ACTIVITES
from TCKT_ACT
where tdm_msg = ‘4162′ and dev_time >= DATEADD(day, - 1, GETDATE())
group by datepart(Year, dev_time) ,
datepart(Month, dev_time) ,
datepart(Day, dev_time),
datepart(Hour, dev_time)
order by datepart(Year, dev_time) asc,
datepart(Month, dev_time) asc,
datepart(Day, dev_time) asc,
datepart(Hour, dev_time) asc
推荐答案
首先，我根据由所描述的递归公用表查询创建了一个表函数 Dave Markle(感谢您向我展示Dave！).这非常好，因为我只需要创建一次函数，就可以使用它来分析任何间隔.
First I created a table function based on the recursive common table query described by Dave Markle (thanks for showing me this Dave!). This is extremely sweet because I only have to make the function once and I can use it for analysing any intervals.
if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go
create function fn_daterange
(
@MinDate as datetime,
@MaxDate as datetime,
@intval as datetime
)
returns table
--**************************************************************************
-- Procedure: fn_daterange()
-- Author: Ron Savage
-- Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date Init. Description
-- 12/16/2008 RS Created.
-- **************************************************************************
as
return
WITH times (startdate, enddate, intervl) AS
(
SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
UNION ALL
SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
FROM times
WHERE startdate + intervl <= @MaxDate
)
select startdate, enddate from times;
go
因此，如果您仅从该函数中进行选择，则会得到一个时间间隔表，如下所示:
So if you do a select from that function all by itself you get a table of time intervals like this:
fn_daterange('12/14/2008 10:00:00'，'12/14/2008 20:00:00'，'01:00:00')
返回:
startdate enddate intervl
----------------------- ----------------------- -----------------------
2008-12-14 10:00:00.000 2008-12-14 10:59:59.997 1900-01-01 01:00:00.000
2008-12-14 11:00:00.000 2008-12-14 11:59:59.997 1900-01-01 01:00:00.000
2008-12-14 12:00:00.000 2008-12-14 12:59:59.997 1900-01-01 01:00:00.000
2008-12-14 13:00:00.000 2008-12-14 13:59:59.997 1900-01-01 01:00:00.000
2008-12-14 14:00:00.000 2008-12-14 14:59:59.997 1900-01-01 01:00:00.000
2008-12-14 15:00:00.000 2008-12-14 15:59:59.997 1900-01-01 01:00:00.000
2008-12-14 16:00:00.000 2008-12-14 16:59:59.997 1900-01-01 01:00:00.000
2008-12-14 17:00:00.000 2008-12-14 17:59:59.997 1900-01-01 01:00:00.000
2008-12-14 18:00:00.000 2008-12-14 18:59:59.997 1900-01-01 01:00:00.000
2008-12-14 19:00:00.000 2008-12-14 19:59:59.997 1900-01-01 01:00:00.000
2008-12-14 20:00:00.000 2008-12-14 20:59:59.997 1900-01-01 01:00:00.000
然后我制作了事件数据的样本表:
Then I made a sample table of event data:
eventdate eventnote
----------------------- --------------------
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
22 Row(s) affected
然后我像这样用左外连接将它们钩在一起:
Then I hooked them together with a LEFT OUTER JOIN like so:
select
dr.startdate,
dr.enddate,
count(me.eventdate) as eventcount
from
fn_daterange('12/14/2008 10:00:00', '12/14/2008 20:00:00', '01:00:00' ) dr
LEFT OUTER JOIN myevents me
on ( me.eventdate between dr.startdate and dr.enddate)
group by
dr.startdate,
dr.enddate
startdate enddate eventcount
----------------------- ----------------------- ----------
2008-12-14 10:00:00.000 2008-12-14 10:59:59.993 7
2008-12-14 11:00:00.000 2008-12-14 11:59:59.993 5
2008-12-14 12:00:00.000 2008-12-14 12:59:59.993 0
2008-12-14 13:00:00.000 2008-12-14 13:59:59.993 0
2008-12-14 14:00:00.000 2008-12-14 14:59:59.993 3
2008-12-14 15:00:00.000 2008-12-14 15:59:59.993 0
2008-12-14 16:00:00.000 2008-12-14 16:59:59.993 0
2008-12-14 17:00:00.000 2008-12-14 17:59:59.993 0
2008-12-14 18:00:00.000 2008-12-14 18:59:59.993 0
2008-12-14 19:00:00.000 2008-12-14 19:59:59.993 7
2008-12-14 20:00:00.000 2008-12-14 20:59:59.993 0
11 Row(s) affected
这真是太棒了-我可以在工作中使用它进行各种分析！ :-)
HOLY CRAP that is sweet - I can use this for all kinds of analysis at work! :-)
感谢弗雷德(Fred)的问题，感谢戴夫(Dave)的常见表查询信息！
Thanks Fred for the question and Dave for the info on common table queries!
罗恩
这篇关于在没有返回值的查询的返回值中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
