Querin two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querin two tables

Author

Topic

JeanTouf
Starting Member

3 Posts

Posted - 2011-07-04 : 19:16:39

Hi guys, I would appreciate if you can help me with my problem below :
I have two tables, say Table_A representing temperature every minute and Table_B representing a serie of plane take offs (take off time, company etc...). Table_B has definitely less frequent data than A (take off roughly every 30mins for ex).
I would like to build a table which contains the temperature just before and just after every take off...and i'm struggling with this :(
Can you please help me!

Many thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-04 : 19:46:07

select
select takeofftime = ta.time, beforetemp = te1.temperature, aftertemp = te2.temperature
from takeoffs ta
left join temp te1
on te1.time = (select max(te.time) from temp te where te.time < ta time)
left join temp te2
on te2.time = (select min(te.time) from temp te where te.time > ta time)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:38:06

if you want to capture time before and after takeoff upto to minute level why do you need a table for it. You can directly derive it from takeoff time rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 14:11:15

One table holds the timed temperature the other holds the takeoff times.
Sure you could derive the temperature and times every time you needed it but it wouldn't be very useful for trending or easy/efficient to handle.
You could say why have an entity relational structure - just hold elements and their relationships;.
Depends on what they are going to be used for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources