Help with Case Statement 2

[eastwestk]

Hello,

I have the following code in my stored procedure:


[CTTitle] =(SELECT TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE ID =
(SELECT pl.PresID FROM #PresidentList pl
WHERE pl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = d.donorcompanyId))
AND YEAR(ENDDATE) =
(CASE
WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
ELSE YEAR(GETDATE())
END)),

[CTTitle1] =(SELECT TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE MemberID = od.ShipToID and rank=405
AND YEAR(ENDDATE) =
(CASE
WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
ELSE YEAR(GETDATE())
END))


I want to achieve if [CTTitle] is null then I want to display [CTTitle1] else [CTTitle].

Please anybody let me know how to accomplish this.

Thanks!

 

[SkipVought]

Hi,

How about

WHEN MONTH(GETDATE())>=7 THEN YEAR(GETDATE()) +1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmmastros]

This is sub-query madness.

I want to achieve if [CTTitle] is null then I want to display [CTTitle1] else [CTTitle].

Normally, this is handled through the coalesce function. Coalesce takes multiple parameters and will return the first one that is not null. so...

Coalesce(ColA, Colb, ColC)

You should be able to implement this in your code like this...

[CTTitle] =[!]Coalesce([/!](SELECT TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE ID =
 (SELECT pl.PresID FROM #PresidentList pl
 WHERE pl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = d.donorcompanyId))
        AND YEAR(ENDDATE) =
               (CASE
               WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
               ELSE YEAR(GETDATE())
               END))[!],[/!]
 
       (SELECT  TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE MemberID  = od.ShipToID and rank=405
            AND YEAR(ENDDATE) =
                (CASE
                WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
                ELSE YEAR(GETDATE())
                END)) [!])[/!]

It's possible that I have a comma or parenthesis out of place. But, hopefully the concept makes sense.

I would encourage you to modify this code so that you use joins instead of sub-queries. Joins will make the code 100 times easier to read and will (probably) execute many times faster.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom