Storing variable updating table

Transact-SQL
1

hi,

i have been asked to do the following

…...store CurrencyCodePurchased & CurrencyCodeSold as temp variables
…..perform a lookup of the currency in SecurityCaption11 to the XrateNotional file Base field (new)
…..starting with the first currency code in that file, find the first code that is either CurrencyCodePurchased or CurrencyCodeSold
…..if the match is CurrencyCodePurchased, take the rate field and multiply this by the BaseCurrencyPurchased (in 167) amount and populate to NotionalAmount
…..if the match is CurrencyCodeSold, take the rate field and multiply this by the BaseCurrencySold amount and populate to NotionalAmount

first part i need to get which i dont know how to is the varibale part

Currency CurrencyCodePurchased CurrencyCodeSold
XPD US$ JPY
XAU US$ JPY
XPT JPY £
XAG JPY CHF
KWD
BHD
OMR
LVL
JOD
GBP
KYD
EUR
CHF
JPY

so i need to loop through the currency column in first table and then read the value from that table to the CurrencyCodePurchased then the CurrencyCodesold column until i find a match. i need to do it row by row so that both the CurrencyCodePurchased then the CurrencyCodesold columns are check by the value from the currency column until a match is found.

is this possible and how do i go about doing it

2

You don't need to loop through the data, probably get it done with a single query. If you provide DDL, Sample Data and expected results, then we can help you

3

Could you please provide us direct access to your database?

4

Hi

I cant give access to the db. here is some samle data

table 1

ID Base Currency Rate
1 EUR XPD 1359.826133
2 EUR XAU 1239.502107
3 EUR CHF 0.90059712
4 EUR SEK 0.094661047
5 EUR JPY 0.008166491

table 2

SecurityCaptionDescription11 CurrencyCodePurchased CurrencyCodeSold
EUR CHF JPY
EUR JPY SEK

so for the first one in table 2 i expect it to use the chf from CurrencyCodePurchased as that is first in list from table 1 and use the chf figure from table 1

the second one i expect it tp pick SEK FROM CurrencyCodeSold| as that before jpy in table 1 and use the sek figure from table 1

5

Please provide the sample data as follows.
Create table #sample(id int, base char(5), rate decimal(4,11))

Insert into #sample

6

/****** Object: Table [dbo].[BNYUKWorkingDataloadFile2] Script Date: 3/30/2020 11:23:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BNYUKWorkingDataloadFile2](
[BaseCurrencyBought] [decimal](28, 6) NULL,
[BaseCurrencySold] [decimal](28, 6) NULL,
[SecurityCaptionDescription11] nvarchar NULL,
[CurrencyCodePurchased] nvarchar NULL,
[CurrencyCodeSold]nvarchar NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [dbo].[BNYUKWorkingDataloadFile2]
(
[BaseCurrencyBought]
,[BaseCurrencySold]
,[SecurityCaptionDescription11]
,[CurrencyCodePurchased]
,[CurrencyCodeSold]
)
VALUES
('1.0',
'2.0',
'EUR',
'CHF',
'JPY'),

	   ('1.0',
	   '2.0',
	   'EUR',
	   'JPY',
	   'SEK')

GO

/****** Object: Table [dbo].[BNYIREXRateNotionalTable] Script Date: 3/30/2020 11:10:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BNYIREXRateNotionalTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[Base] nvarchar NULL,
[Rate] [decimal](28, 9) NULL,
[Currency] nvarchar NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[BNYIREXRateNotionalTable]
([Base]
,[Rate]
,[Currency])
VALUES
('EUR',
'1359.826133',
'XPD'),

		('EUR',
	   '1239.502107',
	   'XAU'),

	   ('EUR',
	   '0.90059712',
	   'CHF'),

	   ('EUR',
	   '0.094661047',
	   'SEK'),

	   ('EUR',
	   '0.008166491',
	   'JPY')

GO

7 
CREATE TABLE [#BNYUKWorkingDataloadFile2](
[BaseCurrencyBought] [decimal](28, 6) NULL,
[BaseCurrencySold] [decimal](28, 6) NULL,
[SecurityCaptionDescription11] nvarchar(10) NULL,
[CurrencyCodePurchased] nvarchar(10) NULL,
[CurrencyCodeSold]nvarchar(10) NULL

) ON [PRIMARY] 
GO

INSERT INTO [#BNYUKWorkingDataloadFile2]
(
[BaseCurrencyBought]
,[BaseCurrencySold]
,[SecurityCaptionDescription11]
,[CurrencyCodePurchased]
,[CurrencyCodeSold]
)
VALUES
('1.0',
'2.0',
'EUR',
'CHF',
'JPY'),

	   ('1.0',
	   '2.0',
	   'EUR',
	   'JPY',
	   'SEK')


CREATE TABLE [#BNYIREXRateNotionalTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[Base] nvarchar(10) NULL,
[Rate] [decimal](28, 9) NULL,
[Currency] nvarchar (10) NULL
) ON [PRIMARY]
GO

INSERT INTO [#BNYIREXRateNotionalTable]
([Base]
,[Rate]
,[Currency])
VALUES
('EUR',
'1359.826133',
'XPD'),

		('EUR',
	   '1239.502107',
	   'XAU'),

	   ('EUR',
	   '0.90059712',
	   'CHF'),

	   ('EUR',
	   '0.094661047',
	   'SEK'),

	   ('EUR',
	   '0.008166491',
	   'JPY')

Select f.[BaseCurrencyBought]
	  ,f.[BaseCurrencySold]
	  ,f.[SecurityCaptionDescription11]
	  ,f.[CurrencyCodePurchased]
	  ,f.[CurrencyCodeSold] 
	  , case when cp.Id <= cs.ID then 
				cp.Currency
		else	
				cs.Currency
		end as Currency			
	  , case when cp.Id <= cs.ID then 
				cp.Rate
		else	
				cs.Rate
		end as Rate			
  from [#BNYUKWorkingDataloadFile2] f
	join [#BNYIREXRateNotionalTable] cp
		on f.CurrencyCodePurchased = cp.Currency
		and f.SecurityCaptionDescription11 = cp.Base
	join [#BNYIREXRateNotionalTable] cs
		on f.CurrencyCodeSold = cs.Currency
		and f.SecurityCaptionDescription11 = cs.Base
8

Thats perfect. Thanks so much.
Just one last thing. If i wanted to use that select and update a column in the BNYUKWorkingDataloadFile2 with the rate value. what would be the best way to do it

9

I add a new column and then updated

alter table #BNYUKWorkingDataloadFile2 add NewRate [decimal](28, 9)
go

update f
	set NewRate =  case when cp.Id <= cs.ID then 
				cp.Rate
		else	
				cs.Rate
		end 
  from [#BNYUKWorkingDataloadFile2] f
	join [#BNYIREXRateNotionalTable] cp
		on f.CurrencyCodePurchased = cp.Currency
		and f.SecurityCaptionDescription11 = cp.Base
	join [#BNYIREXRateNotionalTable] cs
		on f.CurrencyCodeSold = cs.Currency
		and f.SecurityCaptionDescription11 = cs.Base 

Select top 100  * from #BNYUKWorkingDataloadFile2
1 Like
10

that work. Again thanks very much for the help.