如果发票有一种以上的货币，则为Sql server货币代码 [英] Sql server currency code if invoice has one more than one currency

问题描述

以下是我的要求

Below is my requirement

Table1 - QuotationMaster - QuoteID,CustomerID,Date,InvoiceNo
Table2 - QuoteItems - QuoteID,ItemID,CurrencyID,Amount
Table3 - CurrencyMaster - CurrencyID,CurrencyCode

示例 -



如果我搜索发票声明对于特定日期，最终结果必须是



日期，客户名称，货币代码，金额



特定发票如果它有2个具有相同CurrencyID的项目，则结果应显示如下

Example -

If i search for the invoice statement for a particular date,final result must be

Date,CustomerName,CurrencyCode,Amount

For a particular invoice if it has 2 items which has same CurrencyID then result should be displayed as follows

Item1 - Amount (2.00) 
Item2 - Amount (3.00)

Date   InvoiceNo  CustomerName    CurrencyCode   Amount
June    INV123     TESTING          INR           5.00

对于特定发票，如果它有2个具有不同CurrencyID的项目，则结果应显示为

For a particular invoice if it has 2 items which has different CurrencyID's then result should be displayed as

followDate   InvoiceNo  CustomerName    CurrencyCode   Amount
June    INV123     TESTING          0          0.00

如果发票有两个货币不同的物品，那么货币代码和金额必须为0.



我无法得到一个清晰的图片如何解决这个问题，因为我最终得到了groupby currencyid并获得了两次打印相同的结果

To make it simple if a invoice has two items with different currencies then the currency code and Amount must be 0.

I'm not able to get a clear picture how to solve this as I end up with groupby currencyid and get the same result printed twice

Date   InvoiceNo  CustomerName    CurrencyCode   Amount
June    INV123     TESTING          INR          2.00
June    INV123     TESTING          GBP          3.00

你能帮我解决这个问题



我尝试了什么：



我无法清楚地了解如何解决这个问题，因为我最终得到了groupby currencyid并获得了两次打印相同的结果

Can you please help me with the approach

What I have tried:

I'm not able to get a clear picture how to solve this as I end up with groupby currencyid and get the same result printed twice

推荐答案

我同意Maciej认为这没有多大意义。在现实世界中，您需要处理多种货币发票（并获得资金！）或者首先不允许多种货币方案。



但是，您可以使用以下

I agree with Maciej that this doesn't make much sense. In the real world you either need to deal with multi-currency invoices (and get the money!) OR not allow the multi-currency scenario in the first place.

However, you can get a count of the number of currencies used on each invoice with the following

select InvoiceNo, count(distinct currencyID) as CurrCount
FROM  QuotationMaster A 
INNER JOIN QuoteItems B ON A.QuoteID = B.QuoteID GROUP BY InvoiceNo

你可以使用它CTE中的片段以提供给您的主要查询，例如

You could use that snippet in a CTE to feed into your main query e.g.

;with NoCurrs as
(
	-- count the number of currencies used on each Invoice
	select InvoiceNo, count(distinct currencyID) as CurrCount
	FROM  QuotationMaster A 
	INNER JOIN QuoteItems B ON A.QuoteID = B.QuoteID GROUP BY InvoiceNo
)
SELECT A.QuoteID, datename(m,[Date]), A.InvoiceNo, CustomerId, 
Currency = CASE WHEN N.CurrCount = 1 THEN MAX(CurrencyCode) ELSE '' END,
Amount = CASE WHEN N.CurrCount = 1 THEN SUM(Amount) ELSE 0.00 END
FROM NoCurrs N 
INNER JOIN QuotationMaster A ON A.InvoiceNo = N.InvoiceNo
INNER JOIN QuoteItems B ON A.QuoteID = B.QuoteID
INNER JOIN CurrencyMaster C ON B.CurrencyID = C.CurrencyID
GROUP BY A.QuoteID, datename(m,[Date]), A.InvoiceNo, CustomerId,CurrCount

注意事项：

- 使用CASE来确定我是否需要货币和金额

- 我用过MAX （CurrencyCode）只是处理分组依据场景（即我需要某种功能）

- 我还没有加入其他表来获取客户名称等等 - 这是你可以添加的。

- 你可以改为使用子查询，我碰巧喜欢CTE的

Points to note:
- Spot the use of CASE to determine whether or not I need the currency and amount
- I've used MAX(CurrencyCode) just to handle the "group by" scenario (i.e. I needed some sort of function in there)
- I haven't joined to other tables to get things like Customer Name - that's for you to add.
- You could use a sub-query instead, I just happen to like CTE's

这篇关于如果发票有一种以上的货币，则为Sql server货币代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！