CODEWITHSUNDEEP
sqlsql-server
frank
frank

Reputation: 3598

multiply values from 2 tables sql server

I have two tables and want to multiply values together if they satisfy a condition. I looked at Multiply 2 values from 2 different tables to no avail

I want to multiply all values in #Temp1 with the corresponding value from #Temp2 where the month from #Temp1 is the same as the month from #Temp2.

I tried

select costs * ratio as Value from #Temp1, #Temp2 where #Temp2.ratio = cast(SUBSTRING(#Temp1.date,5,2)as int)

to no avail

I want:

costs  date  ratio
234.33 20170103 23.433
56.65  20170203 11.33

I am working with SQL Server2012, and any help will be appreciated.

Table #Temp1
234.33  20170103
56.65   20170203

Table #Temp2
0.1 1
0.2 2
0.3 3   


IF OBJECT_ID ('tempdb..#Temp1') IS NOT NULL

    DROP TABLE #Temp1

IF OBJECT_ID ('tempdb..#Temp2') IS NOT NULL
    DROP TABLE #Temp2

create table #Temp1
(
    costs float, 
    date Varchar(50) 
)

create table #Temp2
(
    ratio float, 
    month int 
)


insert into #Temp1 
values (234.33, 20170103)

insert into #Temp1 
values (56.65, 20170203)

insert into #Temp2 
values (.1,01)
insert into #Temp2 
values (.2,02)
insert into #Temp2 
values (.3,03)

select ratio from #Temp2 where month=3
select cast(SUBSTRING(date,5,2)as int) as month from #Temp1

select * from #Temp1
select * from #Temp2
select costs * ratio as Value
from #Temp1, #Temp2
where #Temp2.ratio = cast(SUBSTRING(#Temp1.date,5,2)as int)

Upvotes: 2

Views: 126

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

It was minor typo but the panic one, used ratio instead of date in the comparison

Above change will resolve your problem but still you can follow the few other major things

1.Avoid using keywords as column names as date, month

2.You don't need to cast the value in INT because it's implicitly converted before comparison

select t1.costs, 
    t1.date,
    (costs * ratio) as ratio
from #Temp1 t1
INNER JOIN #Temp2 t2 ON t2.month = SUBSTRING(t1.date,5,2)

Upvotes: 0

M Danish
M Danish

Reputation: 478

Try now and give me remarks.

select month , case when #Temp2.month = cast(SUBSTRING(#Temp1.date,5,2)as int) then costs * ratio else null End as Value
from #Temp1 
left join #Temp2 on 
#Temp2.month = cast(SUBSTRING(#Temp1.date,5,2)as int)

Upvotes: 0

Killer Queen
Killer Queen

Reputation: 796

If I understood correctly, it should be :

where #Temp2.month = cast(SUBSTRING(#Temp1.date,5,2)as int)

Instead :

where #Temp2.ratio = cast(SUBSTRING(#Temp1.date,5,2)as int)

Upvotes: 1

frank
frank

Reputation: 3598

I am a bonehead.

it was a typo:

select *, costs * ratio as Value
from #Temp1, #Temp2
where #Temp2.month = cast(SUBSTRING(#Temp1.date,5,2)as int)

works

Upvotes: 0

Related Questions