CODEWITHSUNDEEP
sql
VeecoTech
VeecoTech

Reputation: 2143

Multiple case condition

How do i achieve the multiple case condition? The below say invalid syntax

 SUM(CASE WHEN b.SnapshotDtm = getdate() THEN        
       CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') THEN 
         (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * 
                 (TechRepresentCnt_F)/ (365/FiscalYearDayNbr)
        else CASE WHEN (EthnicGroupCd in(2,3,5)) THEN 
              (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * 
                 (TechRepresentCnt_F)/ (365/FiscalYearDayNbr) 
        ELSE 
            CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') THEN
                    (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * 
                         (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr)
        ELSE CASE WHEN (EthnicGroupCd in(2,3,5)) THEN 
                        (TechAnnualizedExitsPct_White - 
                          TechAnnualizedExitsPct_235) * 
                          (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr) 
            end) as TechExitsDeltaCnt,

Upvotes: 1

Views: 109

Answers (3)

Dhaval
Dhaval

Reputation: 2379

Else And END tag Missed

    SUM(CASE WHEN b.SnapshotDtm = getdate() 
THEN        
 CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') 
     THEN 
       (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * 
     (TechRepresentCnt_F)/ (365/FiscalYearDayNbr)
      else
     CASE WHEN (EthnicGroupCd in(2,3,5))
      THEN 
      (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * 
       (TechRepresentCnt_F)/ (365/FiscalYearDayNbr) -------------Else And END TAG is Missing
 ELSE 
       CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') 
       THEN
       (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * 
        (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr)
         ELSE CASE WHEN (EthnicGroupCd in(2,3,5)) THEN 
         (TechAnnualizedExitsPct_White - 
         TechAnnualizedExitsPct_235) * 
         (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr) -------------Else And END TAG is Missing
           end) as TechExitsDeltaCnt,

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You're actually missing a few ENDs for each opened CASE. Try this:

SUM(CASE 
       WHEN b.SnapshotDtm = getdate() 
          THEN CASE 
             WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') 
                THEN (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * (TechRepresentCnt_F)/ (365/FiscalYearDayNbr)
                ELSE CASE 
                   WHEN (EthnicGroupCd in(2,3,5)) 
                      THEN (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * (TechRepresentCnt_F)/ (365/FiscalYearDayNbr) 
                      ELSE CASE 
                         WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') 
                            THEN (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr)
                            ELSE CASE 
                               WHEN (EthnicGroupCd in(2,3,5)) 
                                  THEN (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr)
                                 END
                           END
                     END
               END
     END) AS TechExitsDeltaCnt

Upvotes: 2

Mackan
Mackan

Reputation: 6271

Your nesting is off. You're missing a few ENDs:

SUM(
    CASE WHEN b.SnapshotDtm = getdate() THEN         
        CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') THEN (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * (TechRepresentCnt_F)/ (365/FiscalYearDayNbr)
        ELSE 
            CASE WHEN (EthnicGroupCd in(2,3,5)) THEN (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * (TechRepresentCnt_F)/ (365/FiscalYearDayNbr)
            END 
        END
    ELSE 
        CASE WHEN (GenderCd = 'F' And EthnicGroupCd = 'N/A') THEN (TechAnnualizedExitsPct_M - TechAnnualizedExitsPct_F) * (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr)
        ELSE 
            CASE WHEN (EthnicGroupCd in(2,3,5)) THEN (TechAnnualizedExitsPct_White - TechAnnualizedExitsPct_235) * (TechRepresentCnt_F)/ (365/SnapshotDaysOfYearNbr) 
            END
        END
    END) as TechExitsDeltaCnt,

Upvotes: 3

Related Questions