O solutie mai buna ar fi sa folosesti o functie care sa realizeze conversia valutara:
INSERT INTO CURSURI (DATA,EUR,USD,CHF,HUF,GBP) VALUES ('2007-01-05',2,22,222,2222,22222)
INSERT INTO CURSURI (DATA,EUR,USD,CHF,HUF,GBP) VALUES ('2007-01-06',3,33,333,3333,33333)
--simulare o zi de weekend si lipsa anumite cursuri
INSERT INTO CURSURI (DATA,EUR,USD,CHF,HUF,GBP) VALUES ('2007-01-08',4,44,NULL,NULL,NULL)
INSERT INTO CURSURI (DATA,EUR,USD,CHF,HUF,GBP) VALUES ('2007-01-09',5,NULL,555,5555,55555)
GO
CREATE FUNCTION Conversie_Valutara(
@dt DATETIME,
@vl CHAR(3), @sm MONEY )
RETURNS MONEY
BEGIN
DECLARE
@sm_valuta MONEY,
@sm_conversie MONEY
SET
@sm_valuta = (SELECT CASE @vl
WHEN 'EUR' THEN EUR
WHEN 'USD' THEN USD
WHEN 'CHF' THEN CHF
WHEN 'HUF' THEN HUF
WHEN 'GBP' THEN GBP
END
FROM CURSURI
WHERE DATA = (SELECT MAX(DATA)
FROM CURSURI
WHERE DATA <= @dt
AND CASE @vl
WHEN 'EUR' THEN EUR
WHEN 'USD' THEN USD
WHEN 'CHF' THEN CHF
WHEN 'HUF' THEN HUF
WHEN 'GBP' THEN GBP
END IS NOT NULL
AND CASE @vl
WHEN 'EUR' THEN EUR
WHEN 'USD' THEN USD
WHEN 'CHF' THEN CHF
WHEN 'HUF' THEN HUF
WHEN 'GBP' THEN GBP
END > 0))
SET
@sm_conversie = ISNULL(@sm_valuta,0)*@sm
RETURN
@sm_conversie
END
GO
SELECT dbo.Conversie_Valutara('2007-01-05','USD',1000) AS Suma
SELECT dbo.Conversie_Valutara('2007-01-06','USD',1000) AS Suma
SELECT dbo.Conversie_Valutara('2007-01-07','USD',1000) AS Suma
SELECT dbo.Conversie_Valutara('2007-01-08','USD',1000) AS Suma
SELECT dbo.Conversie_Valutara('2007-01-09','USD',1000) AS Suma