SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE CURRENCY ( CURR varchar(100) NULL, [DATE] date NULL, RATE float NULL ); CREATE TABLE TRANSACTIONS ( TR_DATE date NULL, CURR varchar(100) NULL, AMOUNT float NULL ); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-01', N'AUD', 100.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-09', N'USD', 300.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-17', N'AUD', 400.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-17', N'USD', 500.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-21', N'AUD', 600.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-01-25', N'USD', 800.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-03', N'USD', 900.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-08', N'AUD', 200.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-13', N'USD', 300.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-18', N'USD', 500.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-21', N'AUD', 600.0); INSERT INTO TRANSACTIONS (TR_DATE, CURR, AMOUNT) VALUES('2011-02-05', N'AUD', 900.0); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'USD', '2011-01-01', 1.2651); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'USD', '2011-01-15', 1.2611); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'USD', '2011-01-29', 1.2605); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'USD', '2011-02-12', 1.2581); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'USD', '2011-02-26', 1.2603); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'AUD', '2011-01-01', 1.3144); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'AUD', '2011-01-15', 1.3133); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'AUD', '2011-01-29', 1.3188); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'AUD', '2011-02-12', 1.3164); INSERT INTO CURRENCY (CURR, [DATE], RATE) VALUES(N'AUD', '2011-02-26', 1.3195); SELECT T.*, C.RATE FROM TRANSACTIONS T LEFT JOIN ( SELECT T1.CURR, T1.[DATE] as MINDATE, T2.[DATE] as MAXDATE, T1.RATE FROM ( SELECT CURR, [DATE], RATE, ROW_NUMBER() over (order by CURR, [DATE]) as NUM FROM CURRENCY T1 ) T1 LEFT JOIN ( SELECT CURR, [DATE], RATE, ROW_NUMBER() over (order by CURR, [DATE]) as NUM FROM CURRENCY ) T2 on T1.NUM + 1 = T2.NUM ) C ON T.CURR = C.CURR AND T.TR_DATE >= C.MINDATE AND T.TR_DATE < C.MAXDATE;