row based condition - 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

SQL Server 2008 Forums

Transact-SQL (2008)

row based condition

Author

Topic

Tonekene
Starting Member

6 Posts

Posted - 2014-09-04 : 12:08:36

Here is my table

Id Name Acct Amount
1 mike sav $100
2 mike check $200
3 mike mm $300
4 mike cd $400
5 mike ira $500
6 mike 401k $600

I need to produce a report for mike which will list his name and Status

For example:
Name Status
Mike Great Investor

Status is determined as follows (do not take numbers literally)

Status:
-if Mike has check acct and amount > 0 and he has 401k and amount is >$500 he is "Good Investor"
-if Mike has check acct and amount > 100 and he has 401k and amount is >$500 and he has cd > $750 he is "Great Investor"
-if Mike has sav acct and amount > 0 and he has 401k and amount is >$1000 and he has ira > 200 and cd > 0 he is "Excellent Investor"

What would be the easiest, or neat way of doing it?
Your advice is greatly appreciated

in the meanwhile i am bulding a grid, unless you or i come up with another idea

Thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-04 : 12:25:46

I am pretty sure the precedence will work

CREATE TABLE #I
( ID int,
Name varchar(10),
AccountType varchar(5),
Amount money
)

INSERT INTO #I
VALUES(1 , 'mike', 'sav' ,100),
(2 , 'mike', 'check' ,200),
(3 , 'mike', 'mm' ,300),
(4 , 'mike', 'cd' ,400),
(5 ,'mike', 'ira' ,500),
(6 , 'mike', '401k' ,600 )

SELECT *,CASE WHEN [check] > 0 and [401k] >1000 AND ira > 200 AND cd > 0 THEN 'Excellent Investor'
WHEN [check] > 100 and [401k] >500 AND cd > 750 THEN 'Great Investor'
WHEN [check] > 0 and [401k] >500 THEN 'Good Investor'
ELSE 'Slacker'
END

FROM
(SELECT Name,AccountType,Amount
FROM #i) AS SourceTable
PIVOT
(
Sum(Amount)
FOR AccountType IN ([sav], [check], [mm], [cd], [ira],[401k])
) AS InvestorAccounts;

Tonekene
Starting Member

6 Posts

Posted - 2014-09-04 : 12:35:14

I did not think of pivot, trying now. thank you very much!

great success!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources