SQL Server Performance Forum – Threads Archive
group objects in one datetime
I have a select statement which states the following:________________________________________________________________________________
SELECT MorningPressures.Room, MorningPressures.[Date], MorningPressures.Pressure, MorningHumidity.Humidity, MorningTemps.Temperature AS [Temp], OccUnOcc.Occupied FROM dbo.MorningPressures MorningPressures
LEFT OUTER JOIN
dbo.MorningHumidity MorningHumidity ON MorningPressures.Room = MorningHumidity.Room AND
MorningPressures.[Date] = MorningHumidity.[Date]
LEFT OUTER JOIN
dbo.MorningTemps MorningTemps ON MorningHumidity.Room = MorningTemps.Room AND
MorningHumidity.[Date] = MorningTemps.[Date]
LEFT OUTER JOIN
db
ccUnocc OccUnOcc ON MorningTemps.Room = OccUnOcc.Room AND MorningTemps.[Date] = OccUnOcc.[Date]
_______________________________________________________________________________
The problem is that the data gets updated at different times; exp. pressure will update at 11302004 3:30:00pm and Temp will update at 3:30:05pm. With the way the select statement written I get a null on Temp. Is there a way to recieve the data with a variance of time like 10seconds? the
Bugster
You can use expressions and comparisons in JOINs, besides the basic t1.somecolumn = t2.somecolumn – so not putting it in a WHERE statement.
Thanks Adriaan, I will do some reading on that. I was not aware of expressions in joins.. the
Bugster
Hi John, I did not keep the script I gave you in Des Moines, but it must have been something like this. For your purposes, the only thing that really matters is the set of queries at the end (starting with the comment "test 4-second tolerance"). You certainly don’t need to use a variable for the cutoff threshhold like I did – I just did that for legibility. You could just put the cutoff inline (e.g., replacing @threshhold with something like (4.0/(24*60*60)) for the 4-second threshhold. This is just demo code, and will have to be adapted to the specific query you have listed, but hopefully it will get you pointed in the right direction. Also (just to save others from chiming in on this), this code is not optimized, and index selection by the optimizer may not be optimal. That could be overcome, but for now you just need to get your reports working, and if this type of approach runs "fast enough" wihtout further tuning, I suspect you’ll be "happy enough." Good luck!
Chris (demo code follows) set nocount on if exists (select * from sysobjects where name = ‘t1’ and xtype = ‘U’)
drop table t1 if exists (select * from sysobjects where name = ‘t2’ and xtype = ‘U’)
drop table t2 create table t1 (x int primary key, TheXDate datetime)
create table t2 (y int primary key, TheYDate datetime) declare @refDate datetime
set @refDate = getdate() declare @SecondsInDay decimal (28,23)
set @SecondsInDay = 86400 declare @threshhold decimal(28,25) insert into t1 values (1, @refDate)
insert into t1 values (2, @refDate – 1) — 1 day before @refDate
insert into t1 values (3, @refDate – 2) — 2 days before @refDate
insert into t1 values (4, @refDate – 3) — … and so forth …
insert into t1 values (5, @refDate – 4)
insert into t1 values (6, @refDate – 5)
insert into t1 values (7, @refDate – 6)
insert into t1 values (8, @refDate – 7) insert into t2 values (1, @refDate) — an exact match with t1 where t1.x = 1
insert into t2 values (2, @refDate – 1 + 5/(@SecondsInDay)) — a near-match with t1 where t1.x = 2 (off by 5 seconds)
insert into t2 values (3, @refDate – 2 – 10/(@SecondsInDay)) — a near-match with t1 where t1.x = 3 (off by 10 seconds)
insert into t2 values (4, @refDate – 3 + 15/(@SecondsInDay)) — … and so forth …
insert into t2 values (5, @refDate – 4 – 60/(@SecondsInDay)) — … and so forth …
insert into t2 values (6, @refDate – 5 + 120/(@SecondsInDay)) — … and so forth …
insert into t2 values (7, @refDate – 6 – 300/(@SecondsInDay)) — … and so forth …
insert into t2 values (8, @refDate – 7 – 600/(@SecondsInDay)) — until we’re finally "off" by 10 minutes — Display data:
print ‘Table t1:’
select * from t1 print ‘Table t2:’
select * from t2 — Test 4-second tolerance
set @threshhold = 4.0/@SecondsInDay
print ‘4-second tolerance:’
select * from t1 join t2 on abs(cast((t1.TheXDate – t2.TheYDate) as float)) <= @threshhold — Test 15-second tolerance
set @threshhold = 15/@SecondsInDay
print ’15-second tolerance:’
select * from t1 join t2 on abs(cast((t1.TheXDate – t2.TheYDate) as float)) <= @threshhold — Test 59-second tolerance
set @threshhold = 59/@SecondsInDay
print ’59-second tolerance:’
select * from t1 join t2 on abs(cast((t1.TheXDate – t2.TheYDate) as float)) <= @threshhold — Test 60-second tolerance
set @threshhold = 60/@SecondsInDay
print ’60-second tolerance:’
select * from t1 join t2 on abs(cast((t1.TheXDate – t2.TheYDate) as float)) <= @threshhold — Test 600-second tolerance
set @threshhold = 600/@SecondsInDay
print ‘600-second tolerance:’
select * from t1 join t2 on abs(cast((t1.TheXDate – t2.TheYDate) as float)) <= @threshhold drop table t1
drop table t2
thanks i will keep you posted, woking on it now… the
bugster
]]>