making a CASE stmt more efficient -maybe use CURS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

making a CASE stmt more efficient -maybe use CURS?

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 17:08:54

Is there a why to make the code below more efficient? It works fine at the moment, but I will
be expanding the case statements to about 50 more. Surfed the net, but found very little with the
exception of an article which mentioned using CURSOR. Not sure how to use CURSOR, but just wanted
to put it out there in hopes someone may have some suggestions? Thank for your time in advance.



SELECT  b.ID,

SUM(CASE WHEN a.desc = 'assets' AND  b.bk <> 'ny' THEN  b.balance ELSE 0 END)      bal1,
SUM(CASE WHEN a.desc = 'liab' AND  b.bk <> 'il' THEN  b.balance ELSE 0 END)        bal2,
SUM(CASE WHEN a.desc = 'O/E' AND  b.bk <> 'ca' THEN  b.balance ELSE 0 END)         bal3,
SUM(CASE WHEN a.desc = 'TL eq' AND  b.bk <> 'ny' THEN  b.balance ELSE 0 END)   bal4 

into table1
FROM dbo.balance b  
LEFT OUTER JOIN dbo.desc   a
ON b.account = a.ACCOUNT
group by b.ID

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-19 : 06:50:52

can you post DDL of your table and some sample data.

i would not suggest you using cursors from what i see in your query above.
besides post some sample data, explain what are you trying to achieve with case statements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:28:10

May be you could add a derived column based on your 50 cases. then use it for grouping and finding sum or use PIVOT to get aggregated values in columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-19 : 14:36:55

Why are you using LEFT JOIN when you are checking both columns in the CASE statement?
The CASE check defers the LEFT JOIN purpose...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-19 : 14:44:22

[code]DECLARE @Table1 TABLE
(
k INT,
v INT,
z INT
)

INSERT @Table1
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2

DECLARE @Table2 TABLE
(
k INT,
v INT
)

INSERT @Table2
SELECT 2, 9 UNION ALL
SELECT 2, 8

SELECT t1.*,
t2.*,
CASE WHEN t1.v = 1 AND t2.v <> 9 THEN 'Hit' ELSE 'No hit' END AS Original,
CASE WHEN t1.v = 1 AND (t2.v <> 9 OR t2.v IS NULL) THEN 'Hit' ELSE 'No hit' END AS Peso
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.k = t1.k[/code]

N 56°04'39.26"
E 12°55'05.63"

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-19 : 22:45:59

An account can have multiple records for various types of balances. I am just trying to create 1 record
which will house all the balances into 1 record by storing each type of balance in a column.
If a record does not have a balance for a specific bal type I need to assign it a 0 value.

The final record I am trying to accompish should like something like this:


ID     bal1      bal2      bal3      bal4

a      5.25       .0        7.77      8.23
b       .25       .25      33.33    234.33
c      9.99       .0         .00       .00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources