how to replace this case statement with a subquery

Question

CASE
           WHEN(CASE
                    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                         AND VEH.FVMessagingInd = 1
                    THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1  
                    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                    THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
                    WHEN VEH.FVMessagingInd = 1
                    THEN 1
                    ELSE 0
                END - ISNULL((COM.OutreachCount + COM.FVMsgCount), 0)) < 0
           THEN 0
           ELSE(CASE
                    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                         AND VEH.FVMessagingInd = 1
                    THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1 
                    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                    THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
                    WHEN VEH.FVMessagingInd = 1
                    THEN 1
                    ELSE 0
                END - ISNULL((COM.OutreachCount + COM.FVMsgCount), 0))
       END AS EventsRemaining,
       CASE
           WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                AND VEH.FVMessagingInd = 1
           THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1  
           WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
           THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
           WHEN VEH.FVMessagingInd = 1
           THEN 1
           ELSE 0
       END AS ExpectedNumofEvents,

I have a logic like below inside my View. How to replace this case statement with a shorter way or any subquery without writing multiple times the same logic, how can i store in any variable and how to do this in View. Please help me to fix this. Thanks in Advance.

Answer 1

For one improvement, you can remove some of the ISNULL() expressions.

For example, NULL > 0 is never true. Therefore the ISNULL(VEH.DriverOutreachProfileId, 0) > 0 expression in the first WHEN clause is also never true if VEH.DriverOutreachProfileId is NULL.

This can sometimes get you into trouble, because it's also not exactly false. However, a WHEN expression will also not match a NULL boolean result, so we're safe here to remove the ISNULL() check completely, leaving us with this:

VEH.DriverOutreachProfileId > 0

We now apply this and can further look at these two WHEN/THEN expressions:

WHEN VEH.DriverOutreachProfileId > 0
             AND VEH.FVMessagingInd = 1
    THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1  
WHEN VEH.DriverOutreachProfileId > 0
    THEN ISNULL(CPP.ExpectedNumberofEvents, 0)

Notice they both have the same THEN result, except the former adds one.

This is almost redundant, and I think we can factor out the AND VEH.FVMessagingInd = 1 check.

Put both adjustments together, and I think this:

CASE
    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
         AND VEH.FVMessagingInd = 1
    THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1  
    WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
    THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
    WHEN VEH.FVMessagingInd = 1
    THEN 1
    ELSE 0
END

reduces to this:

CASE WHEN VEH.DriverOutreachProfileId > 0 
     THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
     ELSE 0 END
 + CASE WHEN VEH.FVMessagingInd = 1 THEN 1 ELSE 0 END

Or this:

COALESCE(CASE WHEN VEH.DriverOutreachProfileId > 0 
              THEN CPP.ExpectedNumberofEvents END, 0)
 + CASE WHEN VEH.FVMessagingInd = 1 THEN 1 ELSE 0 END

And now we spread this through the rest of the excerpt:

CASE
    WHEN COALESCE(CASE WHEN VEH.DriverOutreachProfileId > 0 
                       THEN CPP.ExpectedNumberofEvents END, 0)
                   + CASE WHEN VEH.FVMessagingInd = 1 THEN 1 ELSE 0 END
           < ISNULL(COM.OutreachCount + COM.FVMsgCount, 0) 
    THEN 0
    ELSE COALESCE(CASE WHEN VEH.DriverOutreachProfileId > 0 
                       THEN CPP.ExpectedNumberofEvents END, 0)
                    + CASE WHEN VEH.FVMessagingInd = 1 THEN 1 ELSE 0 END
                    - ISNULL(COM.OutreachCount + COM.FVMsgCount, 0)
END AS EventsRemaining,
COALESCE(CASE WHEN VEH.DriverOutreachProfileId > 0 
              THEN CPP.ExpectedNumberofEvents END, 0)
 + CASE WHEN VEH.FVMessagingInd = 1 THEN 1 ELSE 0 END AS ExpectedNumofEvents,

You could, of course, also further reduce things by nesting the this in another SELECT query, so this result is a computed column.