Click here to Skip to main content
15,889,899 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Below are the 3 tables:
1 INVOICE_DUMP -- Columns-> InvoiceNo, Currency, Amount
2 CURRENCY_EXHANGE_VALUE -- Columns-> FromCurrency, ToCurrency, ExchangeValue
3 Target_table

sample records:
INVOICE_DUMP:
InvoiceNo	Currency	Amount
5257	USD	9450.00
5257	INR	-35212.32

CURRENCY_EXHANGE_VALUE:
FromCurrency	ToCurrency	ExchangeValue
INR 	USD 	 0.2666 


INVOICE_DUMP has some duplicate records (Column - InvoiceNo), in this scenario duplcaited invoiceNo may have different currency value in column Currency 
and negative amount in column Amount.

need to update the Amount value in table INVOICE_DUMP based on below logic.
-ve amount line item currency should be changed to +ve amount line item currency 
and Amount also need to be updated using CURRENCY_EXHANGE_VALUE table as below
 Amount (-ve value record) * ExchangeValue (from table CURRENCY_EXHANGE_VALUE ) + Amount (+ve value record)
in this case -- select (-35212.32 * 0.2666)+9450.00  Ans = 62.395488

this updated Amount and currency need to inserted into target_table.



How can i achieve this?

What I have tried:

Is it possible to achieve using joins or cursors required?
Posted
Updated 18-Sep-19 4:21am

1 solution

You should avoid using a cursor for this - remember SQL Server is set based.

Your instructions don't really make much sense - for example you would not use currency exchange values just on negative values. I think what you mean is that you want the amounts in USD.

This also smacks a little of homework so what follows is not a complete solution - besides my initial comment above applies.

I used the following test data
SQL
declare @invoice_dump table (InvoiceNo int, currency varchar(4), Amount decimal(15,2))
declare @currency_exchange_value table (FromCurrency varchar(4), ToCurrency varchar(4), ExchangeValue decimal(15,4))
insert into @invoice_dump (InvoiceNo, currency, Amount) values
(5257,'USD',9450.00),(5257,'INR',-35212.32)
insert into @currency_exchange_value (FromCurrency,ToCurrency,ExchangeValue) values
('INR','USD',0.2666)
Note I couldn't really establish what you meant in your comments about duplicates so I have not catered for them explicitly.

This sql snippet
SQL
SELECT *, ABS(Amount) as posValue	-- Note 1
FROM @invoice_dump I
LEFT OUTER JOIN @currency_exchange_value C ON I.currency = C.FromCurrency	-- Note 2
Gives these results
InvoiceNo	currency	Amount	FromCurrency	ToCurrency	ExchangeValue	posValue
5257	USD	9450.00	NULL	NULL	NULL	9450.00
5257	INR	-35212.32	INR	USD	0.2666	35212.32
Note 1 - The ABS() function will give the absolute value of the column - effectively changing a negative value into a positive one
Note 2 - Example of using a join to get the exchange value.

The problem now is that we have two rows but we want the data from both... the best way to handle that is to use PIVOT e.g.
SQL
SELECT * FROM
(SELECT *, ABS(Amount) as posValue	
FROM @invoice_dump I
LEFT OUTER JOIN @currency_exchange_value C ON I.currency = C.FromCurrency
) src
PIVOT
(
	SUM(Amount) FOR currency IN (USD, INR) -- Note 3
)pvt

Note 3 - Notice that the currency is now a column name
That gives the result
5257	NULL	NULL	NULL	9450.00	9450.00	NULL
5257	INR	USD	0.2666	35212.32	NULL	-35212.32
so I obviously haven't got that quite right ... I need a single row per invoice so I am going to use GROUP BY
SQL
SELECT InvoiceNo, MAX(FromCurrency) as FromCurrency, MAX(ToCurrency) as ToCurrency, 
		MAX(ExchangeValue) as ExchangeValue, MAX(USD) as USD, MAX(INR) as INR 
FROM
(SELECT *, ABS(Amount) as posValue	
FROM @invoice_dump I
LEFT OUTER JOIN @currency_exchange_value C ON I.currency = C.FromCurrency
) src
PIVOT
(
	SUM(Amount) FOR currency IN (USD, INR)
)pvt
GROUP BY InvoiceN
Which gives the results
SQL
InvoiceNo	FromCurrency	ToCurrency	ExchangeValue	USD	INR
5257	INR	USD	0.2666	9450.00	-35212.32
Now that you have all the results in a single row you could add those to a temporary table or use a Common Table Expression or even plug in your simple equation.
 
Share this answer
 
Comments
Virendra S from Bangalore, Karnataka 18-Sep-19 11:23am    
Hi CHill60, thanks for your answer.sorry for the broken English and less clarity on requirement. I have concern here @Note2 point, Exchange value table has number of records, so this join returns many records.

Exchange table sample records:
 FromCurrency	ToCurrency	exchangevalue
INR 	ADP 	 2.094240000 
INR 	AED 	 0.051212000 
INR 	ARS 	 0.764080000 
INR 	AUD 	 0.020575000 
INR 	BDT 	 1.178190000 
CHill60 18-Sep-19 13:05pm    
You could extend the clause to limit the results to INR -> USD only. e.g.
ON I.currency = C.FromCurrency AND C.ToCurrency = 'USD'

If it's something more complex than that then you will need to supply 2 or 3 more examples

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900