Need suggestion for case statement code optimisation.

Question

Need suggestion for case statement code optimisation.

Anand BI Developer

SSCommitted

Points: 1772
More actions
February 9, 2017 at 7:32 am

#318596

Hi Experts ,
my below coding is working fine , but it is very lenghty and i am using lots of case statments .., is there any possible to optimise this code or any others method to achieve answer ..?
-- code :
select Salutation,case when len(FirstName)= 0 then Initials else FirstName end FirstName ,LastName,[Site Add1],[Site Add2],[Site Add3],[Site District],[Site Town],[Site County],[Site Postcode],
    case when [Site Add1]=[App Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [app Add1] end [App Add1],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [app Add2] end [App Add2],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [app Add3] end [App Add3],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [App District] end [App District],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [App Town] end [App Town],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]
    then '' else [App County] end [App County],
    case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site Town]=[App Town]
    then '' else [App Postcode] end [App Postcode],[Phone] [Tel],PROJECT_NAME Heading,[Local Authority],[Application No],Agent,[Agent Add1],[Agent Add2],[Agent Add3],[Agent District],[Agent Town],[Agent County],
    [Agent Postcode],[Agent Phone] [Agent Tel],PROJECT_DESCRIPTION [Description]
from #MPloft5 where len(LastName)>0

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This is an untested version that might help. It only works if you're repeating the exact same CASE clause.
select Salutation, case when len(FirstName)= 0 then Initials else FirstName end FirstName , LastName, [Site Add1], [Site Add2], [Site Add3], [Site District], [Site Town], [Site County], [Site Postcode], ISNULL( x.[app Add1], '') AS [App Add1], ISNULL( x.[app Add2], '') AS [App Add2], ISNULL( x.[app Add3], '') AS [App Add3], ISNULL( x.[App District], '') AS [App District], ISNULL( x.[App Town], '') AS [App Town], ISNULL( x.[App County], '') AS [App County], case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site Town]=[App Town] then '' else [App Postcode] end [App Postcode], [Phone] [Tel], PROJECT_NAME Heading, [Local Authority], [Application No], Agent, [Agent Add1], [Agent Add2], [Agent Add3], [Agent District], [Agent Town], [Agent County], [Agent Postcode], [Agent Phone] [Agent Tel], PROJECT_DESCRIPTION [Description] from #MPloft5 OUTER APPLY (SELECT [app Add1], [app Add2], [app Add3], [App District], [App Town], [App County] WHERE [Site Add1]=[App Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site District]=[App District] and [Site Town]=[App Town] and [Site County]=[App County] and [Site Postcode]=[App Postcode]) x where len(LastName)>0;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 2

Dear Luis Cazares ,
Thank you very much for your valuable time.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Here's another way of doing the same as Luis' suggestion which I find more intuitive (but that's just personal preference)

SELECT

Salutation,

case when len(FirstName)= 0 then Initials else FirstName end FirstName ,

LastName,[Site Add1],[Site Add2],[Site Add3],

[Site District],[Site Town],[Site County],[Site Postcode],

case when x.Picker = 1 then '' else [app Add1] end [App Add1],

case when x.Picker = 1 then '' else [app Add2] end [App Add2],

case when x.Picker = 1 then '' else [app Add3] end [App Add3],

case when x.Picker = 1 then '' else [App District] end [App District],

case when x.Picker = 1 then '' else [App Town] end [App Town],

case when x.Picker = 1 then '' else [App County] end [App County],

case when [Site Add1]=[app Add1] and [Site Add2]=[App Add2] and [Site Add3]=[App Add3] and [Site Town]=[App Town]

then '' else [App Postcode] end [App Postcode],

[Phone] [Tel],

PROJECT_NAME Heading,[Local Authority],[Application No],Agent,[Agent Add1],

[Agent Add2],[Agent Add3],[Agent District],[Agent Town],[Agent County],

[Agent Postcode],

[Agent Phone] [Agent Tel],

PROJECT_DESCRIPTION [Description]

from #MPloft5

CROSS APPLY (

SELECT Picker = CASE

WHEN [Site Add1]=[App Add1]

and [Site Add2]=[App Add2]

and [Site Add3]=[App Add3]

and [Site District]=[App District]

and [Site Town]=[App Town]

and [Site County]=[App County]

and [Site Postcode]=[App Postcode]

THEN 1 ELSE 0 END) x

WHERE len(LastName) > 0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 4

Dear <a title="Go to ChrisM ,
Thank you very much for your valuable suggestion.