Click here to Skip to main content
15,666,844 members
Please Sign up or sign in to vote.
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
[no name] 29-Mar-12 7:20am    
So what have you tried?

Here you go

--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
--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?
Share this answer
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;
SELECT OrderDate,amount,currency FROM table1 GROUP BY OrderDate
SET A_sum = 0;
OPEN order_cursor
FETCH NEXT FROM db_cursor INTO @Orderdate

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?
SELECT orderdate,CASE WHEN max(currency)='usd' THEN
else sum(amount)
from currencyDet group by OrderDate
Share this answer

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