Author |
Topic |
CanadaDBA
583 Posts |
Posted - 2008-04-17 : 08:11:21
|
Assume we have two tables: T1 T2F1 F2 Field1 Field2-- -- ------ ------ A A1 A2 Fred A A2 B0 JudyB B0 B1 MaxB B2 B3 JoeB 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
12543 Posts |
Posted - 2008-04-17 : 08:20:26
|
Have you had a go at it yourself.You could do it using a correlated subquerywhere 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-17 : 08:24:05
|
[code]DECLARE @T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))INSERT @T1SELECT 'A', 'A1' UNION ALLSELECT 'A', 'A2' UNION ALLSELECT 'B', 'B0' UNION ALLSELECT 'B', 'B2' UNION ALLSELECT 'B', 'B3'DECLARE @T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))INSERT @T2SELECT 'A2', 'Fred' UNION ALLSELECT 'B0', 'Judy' UNION ALLSELECT 'B1', 'Max' UNION ALLSELECT 'B3', 'Joe'SELECT d.F1, d.F2, p.Field1, p.Field2FROM ( 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 dLEFT JOIN @T2 AS p ON p.Field1 = d.Yak[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-17 : 08:54:00
|
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 BWHERE B.Field1 = (SELECT TOP 1 B.Field1 FROM T2 where A.F2 >= B.Field1)Canada DBA |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-17 : 09:48:33
|
Hi Nigel,SELECT A.F1, A.F2, B.Field1, B.Field2 FROM T1 A, T2 Bwhere B.field2 = (select max(B.field2) from t2 where B.field2 <= A.f2)returnes the following error:Msg 147, Level 16, State 2, Line 1An 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 subquerywhere 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 |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-17 : 09:54:26
|
Hi Peter,This looks work fine. Here is the result. F1 F2 Field1 Field2---- ---- ------ ------A A1 NULL NULLA A2 A2 FredB B0 B0 JudyB B2 B1 MaxB 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.F2Thanks a lot Peter!quote: Originally posted by Peso
DECLARE @T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))INSERT @T1SELECT 'A', 'A1' UNION ALLSELECT 'A', 'A2' UNION ALLSELECT 'B', 'B0' UNION ALLSELECT 'B', 'B2' UNION ALLSELECT 'B', 'B3'DECLARE @T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))INSERT @T2SELECT 'A2', 'Fred' UNION ALLSELECT 'B0', 'Judy' UNION ALLSELECT 'B1', 'Max' UNION ALLSELECT 'B3', 'Joe'SELECT d.F1, d.F2, p.Field1, p.Field2FROM ( 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 dLEFT JOIN @T2 AS p ON p.Field1 = d.Yak E 12°55'05.25"N 56°04'39.16"
Canada DBA |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-17 : 12:23:46
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-17 : 16:01:27
|
You should have told us from the beginning... E 12°55'05.25"N 56°04'39.16" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-17 : 17:35:05
|
quote: Originally posted by CanadaDBA Hi Nigel,SELECT A.F1, A.F2, B.Field1, B.Field2 FROM T1 A, T2 Bwhere B.field2 = (select max(B.field2) from t2 where B.field2 <= A.f2)returnes the following error:Msg 147, Level 16, State 2, Line 1An 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 subquerywhere 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 Bwhere 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. |
|
|
chandan_joshi80
Starting Member
30 Posts |
Posted - 2008-04-18 : 05:36:19
|
SELECT * FROM T3 WHERE FIELD1 IN (SELECT F2 FROM T2 )chandan Joshi |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-18 : 07:21:24
|
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. |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-18 : 09:38:37
|
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 @T1SELECT 'A', 'A1' UNION ALLSELECT 'A', 'A2' UNION ALLSELECT 'B', 'B0' UNION ALLSELECT 'B', 'B2' UNION ALLSELECT 'B', 'B3'DECLARE @T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))INSERT @T2SELECT 'A2', 'Fred' UNION ALLSELECT 'B0', 'Judy' UNION ALLSELECT 'B1', 'Max' UNION ALLSELECT 'B3', 'Joe'SELECT A.F1, A.F2, B.Field1, B.Field2 FROM @T1 A, @T2 Bwhere 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 |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-18 : 16:42:48
|
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 @T1SELECT 'DEAN', '2007-08-13 15:00:00', 12, -31.6 UNION ALLSELECT 'DEAN', '2007-08-13 21:00:00', 11.9, -33.1 UNION ALLSELECT 'DEAN', '2007-08-14 03:00:00', 12, -35 UNION ALLSELECT 'DEAN', '2007-08-14 09:00:00', 12, -36.8 UNION ALLSELECT 'DEAN', '2007-08-14 15:00:00', 11.7, -39.4 UNION ALLSELECT 'DEAN', '2007-08-14 21:00:00', 11.6, -41 UNION ALLSELECT 'DEAN', '2007-08-15 03:00:00', 12, -42.3 UNION ALLSELECT 'DEAN', '2007-08-15 09:00:00', 12.2, -44.2 UNION ALLSELECT 'DEAN', '2007-08-15 15:00:00', 12.4, -46 UNION ALLSELECT 'DEAN', '2007-08-15 21:00:00', 13.1, -47.9 UNION ALLSELECT 'DEAN', '2007-08-16 03:00:00', 13.1, -50.2 UNION ALLSELECT 'DEAN', '2007-08-16 06:00:00', 13.2, -51.3 UNION ALLSELECT 'DEAN', '2007-08-16 09:00:00', 13.4, -52.3 UNION ALLSELECT 'DEAN', '2007-08-16 12:00:00', 13.5, -53.3 UNION ALLSELECT 'DEAN', '2007-08-16 15:00:00', 13.7, -54.3 UNION ALLSELECT 'DEAN', '2007-08-16 18:00:00', 13.8, -55.5 UNION ALLSELECT 'DEAN', '2007-08-16 21:00:00', 14, -56.5 UNION ALLSELECT 'DEAN', '2007-08-17 00:00:00', 14, -57.8 UNION ALLSELECT 'DEAN', '2007-08-17 03:00:00', 14.1, -58.7 UNION ALLSELECT 'DEAN', '2007-08-17 06:00:00', 14.3, -59.8 UNION ALLSELECT 'DEAN', '2007-08-17 09:00:00', 14.3, -60.9 UNION ALLSELECT 'DEAN', '2007-08-17 12:00:00', 14.4, -61.7 UNION ALLSELECT 'DEAN', '2007-08-17 15:00:00', 14.6, -62.6 UNION ALLSELECT 'DEAN', '2007-08-17 17:00:00', 14.8, -63.6 UNION ALLSELECT 'DEAN', '2007-08-17 21:00:00', 15, -64.5 UNION ALLSELECT 'DEAN', '2007-08-18 00:00:00', 14.9, -65.1 UNION ALLSELECT 'DEAN', '2007-08-18 03:00:00', 14.9, -65.9 UNION ALLSELECT 'DEAN', '2007-08-18 06:00:00', 15, -66.7 UNION ALLSELECT 'DEAN', '2007-08-18 09:00:00', 15.1, -67.3 UNION ALLSELECT 'DEAN', '2007-08-18 12:00:00', 15.4, -67.9 UNION ALLSELECT 'DEAN', '2007-08-18 15:00:00', 15.7, -68.6 UNION ALLSELECT 'DEAN', '2007-08-18 18:00:00', 15.9, -69.4 UNION ALLSELECT 'DEAN', '2007-08-18 21:00:00', 16.1, -70.2 UNION ALLSELECT 'DEAN', '2007-08-19 00:00:00', 16, -71 UNION ALLSELECT 'DEAN', '2007-08-19 03:00:00', 16.2, -71.7 UNION ALLSELECT 'DEAN', '2007-08-19 06:00:00', 16.4, -72.6 UNION ALLSELECT 'DEAN', '2007-08-19 09:00:00', 16.6, -73.4 UNION ALLSELECT 'DEAN', '2007-08-19 12:00:00', 16.8, -74.3 UNION ALLSELECT 'DEAN', '2007-08-19 15:00:00', 17, -75.1 UNION ALLSELECT 'DEAN', '2007-08-19 18:00:00', 17.1, -76 UNION ALLSELECT '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 @T2SELECT 'HWRF', '2007-07-18 18:00:00', 15, -139.5 UNION ALLSELECT 'HWRF', '2007-07-19 00:00:00', 15.1, -140.7 UNION ALLSELECT 'HWRF', '2007-07-19 06:00:00', 15.4, -142.1 UNION ALLSELECT 'HWRF', '2007-07-19 12:00:00', 15.5, -143.5 UNION ALLSELECT 'HWRF', '2007-07-19 18:00:00', 15.7, -145 UNION ALLSELECT 'HWRF', '2007-07-20 00:00:00', 16.1, -146.7 UNION ALLSELECT 'HWRF', '2007-07-20 06:00:00', 16, -148.3 UNION ALLSELECT 'HWRF', '2007-07-23 00:00:00', 13.3, -107.2 UNION ALLSELECT 'HWRF', '2007-07-23 06:00:00', 13.6, -108 UNION ALLSELECT 'HWRF', '2007-07-23 12:00:00', 14.6, -108.5 UNION ALLSELECT 'HWRF', '2007-07-23 18:00:00', 15.2, -173.1 UNION ALLSELECT 'HWRF', '2007-07-23 18:00:00', 15.1, -108.9 UNION ALLSELECT 'HWRF', '2007-07-24 00:00:00', 15.8, -109.2 UNION ALLSELECT 'HWRF', '2007-07-24 06:00:00', 16.4, -109.6 UNION ALLSELECT 'HWRF', '2007-07-24 12:00:00', 16.6, -110.3 UNION ALLSELECT 'HWRF', '2007-07-24 18:00:00', 17.1, -110.5 UNION ALLSELECT 'HWRF', '2007-07-25 00:00:00', 18.1, -110.8 UNION ALLSELECT 'HWRF', '2007-07-25 06:00:00', 18.7, -111.2 UNION ALLSELECT 'HWRF', '2007-07-25 12:00:00', 19.3, -111.7 UNION ALLSELECT 'HWRF', '2007-07-25 18:00:00', 19.9, -112.1 UNION ALLSELECT 'HWRF', '2007-07-26 00:00:00', 20.4, -112.4 UNION ALLSELECT 'HWRF', '2007-07-26 06:00:00', 20.7, -112.8 UNION ALLSELECT 'HWRF', '2007-07-26 12:00:00', 21.5, -113.7 UNION ALLSELECT 'HWRF', '2007-07-26 18:00:00', 22.2, -114.6 UNION ALLSELECT 'HWRF', '2007-07-27 00:00:00', 22.5, -115.7 UNION ALLSELECT 'HWRF', '2007-07-27 06:00:00', 22.7, -116.7 UNION ALLSELECT 'HWRF', '2007-07-28 12:00:00', 25.3, -73.8 UNION ALLSELECT 'HWRF', '2007-07-28 18:00:00', 25.4, -73.6 UNION ALLSELECT 'HWRF', '2007-07-29 00:00:00', 27.2, -73.2 UNION ALLSELECT 'HWRF', '2007-07-29 00:00:00', 9.5, -112.4 UNION ALLSELECT 'HWRF', '2007-07-29 12:00:00', 9.8, -115.2 UNION ALLSELECT 'HWRF', '2007-07-29 18:00:00', 10.7, -115.7 UNION ALLSELECT 'HWRF', '2007-07-30 00:00:00', 11.1, -116.3 UNION ALLSELECT 'HWRF', '2007-07-30 00:00:00', 31.1, -70.3 UNION ALLSELECT 'HWRF', '2007-07-30 06:00:00', 11.8, -117.1 UNION ALLSELECT 'HWRF', '2007-07-30 12:00:00', 32.7, -67.7 UNION ALLSELECT 'HWRF', '2007-07-30 12:00:00', 9.7, -45.7 UNION ALLSELECT 'HWRF', '2007-07-30 12:00:00', 12, -118 UNION ALLSELECT 'HWRF', '2007-07-30 18:00:00', 9.8, -47.1 UNION ALLSELECT 'HWRF', '2007-07-30 18:00:00', 34.4, -67.5 UNION ALLSELECT 'HWRF', '2007-07-30 18:00:00', 12.2, -119 UNION ALLSELECT 'HWRF', '2007-07-31 00:00:00', 35.4, -66.7 UNION ALLSELECT 'HWRF', '2007-07-31 00:00:00', 10.2, -48.8 UNION ALLSELECT 'HWRF', '2007-07-31 06:00:00', 10.3, -50.1 UNION ALLSELECT 'HWRF', '2007-07-31 06:00:00', 37.1, -65.9 UNION ALLSELECT 'HWRF', '2007-07-31 12:00:00', 39.4, -63.7 UNION ALLSELECT 'HWRF', '2007-07-31 12:00:00', 10.8, -50.7 UNION ALLSELECT 'HWRF', '2007-07-31 18:00:00', 40.8, -62.3 UNION ALLSELECT 'HWRF', '2007-07-31 18:00:00', 13.1, -123.4 UNION ALLSELECT 'HWRF', '2007-07-31 18:00:00', 11.1, -52.2 UNION ALLSELECT 'HWRF', '2007-08-01 00:00:00', 13.1, -124 UNION ALLSELECT 'HWRF', '2007-08-01 00:00:00', 11.2, -53.4 UNION ALLSELECT 'HWRF', '2007-08-01 06:00:00', 13, -124.8 UNION ALLSELECT 'HWRF', '2007-08-01 06:00:00', 11.4, -54.8 UNION ALLSELECT 'HWRF', '2007-08-01 12:00:00', 11.7, -56.4 UNION ALLSELECT 'HWRF', '2007-08-01 12:00:00', 13.2, -125.4 UNION ALLSELECT 'HWRF', '2007-08-01 18:00:00', 11.9, -58.4 UNION ALLSELECT 'HWRF', '2007-08-01 18:00:00', 13.3, -126.6 UNION ALLSELECT 'HWRF', '2007-08-02 00:00:00', 13.3, -128 UNION ALLSELECT 'HWRF', '2007-08-02 00:00:00', 12.3, -60 UNION ALLSELECT 'HWRF', '2007-08-02 06:00:00', 13.3, -129.4 UNION ALLSELECT 'HWRF', '2007-08-02 06:00:00', 12.4, -61.4 UNION ALLSELECT 'HWRF', '2007-08-02 12:00:00', 13.4, -129.8 UNION ALLSELECT 'HWRF', '2007-08-02 18:00:00', 12.7, -67 UNION ALLSELECT 'HWRF', '2007-08-03 18:00:00', 16.7, -116.5 UNION ALLSELECT 'HWRF', '2007-08-04 00:00:00', 16.6, -117.7 UNION ALLSELECT 'HWRF', '2007-08-04 00:00:00', 13.9, -80.2 UNION ALLSELECT 'HWRF', '2007-08-04 12:00:00', 9.7, -108 UNION ALLSELECT 'HWRF', '2007-08-05 00:00:00', 10.8, -111.3 UNION ALLSELECT 'HWRF', '2007-08-06 00:00:00', 12.4, -115.9 UNION ALLSELECT 'HWRF', '2007-08-06 06:00:00', 12.6, -116.4 UNION ALLSELECT 'HWRF', '2007-08-06 12:00:00', 12.8, -117.3 UNION ALLSELECT 'HWRF', '2007-08-06 18:00:00', 13.4, -119.2 UNION ALLSELECT 'HWRF', '2007-08-07 00:00:00', 13.8, -119.8 UNION ALLSELECT 'HWRF', '2007-08-07 12:00:00', 14.4, -121.5 UNION ALLSELECT 'HWRF', '2007-08-13 00:00:00', 14, -146.3 UNION ALLSELECT 'HWRF', '2007-08-13 00:00:00', 12.7, -27.1 UNION ALLSELECT 'HWRF', '2007-08-13 06:00:00', 14.3, -147.5 UNION ALLSELECT 'HWRF', '2007-08-13 12:00:00', 12, -30.6 UNION ALLSELECT 'HWRF', '2007-08-13 12:00:00', 21.6, -85.8 UNION ALLSELECT 'HWRF', '2007-08-13 12:00:00', 14.7, -148.8 UNION ALLSELECT 'HWRF', '2007-08-13 18:00:00', 15.2, -150 UNION ALLSELECT 'HWRF', '2007-08-13 18:00:00', 11.9, -32.2 UNION ALLSELECT 'HWRF', '2007-08-13 18:00:00', 22, -86.9 UNION ALLSELECT 'HWRF', '2007-08-14 00:00:00', 12, -34.1 UNION ALLSELECT 'HWRF', '2007-08-14 00:00:00', 22, -87.7 UNION ALLSELECT 'HWRF', '2007-08-14 00:00:00', 15.7, -151.5 UNION ALLSELECT 'HWRF', '2007-08-14 06:00:00', 12, -35.9 UNION ALLSELECT 'HWRF', '2007-08-14 06:00:00', 22.2, -88.5 UNION ALLSELECT 'HWRF', '2007-08-14 06:00:00', 16.1, -152.6 UNION ALLSELECT 'HWRF', '2007-08-14 12:00:00', 11.7, -38.4 UNION ALLSELECT 'HWRF', '2007-08-14 12:00:00', 22.3, -89.7 UNION ALLSELECT 'HWRF', '2007-08-14 12:00:00', 16.5, -153.7 UNION ALLSELECT 'HWRF', '2007-08-14 18:00:00', 22.9, -90.2 UNION ALLSELECT 'HWRF', '2007-08-14 18:00:00', 17, -154.5 UNION ALLSELECT 'HWRF', '2007-08-14 18:00:00', 11.6, -40.1 UNION ALLSELECT 'HWRF', '2007-08-15 00:00:00', 17.4, -155.2 UNION ALLSELECT 'HWRF', '2007-08-15 00:00:00', 11.9, -41.4 UNION ALLSELECT 'HWRF', '2007-08-15 00:00:00', 23.3, -90.7 UNION ALLSELECT 'HWRF', '2007-08-15 06:00:00', 24.1, -91.4 UNION ALLSELECT 'HWRF', '2007-08-15 06:00:00', 17.7, -156 UNION ALLSELECT 'HWRF', '2007-08-15 06:00:00', 12.1, -43.3 UNION ALLSELECT 'HWRF', '2007-08-15 12:00:00', 24.5, -93.1 UNION ALLSELECT 'HWRF', '2007-08-15 12:00:00', 12.4, -45.1 UNION ALLSELECT 'HWRF', '2007-08-15 12:00:00', 17.3, -157.3 UNION ALLSELECT 'HWRF', '2007-08-15 18:00:00', 12.9, -47.1 UNION ALLSELECT 'HWRF', '2007-08-15 18:00:00', 25.9, -93.7 UNION ALLSELECT 'HWRF', '2007-08-15 18:00:00', 17.3, -158 UNION ALLSELECT 'HWRF', '2007-08-16 00:00:00', 13.1, -49.2 UNION ALLSELECT 'HWRF', '2007-08-16 00:00:00', 26.2, -95.1 UNION ALLSELECT 'HWRF', '2007-08-16 00:00:00', 17.4, -159 UNION ALLSELECT 'HWRF', '2007-08-16 06:00:00', 13.3, -51.4 UNION ALLSELECT 'HWRF', '2007-08-16 06:00:00', 26.7, -96.1 UNION ALLSELECT 'HWRF', '2007-08-16 12:00:00', 13.6, -53.6 UNION ALLSELECT 'HWRF', '2007-08-16 18:00:00', 13.7, -55.7 UNION ALLSELECT 'HWRF', '2007-08-17 00:00:00', 14, -57.9 UNION ALLSELECT 'HWRF', '2007-08-17 06:00:00', 14, -59.9 UNION ALLSELECT 'HWRF', '2007-08-17 12:00:00', 14.2, -61.7 UNION ALLSELECT 'HWRF', '2007-08-17 18:00:00', 14.7, -63.5 UNION ALLSELECT 'HWRF', '2007-08-18 00:00:00', 14.9, -65.1 UNION ALLSELECT 'HWRF', '2007-08-18 06:00:00', 15, -66.6 UNION ALLSELECT 'HWRF', '2007-08-18 12:00:00', 15.4, -67.9 UNION ALLSELECT 'HWRF', '2007-08-18 18:00:00', 15.8, -69.3 UNION ALLSELECT 'HWRF', '2007-08-19 00:00:00', 16.2, -70.8 UNION ALLSELECT 'HWRF', '2007-08-19 06:00:00', 16.4, -72.6 UNION ALLSELECT 'HWRF', '2007-08-19 12:00:00', 16.8, -74.3 UNION ALLSELECT 'HWRF', '2007-08-19 18:00:00', 17.1, -75.9 UNION ALLSELECT 'HWRF', '2007-08-20 00:00:00', 17.3, -77.6 UNION ALLSELECT 'HWRF', '2007-08-20 06:00:00', 17.5, -79.5 UNION ALLSELECT 'HWRF', '2007-08-20 12:00:00', 17.7, -81.4 UNION ALLSELECT 'HWRF', '2007-08-20 18:00:00', 18, -83.4SELECT 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 DLEFT 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.ObsCanada DBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-18 : 16:51:31
|
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" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-18 : 20:47:54
|
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 @T1SELECT 'A', 'A1' UNION ALLSELECT 'A', 'A2' UNION ALLSELECT 'B', 'B0' UNION ALLSELECT 'B', 'B2' UNION ALLSELECT 'B', 'B3'DECLARE @T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))INSERT @T2SELECT 'A2', 'Fred' UNION ALLSELECT 'B0', 'Judy' UNION ALLSELECT 'B1', 'Max' UNION ALLSELECT 'B3', 'Joe'SELECT A.F1, A.F2, B.Field1, B.Field2 FROM @T1 A, @T2 Bwhere 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.field1SELECT A.F1, A.F2, B.Field1, B.Field2 FROM @T1 A, @T2 Bwhere 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. |
|
|
CanadaDBA
583 Posts |
Posted - 2008-04-21 : 09:21:57
|
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 Bwhere 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 |
|
|
|
|
|