Virendra S from Bangalore, Karnataka Ответов: 1

Как я могу реализовать эту логику в SQL server

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.



Как я могу этого достичь?

Что я уже пробовал:

Можно ли добиться этого с помощью необходимых соединений или курсоров?

1 Ответов

Рейтинг:
8

CHill60

Вы должны избегать использования курсора для этого - помните, что SQL Server установлен на основе.

Ваши инструкции на самом деле не имеют большого смысла - например, вы не будете использовать значения обмена валюты только на отрицательные значения. Я думаю, что вы имеете в виду, что вам нужны суммы в долларах США.

Это также немного попахивает домашним заданием, поэтому то, что следует ниже, не является полным решением - кроме того, мой первоначальный комментарий выше применим.

Я использовал следующие тестовые данные 

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)
Обратите внимание, что я действительно не мог установить, что вы имели в виду в своих комментариях о дубликатах, поэтому я не обслуживал их явно.

Этот фрагмент 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
Дает следующие результаты 
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
Примечание 1 - функция ABS() даст абсолютное значение столбца - эффективно изменяя отрицательное значение на положительное
Примечание 2 - Пример использования соединения для получения биржевой стоимости.

Проблема теперь в том, что у нас есть две строки, но мы хотим получить данные из обеих... лучший способ справиться с этим-использовать PIVOT, например 
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

Примечание 3 - Обратите внимание, что валюта теперь является именем столбца
Вот и получается результат 
5257	NULL	NULL	NULL	9450.00	9450.00	NULL
5257	INR	USD	0.2666	35212.32	NULL	-35212.32
так что я, очевидно, не совсем правильно понял ... Мне нужна одна строка на счет, поэтому я буду использовать GROUP BY
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
Что и дает результаты 
InvoiceNo	FromCurrency	ToCurrency	ExchangeValue	USD	INR
5257	INR	USD	0.2666	9450.00	-35212.32
Теперь, когда у вас есть все результаты в одной строке, вы можете добавить их во временную таблицу или использовать общее табличное выражение или даже подключить свое простое уравнение.

Virendra S from Bangalore, Karnataka

Привет CHill60, спасибо за ваш ответ.извините за ломаный английский и меньшую ясность в требованиях. У меня есть проблема здесь @Note2 point, таблица Exchange value имеет количество записей, поэтому это соединение возвращает много записей.

Примеры записей таблицы обмена:

 FromCurrency	ToCurrency	exchangevalue
INR 	ADP 	 2.094240000 
INR 	AED 	 0.051212000 
INR 	ARS 	 0.764080000 
INR 	AUD 	 0.020575000 
INR 	BDT 	 1.178190000

CHill60

Вы можете расширить это предложение, чтобы ограничить результаты только INR -> USD. например 

ON I.currency = C.FromCurrency AND C.ToCurrency = 'USD'

Если это что-то более сложное, чем это, то вам нужно будет предоставить еще 2 или 3 примера