quite complex (or simple) join

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

quite complex (or simple) join

Author

Topic

JeanTouf
Starting Member

3 Posts

Posted - 2011-07-18 : 08:26:08

Hi mates, can you please help me with below.

I've got two tables :
- Table1 : date, temperature
- Table2 : date, windSpeed (not the same dates as in table1)

I would like to write a SQL request to retrieve the 5 temperatures entries in table 1 before and after each date in table2 along with windSpeed.

thanks a lot for your help

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-18 : 10:00:10

There are probably much better ways, but the following should work:


;WITH cte
AS
(
	SELECT T1.[date] AS TDate
		,T1.temperature
		,T2.[date] AS WDate
		,T2.windspeed
		,ROW_NUMBER() OVER (PARTITION T2.[date] ORDER BY T1.[date] DESC) AS RowNum
	FROM Table1 T1
		JOIN Table2 T2
			ON T1.[date] < T2.[date]

	UNION ALL

	SELECT T1.[date] AS TDate
		,T1.temperature
		,T2.[date] AS WDate
		,T2.windspeed
		,ROW_NUMBER() OVER (PARTITION T2.[date] ORDER BY T1.[date]) AS RowNum
	FROM Table1 T1
		JOIN Table2 T2
			ON T1.[date] >= T2.[date]
)
SELECT TDate, temperature, WDate, windspeed
FROM cte
WHERE RowNum <= 5
ORDER BY TDate

JeanTouf
Starting Member

3 Posts

Posted - 2011-07-18 : 12:21:00

Hi Thanks for your reply. It unfortunately won't work as i'm looking to have the 10 temperatures (the 5 after and 5 before)on the same row for each windSpeed.
So the resulting table should have exactly the same number of rows as the the windSpeed table.

Very grateful if anybody can help

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-18 : 14:08:49

This will give you a start. Then you will have to PIVOT the result.

SELECT t2.Date, t2.WindSpeed, f.Temperature, f.Temperature
FROM dbo.Table2 AS t2
OUTER APPLY (SELECT TOP(5) x.Temperature FROM dbo.Table1 AS x WHERE x.Date < t2.Date ORDER BY x.Date DESC) AS f(Temperature)
OUTER APPLY (SELECT TOP(5) x.Temperature FROM dbo.Table1 AS x WHERE x.Date > t2.Date ORDER BY x.Date) AS g(Temperature)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources