Best way to handle if a> b in case
Is there a better way to write the following query snippet?
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
Since all that changes is pli.createdon, you need to use the most recent from the last ad or 6 months ago. (i.e. count the number of deals since they were last promoted or 6 months ago, which is the most recent)?
Or am I stuck doing 2 cases for each type?
+3
source to share
2 answers
You can do this in one case
, simply by improving the logic in the sentence where
:
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()
?
+3
source to share