· 9 years ago · Nov 21, 2016, 07:00 AM
1use master
2GO
3
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[ AMitrofanova]'
8)
9ALTER DATABASE AMitrofanova set single_user with rollback immediate
10GO
11
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'AMitrofanova'
16)
17DROP DATABASE AMitrofanova
18GO
19
20IF OBJECT_ID('AMitrofanova') IS NOT NULL
21DROP DATABASE AMitrofanova
22GO
23
24CREATE DATABASE AMitrofanova
25GO
26
27USE AMitrofanova
28GO
29
30
31
32CREATE TABLE Rates (
33 DataRate datetime NULL ,
34 Rate float NULL ,
35 FromValuta nvarchar(3) NULL ,
36 ToValuta nvarchar(3) NULL
37)
38GO
39
40CREATE TABLE MyMoney
41(
42Valuta nvarchar(3) not null,
43SumMoney money not null,
44--CONSTRAINT Id FOREIGN KEY (IdVal) REFERENCES CValue(IdFrom)
45)
46GO
47
48CREATE FUNCTION ConvertValuta(@Сумма as float, @Валюта as nvarchar(3), @КВалюте as nvarchar(3), @Дата as datetime)
49RETURNS float AS
50BEGIN
51 DECLARE @ReturnValue float
52 IF @Валюта=@КВалюте
53 SET @ReturnValue=@Сумма
54 ELSE IF @Сумма IS NULL
55 SET @ReturnValue=NULL
56 ELSE
57 BEGIN
58 SET @ReturnValue=@Сумма / (SELECT TOP 1 Rate FROM Rates WHERE (DataRate<=@Дата) AND (FromValuta=@КВалюте) AND (ToValuta=@Валюта)
59 ORDER BY DataRate DESC)
60 IF @ReturnValue IS NULL
61 SET @ReturnValue=@Сумма * (SELECT TOP 1 Rate FROM Rates WHERE (DataRate<=@Дата) AND (FromValuta=@Валюта) AND (ToValuta=@КВалюте)
62 ORDER BY DataRate DESC)
63 END
64 RETURN @ReturnValue
65END
66go
67
68
69
70INSERT INTO Rates
71VALUES
72(N'20161020 10:10:12.000', 1, N'USD', N'USD')
73,(N'20161020 10:10:12.000', 0.623, N'USD', N'GBP')
74,(N'20161020 10:10:12.000', 78.065, N'USD', N'JPY')
75,(N'20161020 10:10:12.000', 0.9011, N'USD', N'USD')
76GO
77
78INSERT INTO MyMoney
79VALUES
80(N'GBP', 30.2)
81,(N'CHF', 14)
82,(N'JPY', 10)
83GO
84
85
86
87set res = ConvertValuta(300.3, N'GBP', N'USD', N'20161020 10:10:12.000')
88select res
89--SELECT SUM(ConvertValuta(Сумма, Валюта, Дата, 'RUR')) [itog] FROM Rates