SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 A SELECT request
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/17/2008 :  08:11:21  Show Profile  Reply with Quote
Assume we have two tables:

T1                                     T2

F1    F2                               Field1      Field2
--    --                               ------      ------ 
A      A1                              A2           Fred             
A      A2                              B0           Judy
B      B0                              B1           Max
B      B2                              B3           Joe
B      B3

I need to write a SELECT to return:
- Exact data from Field1 which matches F2; i.e. A2 (from Field1) for A2 (from F2)
- Or the closest smaller one; i.e. B1 (from Field1) for B2 (from F2)

Any help is highly appreciated.


Canada DBA

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/17/2008 :  08:20:26  Show Profile  Visit nr's Homepage  Reply with Quote
Have you had a go at it yourself.
You could do it using a correlated subquery

where field2 = (select max(t2.field2) from t2 where t2.field2 <= t1.f2)


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/17/2008 :  08:24:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT		d.F1,
		d.F2,
		p.Field1,
		p.Field2
FROM		(
			SELECT		t1.F1,
					t1.F2,
					ISNULL((SELECT MAX(y.Field1) FROM @T2 AS y WHERE y.Field1 LIKE t1.F1 + '%' AND y.Field1 < t1.F2), t2.Field1) AS Yak
			FROM		@T1 AS t1
			LEFT JOIN	@T2 AS t2 ON t2.Field1 = t1.F2
		) AS d
LEFT JOIN	@T2 AS p ON p.Field1 = d.Yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/17/2008 :  08:54:00  Show Profile  Reply with Quote
Thanks for the posts! I'll try them and reply the results.

Before post the request, I wrote the following but didn't work properly.
SELECT A.F1, A.F2, B.Field1, B.Field2
FROM T1 A, T2 B
WHERE B.Field1 = (SELECT TOP 1 B.Field1 FROM T2 where A.F2 >= B.Field1)

Canada DBA
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/17/2008 :  09:48:33  Show Profile  Reply with Quote
Hi Nigel,

SELECT A.F1, A.F2, B.Field1, B.Field2
FROM T1 A, T2 B
where B.field2 = (select max(B.field2) from t2 where B.field2 <= A.f2)

returnes the following error:
Msg 147, Level 16, State 2, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


quote:
Originally posted by nr

Have you had a go at it yourself.
You could do it using a correlated subquery

where field2 = (select max(t2.field2) from t2 where t2.field2 <= t1.f2)


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



Canada DBA

Edited by - CanadaDBA on 04/17/2008 09:49:08
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/17/2008 :  09:54:26  Show Profile  Reply with Quote
Hi Peter,

This looks work fine. Here is the result.

F1   F2   Field1 Field2
---- ---- ------ ------
A    A1   NULL   NULL
A    A2   A2     Fred
B    B0   B0     Judy
B    B2   B1     Max
B    B3   B1     Max

Only the last row has incorrect value. It should be

B    B3   B3     Joe

Which can be corrected by adding an equal sign: y.Field1 <= t1.F2

Thanks a lot Peter!

quote:
Originally posted by Peso

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT		d.F1,
		d.F2,
		p.Field1,
		p.Field2
FROM		(
			SELECT		t1.F1,
					t1.F2,
					ISNULL((SELECT MAX(y.Field1) FROM @T2 AS y WHERE y.Field1 LIKE t1.F1 + '%' AND y.Field1 < t1.F2), t2.Field1) AS Yak
			FROM		@T1 AS t1
			LEFT JOIN	@T2 AS t2 ON t2.Field1 = t1.F2
		) AS d
LEFT JOIN	@T2 AS p ON p.Field1 = d.Yak



E 12°55'05.25"
N 56°04'39.16"




Canada DBA

Edited by - CanadaDBA on 04/17/2008 15:50:08
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/17/2008 :  12:23:46  Show Profile  Reply with Quote
I am working on Peter's script. In fact F2 and Field1 are date and time and are like '2007-08-13 15:00:00'. I mean the minutes and seconds are 0. So, I have to change the LIKE and % usage and replace t1.F1 with something else.

Canada DBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/17/2008 :  16:01:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You should have told us from the beginning...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/17/2008 :  17:35:05  Show Profile  Visit nr's Homepage  Reply with Quote
quote:
Originally posted by CanadaDBA

Hi Nigel,

SELECT A.F1, A.F2, B.Field1, B.Field2
FROM T1 A, T2 B
where B.field2 = (select max(B.field2) from t2 where B.field2 <= A.f2)

returnes the following error:
Msg 147, Level 16, State 2, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


quote:
Originally posted by nr

Have you had a go at it yourself.
You could do it using a correlated subquery

where field2 = (select max(t2.field2) from t2 where t2.field2 <= t1.f2)

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



Canada DBA





SELECT A.F1, A.F2, B.Field1, B.Field2
FROM T1 A, T2 B
where B.field2 = (select max(t2.field2) from t2 where t2.field2 <= A.f2)


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

Edited by - nr on 04/17/2008 17:35:51
Go to Top of Page

chandan_joshi80
Starting Member

India
30 Posts

Posted - 04/18/2008 :  05:36:19  Show Profile  Reply with Quote
SELECT * FROM T3 WHERE FIELD1 IN (SELECT F2 FROM T2 )

chandan Joshi
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/18/2008 :  07:21:24  Show Profile  Visit nr's Homepage  Reply with Quote
quote:
Originally posted by chandan_joshi80

SELECT * FROM T3 WHERE FIELD1 IN (SELECT F2 FROM T2 )

chandan Joshi



Er - did you read the question?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/18/2008 :  09:38:37  Show Profile  Reply with Quote
Nigel,

I merged your script with Peter's table creation, and it didn't return any records.

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT A.F1, A.F2, B.Field1, B.Field2 
FROM @T1 A, @T2 B
where B.field2 = (select max(t2.field2) from @t2 as t2 where t2.field2 <= A.f2)

Results:

(5 row(s) affected)

(4 row(s) affected)
F1   F2   Field1 Field2
---- ---- ------ ------

(0 row(s) affected)



Canada DBA
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/18/2008 :  16:42:48  Show Profile  Reply with Quote
Here is the real data and Peter's modified script. You can run and see the results. I have filtered the data only for DEAN and HWRE. Lat and Long are acceptable if the tolerance is +/- 5 degree.

The script works fine except for Obs = '2007-08-16 06:00:00.000' that returns two rows instead of one. You can run and see.


DECLARE @T1 TABLE (Storm VARCHAR(10), Obs DATETIME, ObsLat Decimal(5, 1), ObsLong Decimal(5, 1))

INSERT @T1
SELECT 'DEAN', '2007-08-13 15:00:00', 12, -31.6 UNION ALL
SELECT 'DEAN', '2007-08-13 21:00:00', 11.9, -33.1 UNION ALL
SELECT 'DEAN', '2007-08-14 03:00:00', 12, -35 UNION ALL
SELECT 'DEAN', '2007-08-14 09:00:00', 12, -36.8 UNION ALL
SELECT 'DEAN', '2007-08-14 15:00:00', 11.7, -39.4 UNION ALL
SELECT 'DEAN', '2007-08-14 21:00:00', 11.6, -41 UNION ALL
SELECT 'DEAN', '2007-08-15 03:00:00', 12, -42.3 UNION ALL
SELECT 'DEAN', '2007-08-15 09:00:00', 12.2, -44.2 UNION ALL
SELECT 'DEAN', '2007-08-15 15:00:00', 12.4, -46 UNION ALL
SELECT 'DEAN', '2007-08-15 21:00:00', 13.1, -47.9 UNION ALL
SELECT 'DEAN', '2007-08-16 03:00:00', 13.1, -50.2 UNION ALL
SELECT 'DEAN', '2007-08-16 06:00:00', 13.2, -51.3 UNION ALL
SELECT 'DEAN', '2007-08-16 09:00:00', 13.4, -52.3 UNION ALL
SELECT 'DEAN', '2007-08-16 12:00:00', 13.5, -53.3 UNION ALL
SELECT 'DEAN', '2007-08-16 15:00:00', 13.7, -54.3 UNION ALL
SELECT 'DEAN', '2007-08-16 18:00:00', 13.8, -55.5 UNION ALL
SELECT 'DEAN', '2007-08-16 21:00:00', 14, -56.5 UNION ALL
SELECT 'DEAN', '2007-08-17 00:00:00', 14, -57.8 UNION ALL
SELECT 'DEAN', '2007-08-17 03:00:00', 14.1, -58.7 UNION ALL
SELECT 'DEAN', '2007-08-17 06:00:00', 14.3, -59.8 UNION ALL
SELECT 'DEAN', '2007-08-17 09:00:00', 14.3, -60.9 UNION ALL
SELECT 'DEAN', '2007-08-17 12:00:00', 14.4, -61.7 UNION ALL
SELECT 'DEAN', '2007-08-17 15:00:00', 14.6, -62.6 UNION ALL
SELECT 'DEAN', '2007-08-17 17:00:00', 14.8, -63.6 UNION ALL
SELECT 'DEAN', '2007-08-17 21:00:00', 15, -64.5 UNION ALL
SELECT 'DEAN', '2007-08-18 00:00:00', 14.9, -65.1 UNION ALL
SELECT 'DEAN', '2007-08-18 03:00:00', 14.9, -65.9 UNION ALL
SELECT 'DEAN', '2007-08-18 06:00:00', 15, -66.7 UNION ALL
SELECT 'DEAN', '2007-08-18 09:00:00', 15.1, -67.3 UNION ALL
SELECT 'DEAN', '2007-08-18 12:00:00', 15.4, -67.9 UNION ALL
SELECT 'DEAN', '2007-08-18 15:00:00', 15.7, -68.6 UNION ALL
SELECT 'DEAN', '2007-08-18 18:00:00', 15.9, -69.4 UNION ALL
SELECT 'DEAN', '2007-08-18 21:00:00', 16.1, -70.2 UNION ALL
SELECT 'DEAN', '2007-08-19 00:00:00', 16, -71 UNION ALL
SELECT 'DEAN', '2007-08-19 03:00:00', 16.2, -71.7 UNION ALL
SELECT 'DEAN', '2007-08-19 06:00:00', 16.4, -72.6 UNION ALL
SELECT 'DEAN', '2007-08-19 09:00:00', 16.6, -73.4 UNION ALL
SELECT 'DEAN', '2007-08-19 12:00:00', 16.8, -74.3 UNION ALL
SELECT 'DEAN', '2007-08-19 15:00:00', 17, -75.1 UNION ALL
SELECT 'DEAN', '2007-08-19 18:00:00', 17.1, -76 UNION ALL
SELECT 'DEAN', '2007-08-19 21:00:00', 17.3, -76.8

DECLARE @T2 TABLE (Model VARCHAR(10), Init DATETIME, InitLat Decimal(5, 1), InitLong Decimal(5, 1))

INSERT @T2
SELECT 'HWRF', '2007-07-18 18:00:00', 15, -139.5 UNION ALL
SELECT 'HWRF', '2007-07-19 00:00:00', 15.1, -140.7 UNION ALL
SELECT 'HWRF', '2007-07-19 06:00:00', 15.4, -142.1 UNION ALL
SELECT 'HWRF', '2007-07-19 12:00:00', 15.5, -143.5 UNION ALL
SELECT 'HWRF', '2007-07-19 18:00:00', 15.7, -145 UNION ALL
SELECT 'HWRF', '2007-07-20 00:00:00', 16.1, -146.7 UNION ALL
SELECT 'HWRF', '2007-07-20 06:00:00', 16, -148.3 UNION ALL
SELECT 'HWRF', '2007-07-23 00:00:00', 13.3, -107.2 UNION ALL
SELECT 'HWRF', '2007-07-23 06:00:00', 13.6, -108 UNION ALL
SELECT 'HWRF', '2007-07-23 12:00:00', 14.6, -108.5 UNION ALL
SELECT 'HWRF', '2007-07-23 18:00:00', 15.2, -173.1 UNION ALL
SELECT 'HWRF', '2007-07-23 18:00:00', 15.1, -108.9 UNION ALL
SELECT 'HWRF', '2007-07-24 00:00:00', 15.8, -109.2 UNION ALL
SELECT 'HWRF', '2007-07-24 06:00:00', 16.4, -109.6 UNION ALL
SELECT 'HWRF', '2007-07-24 12:00:00', 16.6, -110.3 UNION ALL
SELECT 'HWRF', '2007-07-24 18:00:00', 17.1, -110.5 UNION ALL
SELECT 'HWRF', '2007-07-25 00:00:00', 18.1, -110.8 UNION ALL
SELECT 'HWRF', '2007-07-25 06:00:00', 18.7, -111.2 UNION ALL
SELECT 'HWRF', '2007-07-25 12:00:00', 19.3, -111.7 UNION ALL
SELECT 'HWRF', '2007-07-25 18:00:00', 19.9, -112.1 UNION ALL
SELECT 'HWRF', '2007-07-26 00:00:00', 20.4, -112.4 UNION ALL
SELECT 'HWRF', '2007-07-26 06:00:00', 20.7, -112.8 UNION ALL
SELECT 'HWRF', '2007-07-26 12:00:00', 21.5, -113.7 UNION ALL
SELECT 'HWRF', '2007-07-26 18:00:00', 22.2, -114.6 UNION ALL
SELECT 'HWRF', '2007-07-27 00:00:00', 22.5, -115.7 UNION ALL
SELECT 'HWRF', '2007-07-27 06:00:00', 22.7, -116.7 UNION ALL
SELECT 'HWRF', '2007-07-28 12:00:00', 25.3, -73.8 UNION ALL
SELECT 'HWRF', '2007-07-28 18:00:00', 25.4, -73.6 UNION ALL
SELECT 'HWRF', '2007-07-29 00:00:00', 27.2, -73.2 UNION ALL
SELECT 'HWRF', '2007-07-29 00:00:00', 9.5, -112.4 UNION ALL
SELECT 'HWRF', '2007-07-29 12:00:00', 9.8, -115.2 UNION ALL
SELECT 'HWRF', '2007-07-29 18:00:00', 10.7, -115.7 UNION ALL
SELECT 'HWRF', '2007-07-30 00:00:00', 11.1, -116.3 UNION ALL
SELECT 'HWRF', '2007-07-30 00:00:00', 31.1, -70.3 UNION ALL
SELECT 'HWRF', '2007-07-30 06:00:00', 11.8, -117.1 UNION ALL
SELECT 'HWRF', '2007-07-30 12:00:00', 32.7, -67.7 UNION ALL
SELECT 'HWRF', '2007-07-30 12:00:00', 9.7, -45.7 UNION ALL
SELECT 'HWRF', '2007-07-30 12:00:00', 12, -118 UNION ALL
SELECT 'HWRF', '2007-07-30 18:00:00', 9.8, -47.1 UNION ALL
SELECT 'HWRF', '2007-07-30 18:00:00', 34.4, -67.5 UNION ALL
SELECT 'HWRF', '2007-07-30 18:00:00', 12.2, -119 UNION ALL
SELECT 'HWRF', '2007-07-31 00:00:00', 35.4, -66.7 UNION ALL
SELECT 'HWRF', '2007-07-31 00:00:00', 10.2, -48.8 UNION ALL
SELECT 'HWRF', '2007-07-31 06:00:00', 10.3, -50.1 UNION ALL
SELECT 'HWRF', '2007-07-31 06:00:00', 37.1, -65.9 UNION ALL
SELECT 'HWRF', '2007-07-31 12:00:00', 39.4, -63.7 UNION ALL
SELECT 'HWRF', '2007-07-31 12:00:00', 10.8, -50.7 UNION ALL
SELECT 'HWRF', '2007-07-31 18:00:00', 40.8, -62.3 UNION ALL
SELECT 'HWRF', '2007-07-31 18:00:00', 13.1, -123.4 UNION ALL
SELECT 'HWRF', '2007-07-31 18:00:00', 11.1, -52.2 UNION ALL
SELECT 'HWRF', '2007-08-01 00:00:00', 13.1, -124 UNION ALL
SELECT 'HWRF', '2007-08-01 00:00:00', 11.2, -53.4 UNION ALL
SELECT 'HWRF', '2007-08-01 06:00:00', 13, -124.8 UNION ALL
SELECT 'HWRF', '2007-08-01 06:00:00', 11.4, -54.8 UNION ALL
SELECT 'HWRF', '2007-08-01 12:00:00', 11.7, -56.4 UNION ALL
SELECT 'HWRF', '2007-08-01 12:00:00', 13.2, -125.4 UNION ALL
SELECT 'HWRF', '2007-08-01 18:00:00', 11.9, -58.4 UNION ALL
SELECT 'HWRF', '2007-08-01 18:00:00', 13.3, -126.6 UNION ALL
SELECT 'HWRF', '2007-08-02 00:00:00', 13.3, -128 UNION ALL
SELECT 'HWRF', '2007-08-02 00:00:00', 12.3, -60 UNION ALL
SELECT 'HWRF', '2007-08-02 06:00:00', 13.3, -129.4 UNION ALL
SELECT 'HWRF', '2007-08-02 06:00:00', 12.4, -61.4 UNION ALL
SELECT 'HWRF', '2007-08-02 12:00:00', 13.4, -129.8 UNION ALL
SELECT 'HWRF', '2007-08-02 18:00:00', 12.7, -67 UNION ALL
SELECT 'HWRF', '2007-08-03 18:00:00', 16.7, -116.5 UNION ALL
SELECT 'HWRF', '2007-08-04 00:00:00', 16.6, -117.7 UNION ALL
SELECT 'HWRF', '2007-08-04 00:00:00', 13.9, -80.2 UNION ALL
SELECT 'HWRF', '2007-08-04 12:00:00', 9.7, -108 UNION ALL
SELECT 'HWRF', '2007-08-05 00:00:00', 10.8, -111.3 UNION ALL
SELECT 'HWRF', '2007-08-06 00:00:00', 12.4, -115.9 UNION ALL
SELECT 'HWRF', '2007-08-06 06:00:00', 12.6, -116.4 UNION ALL
SELECT 'HWRF', '2007-08-06 12:00:00', 12.8, -117.3 UNION ALL
SELECT 'HWRF', '2007-08-06 18:00:00', 13.4, -119.2 UNION ALL
SELECT 'HWRF', '2007-08-07 00:00:00', 13.8, -119.8 UNION ALL
SELECT 'HWRF', '2007-08-07 12:00:00', 14.4, -121.5 UNION ALL
SELECT 'HWRF', '2007-08-13 00:00:00', 14, -146.3 UNION ALL
SELECT 'HWRF', '2007-08-13 00:00:00', 12.7, -27.1 UNION ALL
SELECT 'HWRF', '2007-08-13 06:00:00', 14.3, -147.5 UNION ALL
SELECT 'HWRF', '2007-08-13 12:00:00', 12, -30.6 UNION ALL
SELECT 'HWRF', '2007-08-13 12:00:00', 21.6, -85.8 UNION ALL
SELECT 'HWRF', '2007-08-13 12:00:00', 14.7, -148.8 UNION ALL
SELECT 'HWRF', '2007-08-13 18:00:00', 15.2, -150 UNION ALL
SELECT 'HWRF', '2007-08-13 18:00:00', 11.9, -32.2 UNION ALL
SELECT 'HWRF', '2007-08-13 18:00:00', 22, -86.9 UNION ALL
SELECT 'HWRF', '2007-08-14 00:00:00', 12, -34.1 UNION ALL
SELECT 'HWRF', '2007-08-14 00:00:00', 22, -87.7 UNION ALL
SELECT 'HWRF', '2007-08-14 00:00:00', 15.7, -151.5 UNION ALL
SELECT 'HWRF', '2007-08-14 06:00:00', 12, -35.9 UNION ALL
SELECT 'HWRF', '2007-08-14 06:00:00', 22.2, -88.5 UNION ALL
SELECT 'HWRF', '2007-08-14 06:00:00', 16.1, -152.6 UNION ALL
SELECT 'HWRF', '2007-08-14 12:00:00', 11.7, -38.4 UNION ALL
SELECT 'HWRF', '2007-08-14 12:00:00', 22.3, -89.7 UNION ALL
SELECT 'HWRF', '2007-08-14 12:00:00', 16.5, -153.7 UNION ALL
SELECT 'HWRF', '2007-08-14 18:00:00', 22.9, -90.2 UNION ALL
SELECT 'HWRF', '2007-08-14 18:00:00', 17, -154.5 UNION ALL
SELECT 'HWRF', '2007-08-14 18:00:00', 11.6, -40.1 UNION ALL
SELECT 'HWRF', '2007-08-15 00:00:00', 17.4, -155.2 UNION ALL
SELECT 'HWRF', '2007-08-15 00:00:00', 11.9, -41.4 UNION ALL
SELECT 'HWRF', '2007-08-15 00:00:00', 23.3, -90.7 UNION ALL
SELECT 'HWRF', '2007-08-15 06:00:00', 24.1, -91.4 UNION ALL
SELECT 'HWRF', '2007-08-15 06:00:00', 17.7, -156 UNION ALL
SELECT 'HWRF', '2007-08-15 06:00:00', 12.1, -43.3 UNION ALL
SELECT 'HWRF', '2007-08-15 12:00:00', 24.5, -93.1 UNION ALL
SELECT 'HWRF', '2007-08-15 12:00:00', 12.4, -45.1 UNION ALL
SELECT 'HWRF', '2007-08-15 12:00:00', 17.3, -157.3 UNION ALL
SELECT 'HWRF', '2007-08-15 18:00:00', 12.9, -47.1 UNION ALL
SELECT 'HWRF', '2007-08-15 18:00:00', 25.9, -93.7 UNION ALL
SELECT 'HWRF', '2007-08-15 18:00:00', 17.3, -158 UNION ALL
SELECT 'HWRF', '2007-08-16 00:00:00', 13.1, -49.2 UNION ALL
SELECT 'HWRF', '2007-08-16 00:00:00', 26.2, -95.1 UNION ALL
SELECT 'HWRF', '2007-08-16 00:00:00', 17.4, -159 UNION ALL
SELECT 'HWRF', '2007-08-16 06:00:00', 13.3, -51.4 UNION ALL
SELECT 'HWRF', '2007-08-16 06:00:00', 26.7, -96.1 UNION ALL
SELECT 'HWRF', '2007-08-16 12:00:00', 13.6, -53.6 UNION ALL
SELECT 'HWRF', '2007-08-16 18:00:00', 13.7, -55.7 UNION ALL
SELECT 'HWRF', '2007-08-17 00:00:00', 14, -57.9 UNION ALL
SELECT 'HWRF', '2007-08-17 06:00:00', 14, -59.9 UNION ALL
SELECT 'HWRF', '2007-08-17 12:00:00', 14.2, -61.7 UNION ALL
SELECT 'HWRF', '2007-08-17 18:00:00', 14.7, -63.5 UNION ALL
SELECT 'HWRF', '2007-08-18 00:00:00', 14.9, -65.1 UNION ALL
SELECT 'HWRF', '2007-08-18 06:00:00', 15, -66.6 UNION ALL
SELECT 'HWRF', '2007-08-18 12:00:00', 15.4, -67.9 UNION ALL
SELECT 'HWRF', '2007-08-18 18:00:00', 15.8, -69.3 UNION ALL
SELECT 'HWRF', '2007-08-19 00:00:00', 16.2, -70.8 UNION ALL
SELECT 'HWRF', '2007-08-19 06:00:00', 16.4, -72.6 UNION ALL
SELECT 'HWRF', '2007-08-19 12:00:00', 16.8, -74.3 UNION ALL
SELECT 'HWRF', '2007-08-19 18:00:00', 17.1, -75.9 UNION ALL
SELECT 'HWRF', '2007-08-20 00:00:00', 17.3, -77.6 UNION ALL
SELECT 'HWRF', '2007-08-20 06:00:00', 17.5, -79.5 UNION ALL
SELECT 'HWRF', '2007-08-20 12:00:00', 17.7, -81.4 UNION ALL
SELECT 'HWRF', '2007-08-20 18:00:00', 18, -83.4


SELECT D.Storm, D.Obs, D.ObsLat, D.ObsLong, P.model, P.Init, P.InitLat, P.InitLong
FROM (SELECT A.Storm, A.Obs, A.ObsLat, A.ObsLong, B.model, B.Init, B.InitLat, B.InitLong,
ISNULL((SELECT MAX(Y.Init) FROM @T2 AS Y WHERE Convert(Char(10),Y.Init,101) = Convert(Char(10), A.Obs,101) AND Y.Init <= A.Obs), B.Init) AS Yak
FROM @T1 A LEFT JOIN @T2 B
ON B.Init = A.Obs) AS D
LEFT JOIN @T2 AS P ON P.Init = D.Yak
WHERE ( ABS(P.InitLat-D.ObsLat) <= 5 )
AND ( ABS(P.InitLong-D.ObsLong) <= 5 )
Order by D.Storm, D.Obs




Canada DBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/18/2008 :  16:51:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Wow. This is a completely different thing!

For the storm, with every two points (times) you have to assume a linear movement.
Then calculate the straight line between those points.
Then you make two boundary lines +5 and -5 degree.

When that is done, you match those two boundary lines (and times!) if model is included within the boundaries and the two times.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/18/2008 :  20:47:54  Show Profile  Visit nr's Homepage  Reply with Quote
quote:
Originally posted by CanadaDBA

Nigel,

I merged your script with Peter's table creation, and it didn't return any records.

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT A.F1, A.F2, B.Field1, B.Field2 
FROM @T1 A, @T2 B
where B.field2 = (select max(t2.field2) from @t2 as t2 where t2.field2 <= A.f2)

Results:

(5 row(s) affected)

(4 row(s) affected)
F1   F2   Field1 Field2
---- ---- ------ ------

(0 row(s) affected)



Canada DBA



You need to match T1.F2 with T2.field1

SELECT A.F1, A.F2, B.Field1, B.Field2
FROM @T1 A, @T2 B
where B.field1 = (select max(t2.field1) from @t2 as t2 where t2.field1 <= A.f2)


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/21/2008 :  09:21:57  Show Profile  Reply with Quote
Thanks Peter for the hints. I worked on Nigel's script and it looks so simple but works very well. Here is his modified script that works with the above real data.

SELECT A.Storm, A.Obs, A. ObsLat, A.ObsLong, B.Model, B.Init, B.InitLat, B.InitLong 
FROM @T1 A, @T2 B
where B.Init = (select max(t2.Init) from @t2 as t2 where t2.Init <= A.Obs)
    AND ( ABS(B.InitLat-A.ObsLat) <= 5 ) 
    AND ( ABS(B.InitLong-A.ObsLong) <= 5 ) 
Order by A.Storm, A.Obs

Thanks guyes!
Good job Nigel!

Canada DBA

Edited by - CanadaDBA on 04/21/2008 09:27:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000