hay all i am new to sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

hay all i am new to sql

Author

Topic

NewBird
Starting Member

3 Posts

Posted - 2010-05-04 : 11:10:30

i have a table with 3 columns named ACTUAL,PROJECTNumber and ID i need to retrive one sigle value by writing a query for actuals and formulla is like
ACTUAL(Projectnumber)/Actual(Projectnumber) for example
Actual(101)/Actual(102)

101 and 102 are different departmentnumbers some how i need to get values with these two departmentnumber filters right in my select clause

can someone help this new bird to start flying?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-04 : 11:20:47

Sure. Can you provide table structure, sample data and expected output.

You can refer this link on how to do that.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

NewBird
Starting Member

3 Posts

Posted - 2010-05-04 : 11:39:08

Its a simple table with 3 columns actual,project and an id
actually i want to get just one row of data
By dividing actuals of both departments
i am confused how to put two where clauses here in one query and then divide numbers
1.select actual
from tbl
where project=101
2. select actual from tbl
where project=102
with these two simple queries i can get both numbers but i want to get these numbers with one query and confused how to place two conditions with in select clause and then divide them

required result is actual of department 101 / Actual of department 102
i hope it make sence

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-04 : 11:58:26

I dont understand this perfectly, but maybe this.

select actual / (select actual from  tbl where dept = '102')
from tbl where dept = '101'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 05:15:17

select 1.0*actual / (select actual from tbl where dept = '102')
from tbl where dept = '101'

for accuracy

Madhivanan

Failing to plan is Planning to fail

NewBird
Starting Member

3 Posts

Posted - 2010-05-17 : 14:10:35

Thanks for your reply..
My table OTMH has different account numbers for different KPI's like

AccountNUmber Actual Accounttype
102 15 OTActual
103 17 MHActual
104 18 EXPActual

now i am making a view in that view i need to create a ratio column and ratio for KPI is going to be calculated by this formulla
(Actual(102)/Actual(103))*100 (15/17)*100
i want my select statment to find row of 102 OTActual and divide it by 103 MHActual then multiply it by 100 and produce me a value in new ratio column

i have a flope case statement

Select (Case when account_number =102 then Actual/(Select Actual where account_number =103)*100 As Ratio
From OTMH;

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources