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?

Gordon Linoff · Accepted Answer · 2014-09-04 12:07:51Z

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()?

+1 for mentioning least() and greatest() - you only realize how useful they are when you don't have them :-/
Yes, that's why I asked the question because I thought to myself "something this simple must exist surely?!" :D
While this answer is correct, I've just created a function to get the most recent date out of two dates and used that!

Bulat · Accepted Answer · 2014-09-04 12:14:53Z

You can move your case to the WHERE clause:

 AND pli.CreatedOn BETWEEN IFF(ISNULL(ug.lastpromotion,u.CreatedOn) < DATEADD(MM,-6,GETDATE()), DATEADD(MM,-6,GETDATE()), ISNULL(ug.lastpromotion,u.CreatedOn)) AND GETDATE()

Collectives™ on Stack Overflow

Best way of handling if a>b in a case

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related