Select query with calculations

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select query with calculations

Author

Topic

propoo2
Starting Member

2 Posts

Posted - 2009-04-27 : 11:47:27

Hey All,

I have Table like

Table
Company User Type
1 1 Month
2 3 Month
3 1 Quarter
4 3 Quarter

I want to select query with result like this with some calculation. If Type is Month User should be $30 & if Type is Quarter User should be $90 and extra users in for any Type should be $10 extra.

Result

Company MonthAmt
1 $30
2 $60
3 $90

this is how calculation I want perfrom

Company Amount
1 (1*30)
2 (1*30) + (2*10)
3 (1*90)
4 (1*90) +(2*10)

Thanks!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-27 : 12:31:35

Try this:

DECLARE @Company TABLE (Company int,[User] int,[Type] varchar(10) )

INSERT INTO @Company
SELECT 1,1,'Month' UNION ALL
SELECT 2,3,'Month' UNION ALL
SELECT 3,1,'Quarter' UNION ALL
SELECT 4,3,'Quarter'

SELECT co.Company,co.[user],co.[type]
,[Amount] =
CASE
WHEN co.[user] > 0 THEN
CASE
WHEN co.[Type] = 'Month' THEN 30 + (10 * ( [USER] - 1))
WHEN co.[Type] = 'Quarter' THEN 90 + (10 * ( [USER] - 1))
END
ELSE 0
END

FROM

@Company co

Jim

Make sure to look up CASE statements and nesting them in Books On Line

propoo2
Starting Member

2 Posts

Posted - 2009-04-27 : 16:54:28

Thanks it worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources