15,995,758 members
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
[no name] 29-Mar-12 7:20am
So what have you tried?

## 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?

v2
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?

## Solution 2

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