date joins filling in the blanks
-
Hi, I have a table with rows like this:
datetime, value
Each row is rounded to the hour but if there was no value for an hour there is no row:
2/18/2004 10:00:00, 34567
2/18/2004 12:00:00, 76543
I want to build a procedure that will return my rows with null or zero values for the missing hours. e.g.
2/18/2004 10:00:00, 34567
2/18/2004 11:00:00, 0
2/18/2004 12:00:00, 76543
I had consider building a dummy table with a row for each hour in a 24 hour clock, but that doesn't seem like a good solution and would also require me to have an extra view in my queries (to delivery the hour date part for the join)
Any ideas?
I will be using my returned set as the basis of some charts, wher the chart tool takes two arrays, in my case the datetime and the value.
My alternative is to simply loop through my recordset and stuff zero hour rows in with script, it just seems that it would be way more efficient to do it in SQL.
Thanks
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
I don't think that to have an extra table is bad but you could do something like:
CREATE TABLE TTT (dte datetime, val int)
GO
INSERT INTO TTT(dte, val) VALUES ('2/18/2004 10:00:00', 34567)
INSERT INTO TTT(dte, val) VALUES ('2/18/2004 12:00:00', 76543)
DECLARE @Date as DateTime
SET @Date = '2/18/2004 00:00:00'
SELECT D.Dte, IsNull(T.Val,0)as Value
From
(SELECT dateadd(hh, Number,@date) as Dte
from master..spt_values
where type = 'P' and number < 24 )D
Left Outer join TTT T on D.Dte = T.dte
* Noel -
Thank you that looks like a fine solution I just have to weave it in:
CREATE TABLE TTT (dte datetime, val int)
GO
INSERT INTO TTT (my select statement)
--hmmm then the date range could be as much as a couple of years. So I would make the @date = the first date in the criteria
I don't quite understand this bit
** from master..spt_values
where type = 'P' and number < 24
Can you explain a bit about this table? Is this the intended use of this table or are you just taking advatage of it for this purpose?
I imagine there may be someway I might be able to use it or create my own to handle days, weeks, months as required.
LOL if I start thinking about dynamically creating a table for every day and hour in the time period specified in the source query, it might just be more efficient to do it on the script side.
Thanks again you got me thinking
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
yes I'm writting a job to keep this lookup table up to date to avoid any y2k situations in the app
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
I have a related question:
there are many times when I want to group by individual days in a datetime field. I always finding myself thinking there has to be a better way. I end up doing this and grouping by it:
cast(DATEPART([year], dtField) as varchar) + '-'
cast(DATEPART([month], dtField) as varchar) + '-'
cast(DATEPART([day], dtField) as varchar)
Is there a better way?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
UDF's to ther rescue! I'll answer my last question myself. using the sample UDf in C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\Misc. as a base I'll modify it to take a datepart type paramter: y,m,d,h
this will clean up my code allot!
thanks for all your help.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
First If you have a table with Numbers
1. From 0 to 23 you will be able to generate the 24h in a day.
2. if you have the total number of days you could build it on the fly just like:
SELECT a.nbr as Day, b.nbr as Hr
FROM
(SELECT nbr From TblNumbers where nbr < maxNbrOfDays) a
CROSS JOIN
(SELECT nbr FROM Number where nbr < 24 
b365 * 5 ~ 1825 days in 5 years so the above query should easily handle that
the spt_values was used as a built in Numbers Table (You can have your custom though
)to group by Day you can
GROUP BY LEFT(dtField ,11)
Or as I learned in this forum
GROUP BY SUBSTRING(CAST( dtField AS binary(8)),1,4) --Best Performance
HTh
* Noel -
Ya well I did something like that. I created a table with 5 years worth of dates times rounded to the hour as datetime fields.
Then I wrote a UDF that take three params:
@increment (month,week,day,hour)
@indate (a date)
@seperator (seperation character - / etc)
so
udf_mydate(0,getdate(),'-') = '2004-2' (yyyy-mm-dd)
udf_mydate(2,getdate(),'-') = '2004-2-18' (yyyy-mm-dd)
udf_mydate(3,getdate(),'-') = '2004-2-18 11:00' (yyyy-mm-dd hh:00)
I have a table filled with log file information from several replicated windows media servers rolled up to unique 1 hour chunks per publishing point per server.
With the following stored proc I can pass in a few params and get reports by 4 different increments, for any time period, with no gaps for missing timeperiods.
@pp as varchar(50), (publishing point name)
@startdays as int, (start date as days ago)
@enddays as int, (end date as days ago)
@increment as int (increment for reports m,w,d,h)
AS
declare @start datetime
declare @end datetime
set @start = dateadd(d,@startdays,getdate())
set @end = dateadd(d,@enddays,getdate())
/*
increment options are enumerated
0=months
1=weeks
2=days
3=hours
*/
SELECT TOP 100 PERCENT
dbo.udf_MyDate(@increment,dbo.tbldatetimelookup.datetimelookup,'-') as period,
isnull(SUM(bytesSent / 1000),0) AS mbssent
FROM (SELECT PPName, hours, bytesSent FROM netro.statTotals WHERE (PPName = @pp))
derivedView RIGHT OUTER JOIN
dbo.tbldatetimelookup ON derivedView.hours = dbo.tbldatetimelookup.datetimelookup
WHERE (dbo.tbldatetimelookup.datetimelookup > @start AND dbo.tbldatetimelookup.datetimelookup < @end)
GROUP BY dbo.udf_MyDate(@increment,dbo.tbldatetimelookup.datetimelookup,'-')
ORDER BY dbo.udf_MyDate(@increment,dbo.tbldatetimelookup.datetimelookup,'-')
And bob's yer uncle and I'm a happy camper.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply