Hello community, so glad to have found this place. I hope to be around a long time, my name is Jason and I am new here! Thanks for having me.
Now may I discuss what I wish to do:
I am using CASE statements to determine which Notification to use in a 3rd party application. In this particular statement I have multiple revisions of a document and I need to be able to determine between the 2 and compare them and if condition is met than to use Notify List 2. Now I have it partly done but my problem is that it is only looking for GREATER THAN as you can see. I need it to make sure that the GREAT THAN is only +1 higher than the 2nd one. I need to make it dynamic as many documents will not be the same revision number. This also needs to check if there is not a revision within 7 days of last document revision.
I am open to discussion as I am here to learn!
Thank you for your time and I appreciate you.
It is for the 2nd 'Notify List 2'.
Case When ABS(DATEDIFF(mi, getdate(), A.DateDoc))>=10080 AND ABS(DATEDIFF(mi, A.DateDoc, A.RevStartDate)) IS NULL Then 'Notify List 1' When ABS(DATEDIFF(mi, getdate(), A.DateDoc)) - ABS(DATEDIFF(mi, A.DateDoc, A.RevStartDate))>=10080 AND(SELECT MAX(Revision) FROM Master)>(SELECT MAX(Revision) FROM Master WHERE Revision NOT IN (SELECT MAX(Revision) FROM Master)) Then 'Notify List 2' END as NotificationListToUse,