Sql server currency code if invoice has one more than one currency

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Below is my requirement

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

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

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

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

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

Posted 28-Jun-18 1:48am

Prathap Gangireddy

Updated 28-Jun-18 3:49am

CHill60

v2

Add a Solution

Comments

Maciej Los 28-Jun-18 9:44am

Why do you need such of functionality? It makes no sense!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2018-06-28T03:50:00

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

SQL

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

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

SQL

;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

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