how to write query for below output

Question

0.00/5 (No votes)

See more:

We have a table table1 which has columns ordereddate, amount and currency and the table looks like this

Ordereddate amount currency
2012-02-07 2.6 usd
2012-02-07 1.3 gbp
2012-02-09 10.08 usd
2012-02-09 15 gbp

Our query is how to write sql query which gives following output and that should be ordered by ordereddate and the amount should be converted into any one currency(say gbp from usd) and you can take this formula for converting usd to gbp

1.00 USD = 0.629876 GBP

Output needed
----------------

Ordereddate TotalAmount currency
2012-02-07 2.9376776 gbp
2012-02-09 21.34915 gbp

so please anybody help us out

Posted 29-Mar-12 1:10am

sangamesh arali

Add a Solution

Comments

[no name] 29-Mar-12 7:20am

So what have you tried?

2 solutions

Solution 2

SQL

SELECT orderdate,CASE WHEN max(currency)='usd' THEN
SUM(amount*0.629876)
else sum(amount)
END
from currencyDet group by OrderDate

Posted 29-Mar-12 2:37am

satheeshkanyakumari

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)

Saral S Stalin · Accepted Answer · 2012-03-29T02:10:00

Solution 1

Here you go

SQL

--temp table
DECLARE @Orders TABLE (OrderedDate DATETIME, Amount FLOAT, Currency VARCHAR(50))
INSERT INTO @Orders  VALUES ('2012-02-07', 2.6, 'USD')
INSERT INTO @Orders VALUES ('2012-02-07', 1.3, 'GBP' )
INSERT INTO @Orders VALUES ('2012-02-09', 10.08 , 'USD')
INSERT INTO @Orders VALUES ('2012-02-09', 15, 'GBP')

SELECT OrderedDate, SUM(Amount), 'GBP' Unit
FROM
--Create a derived table with single currency unit
(SELECT OrderedDate, CASE WHEN Currency = 'USD' THEN Amount * 0.629846 ELSE Amount END Amount
FROM @Orders) As New
GROUP BY OrderedDate

I have one question though, did you try out something or posting it directly in forums so that someone else will do the work for you?

Posted 29-Mar-12 2:10am

Saral S Stalin

Updated 29-Mar-12 2:11am

v2

Comments

Nelek 29-Mar-12 8:16am

Probably the second

sangamesh arali 29-Mar-12 8:27am

Thank you stalin, it helped a lot. Actually i tried using cursor but i dint get it.

Saral S Stalin 29-Mar-12 8:30am

Happy to help..Just mention what you tried in the posting.. meanwhile did you downvote my answer?

sangamesh arali 29-Mar-12 9:20am

i tried to write query in cursor
DECLARE @Orderdate datetime;
DECLARE A_sum float;
DECLARE order_cursor CURSOR FOR
SELECT OrderDate,amount,currency FROM table1 GROUP BY OrderDate
SET A_sum = 0;
OPEN order_cursor
FETCH NEXT FROM db_cursor INTO @Orderdate
WHILE @@FETCH_STATUS = 0
BEGIN
if(

i stopped at if condition and i dint get any idea so i posted it. Actually i have not seen this type of query (what Stalin posted the answer) in my career so Specially thankful to Stalin.

What Stalin posted that is working fine

Saral S Stalin 29-Mar-12 8:28am

@Nelek what did you mean?

Nelek 29-Mar-12 9:19am

Your question had two parts, I think the second one was the answer.