xr280xr (9/29/2016)
Here's what I settled on. AllowsDeletionGracePeriod was a new column so I had to update the where clause to include it.
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
Assuming that DeleteAsap only allows the values 1 and 0 then these two are equivalent. That is, you don't need to worry about being mutually exclusive. You're trying to force an order to the evaluation which is unnecessary.
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
I can even show you the logic if you don't believe me.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA