Best way of handling if a>b in a case

Question

Is there a better way of writing the following chunk of a query?

CASE WHEN ugt.Type = 'Permanent' 
            AND ISNULL(ug.lastpromotion,u.CreatedOn) < DATEADD(MM,-6,GETDATE())
        THEN 
            (
                SELECT  cast(sum(pc.CommissionPerc / 100) as numeric(3,1))
                FROM Placements p
                INNER JOIN PlacementConsultants pc on pc.PlacementId = p.PlacementID AND pc.UserId = @userid
                INNER JOIN PlacementInvoices PlI on PlI.PlacementID = P.Placementid
                WHERE p.CreatedUserId = @userid
                AND pli.CreatedOn 
                        BETWEEN DATEADD(MM,-6,GETDATE())
                        AND GETDATE()
            )
    WHEN ugt.Type = 'Permanent' 
            AND ISNULL(ug.lastpromotion,u.CreatedOn) > DATEADD(MM,-6,GETDATE())
        THEN 
            (
                SELECT cast(sum(pc.CommissionPerc / 100) as numeric(3,1))
                FROM Placements p
                INNER JOIN PlacementConsultants pc on pc.PlacementId = p.PlacementID AND pc.UserId = @userid
                INNER JOIN PlacementInvoices PlI on PlI.PlacementID = P.Placementid
                WHERE pc.UserId = @userid
                AND pli.CreatedOn 
                        BETWEEN ISNULL(ug.lastpromotion,u.CreatedOn)
                        AND GETDATE()
            )
END

As all that's changing is the pli.createdon needs to use the most recent out of the last promotion or 6 months ago. (i.e. count the number of deals since their latest promotion or 6 months ago, whatever is the most recent)?

Or am I stuck doing 2 cases for each type?

Answer 1

You can do this in a single case, just by enhancing the logic in the where clause:

CASE WHEN ugt.Type = 'Permanent' 
     THEN (SELECT  cast(sum(pc.CommissionPerc / 100) as numeric(3,1))
           FROM Placements p INNER JOIN
                PlacementConsultants pc
                on pc.PlacementId = p.PlacementID AND pc.UserId = @userid INNER JOIN
                PlacementInvoices PlI
                on PlI.PlacementID = P.Placementid
           WHERE p.CreatedUserId = @userid AND
                 pli.CreatedOn <= GETDATE() AND
                 ((pli.CreatedOn >= DATEADD(MM, -6, GETDATE()) AND
                   ISNULL(ug.lastpromotion, u.CreatedOn) < DATEADD(MM, -6, GETDATE())
                  ) OR
                  (pli.ISNULL(ug.lastpromotion, u.CreatedOn) AND
                   ISNULL(ug.lastpromotion, u.CreatedOn) >= DATEADD(MM, -6, GETDATE())
                  )
                 )

Wouldn't it be nice if SQL Server supported least() and greatest()?