sql division same result on every row

sql division same result on every row

I have a problem with my sql query i got the following data and want to
divide the first column with the second one, these data is calculated in
an earlier query and then inserted to a temp table like this with the
following data:
table #temp:
--------------------------------------------------
| col1 | col2
--------------------------------------------------
| 0.00039572307609997499 |0.00036532140661635839
--------------------------------------------------
| 0.00041192187019791974 | 0.00038027571836302763
--------------------------------------------------
| 0.01844205591109206314 | 0.01702523358692274461
--------------------------------------------------
select valuefirstdate/valueseconddate from #temp
gives the first rows result on every row:
-----------
| col1 |
-----------
| 1.083218|
-----------
| 1.083218|
-----------
| 1.083218|
Sorry for that mistake :D I still had a problem with this code below the
"RESULT" column always gave 1.0000 as result.
ValueFirstDate ValueSecondDate RESULT
43.02206800000000000000 | 56.14522500000000000000 | 1.00000
103.03634500000000000000 | 104.92580000000000000000 | 1.00000
368.20601600000000000000 | 409.45000300000000000000 | 1.00000
declare @FromDate as date
declare @ToDate as date
declare @SymbolId as int
set @FromDate ='2013-08-20'
set @ToDate='2013-08-27'
set @SymbolId=14
SET NOCOUNT ON;
with currenciesByFirstDate as (
select * from Currencies join CurrencySymbols cs on
cs.Id=Currencies.CurrencySymbolId
where Date=@FromDate
),
currenciesBySecondDate as (
select * from Currencies join CurrencySymbols cs on
cs.Id=Currencies.CurrencySymbolId
where Date=@ToDate
),
currencyByFirstDateAndSymbol as (
select * from currenciesByFirstDate
where currenciesByFirstDate.CurrencySymbolId=@SymbolId
),
currencyBySecondDateAndSymbol as (
select * from currenciesBySecondDate
where currenciesBySecondDate.CurrencySymbolId=@SymbolId
)
select
case when @SymbolId!=147 then
currenciesByFirstDate.Value/currencyByFirstDateAndSymbol.Value else
currenciesByFirstDate.Value end as ValueFirstDate,
case when @SymbolId!=147 then
currenciesByFirstDate.Value/currencyBySecondDateAndSymbol.Value else
currenciesBySecondDate.Value end as ValueSecondDate,
currenciesByFirstDate.CurrencySymbolId,
currenciesBySecondDate.Description as CurrencyDescription,
currenciesBySecondDate.SymbolName as CurrencySymbol,
(currenciesByFirstDate.Value/currencyByFirstDateAndSymbol.Value)/(currenciesByFirstDate.Value/currencyBySecondDateAndSymbol.Value)
as RESULT
from currenciesByFirstDate
join currenciesBySecondDate on
currenciesByFirstDate.Id=currenciesBySecondDate.Id
join CurrencySymbols cs on cs.Id=currenciesByFirstDate.CurrencySymbolId
join currencyByFirstDateAndSymbol on
currencyByFirstDateAndSymbol.CurrencySymbolId=currencyByFirstDateAndSymbol.CurrencySymbolId
join currencyBySecondDateAndSymbol on
currencyBySecondDateAndSymbol.CurrencySymbolId=currencyBySecondDateAndSymbol.CurrencySymbolId
I solved this by making a subquery and calculate this instead;
declare @FromDate as date
declare @ToDate as date
declare @SymbolId as int
set @FromDate ='2011-01-20'
set @ToDate='2013-08-27'
set @SymbolId=147
SET NOCOUNT ON;
with currenciesByFirstDate as (
select * from Currencies join CurrencySymbols cs on
cs.Id=Currencies.CurrencySymbolId
where Date=@FromDate
),
currenciesBySecondDate as (
select * from Currencies join CurrencySymbols cs on
cs.Id=Currencies.CurrencySymbolId
where Date=@ToDate
),
currencyByFirstDateAndSymbol as (
select * from currenciesByFirstDate
where currenciesByFirstDate.CurrencySymbolId=@SymbolId
),
currencyBySecondDateAndSymbol as (
select * from currenciesBySecondDate
where currenciesBySecondDate.CurrencySymbolId=@SymbolId
), a as (
select
case when @SymbolId!=147 then
currenciesByFirstDate.Value/currencyByFirstDateAndSymbol.Value else
currenciesByFirstDate.Value end as ValueFirstDate,
case when @SymbolId!=147 then
currenciesByFirstDate.Value/currencyBySecondDateAndSymbol.Value else
currenciesBySecondDate.Value end as ValueSecondDate,
currenciesByFirstDate.CurrencySymbolId,
currenciesBySecondDate.Description as CurrencyDescription,
currenciesBySecondDate.SymbolName as CurrencySymbol,
(currenciesByFirstDate.Value/currencyByFirstDateAndSymbol.Value)/(currenciesByFirstDate.Value/currencyBySecondDateAndSymbol.Value)
as RESULT
from currenciesByFirstDate
join currenciesBySecondDate on
currenciesByFirstDate.Id=currenciesBySecondDate.Id
join CurrencySymbols cs on cs.Id=currenciesByFirstDate.CurrencySymbolId
join currencyByFirstDateAndSymbol on
currencyByFirstDateAndSymbol.CurrencySymbolId=currencyByFirstDateAndSymbol.CurrencySymbolId
join currencyBySecondDateAndSymbol on
currencyBySecondDateAndSymbol.CurrencySymbolId=currencyBySecondDateAndSymbol.CurrencySymbolId)
select ValueFirstDate/ValueSecondDate from a
And now it works! :)