July 5, 2016 at 3:32 pm
I have two tables
Table 1
ProductCurrencyCost
AAUD100
BCAD200
CEUR300
DGBP400
EINR500
FPLN600
GUSD700
HAUD800
Table 2
CurrencyConversionRate
AUD1.46
CAD1.38
EUR1
GBP0.84
INR71.37
PLN4.16
USD1.11
I want a final table
FINAL
ProductCurrencyCostActual Cost
AAUD10068.49315068
BCAD200144.9275362
CEUR300300
DGBP400476.1904762
EINR5007.005744711
FPLN600144.2307692
GUSD700630.6306306
HAUD800547.9452055
which shows the actual cost based on the conversion rate
Actual Cost= Cost/ConversionRate
What code should i write in Microsoft SQL Server Management Studio
July 5, 2016 at 3:46 pm
what have you tried so far?
hint> create a join between your tables based on "currency"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 3:55 pm
I have tried this-
select
Table 1.Product,
Table 1.Currency,
Table 1.Cost,
Case
when Table 2.Currency in ('AUD','CAD','EUR','GBP','INR','PLN','USD')
then (Table 1.Cost/Table 2.ConversionRate) End as Actual Cost
From Table 1, Table 2
but it is giving me 56 records instead of 8.
It is doing the calculation for Actual Cost for each conversion rate
July 5, 2016 at 3:58 pm
amanspschauhan (7/5/2016)
I have tried this-select
Table 1.Product,
Table 1.Currency,
Table 1.Cost,
Case
when Table 2.Currency in ('AUD','CAD','EUR','GBP','INR','PLN','USD')
then (Table 1.Cost/Table 2.ConversionRate) End as Actual Cost
From Table 1, Table 2
but it is giving me 56 records instead of 8.
It is doing the calculation for Actual Cost for each conversion rate
sorry to have to ask...but is this homework?
as for your code...where is the "join" betwen the tables?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 4:14 pm
does this help you get going?
CREATE TABLE tab1(
Product VARCHAR(1) NOT NULL
,Currency VARCHAR(3) NOT NULL
,Cost INTEGER NOT NULL
);
INSERT INTO tab1(Product,Currency,Cost) VALUES
('A','AUD',100),('B','CAD',200),('C','EUR',300),('D','GBP',400)
,('E','INR',500),('F','PLN',600),('G','USD',700),('H','AUD',800);
CREATE TABLE tab2(
Currency VARCHAR(3) NOT NULL PRIMARY KEY
,ConversionRate NUMERIC(5,2) NOT NULL
);
INSERT INTO tab2(Currency,ConversionRate) VALUES
('AUD',1.46),('CAD',1.38),('EUR',1),('GBP',0.84)
,('INR',71.37),('PLN',4.16),('USD',1.11);
SELECT tab1.Product,
tab1.Currency,
tab1.Cost,
tab2.ConversionRate
FROM tab1
INNER JOIN tab2 ON tab1.Currency = tab2.Currency;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 4:16 pm
Silly Mistake!
Its working now thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply