SQL Redundancy - 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 2005 Forums

Transact-SQL (2005)

SQL Redundancy

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-12-28 : 09:33:36

Have the following SQL:

             case
               when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                         '225M', '230M', '231M', '233M',
                                         '234M', '235M', '236M', '237M',
                                         '238M', '239M', '240M', '266M',
                                         '270M', '275M', '645M', '647M',
                                         '649M', '745M', '760M', '650M' ) then m.PerformPostingDate
               else COALESCE(m.PerformTradeDate, m.PerformSettlementDate, m.PerformPostingDate)
             end as Effective_Date,
             case
               when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                         '225M', '230M', '231M', '233M',
                                         '234M', '235M', '236M', '237M',
                                         '238M', '239M', '240M', '266M',
                                         '270M', '275M', '645M', '647M',
                                         '649M', '745M', '760M', '650M' ) then m.PerformPostingDate
               else COALESCE(m.PerformTradeDate, m.PerformSettlementDate, m.PerformPostingDate)
             end as TradeDate,
             case
               when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                         '225M', '230M', '231M', '233M',
                                         '234M', '235M', '236M', '237M',
                                         '238M', '239M', '240M', '266M',
                                         '270M', '275M', '645M', '647M',
                                         '649M', '745M', '760M', '650M' ) then m.PerformPostingDate
               else COALESCE(m.PerformSettlementDate, m.PerformPostingDate)
             end as SettlementDate,

This is piece of a sql statements happen about 5 times throughout a procedure as I have several joins that basically do the same thing with different filters. My question is, and am newer to the game with SQL, is there any better way to clean this up and make it more efficient through the procedure rather then having it repeat so many times?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 09:47:51

Put the values into a table variable - or maybe a table so that they can be maintained.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 09:55:36

could try

select COALESCE(PerformPostingDatetmp, PerformTradeDate, PerformSettlementDate, PerformPostingDate) as Effective_Date,
COALESCE(PerformPostingDatetmp, PerformTradeDate, PerformSettlementDate, PerformPostingDate) as TradeDate,
COALESCE(PerformPostingDatetmp, PerformSettlementDate, PerformPostingDate) as SettlementDate ,
.....
from
(
select PerformPostingDatetmp =
case
when m.taxcode + 'M' in (select code from @tblcode) then m.PerformPostingDate
end , m.PerformTradeDate, m.PerformSettlementDate, m.PerformPostingDate,
rest of your query
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-12-28 : 09:57:41

this is good stuff, thanks for suggestions and will look to test them out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources