Case statement compare 1st and 2nd revision

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.

Server: SQL2008

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,

Hi Jason,
Welcome :slight_smile:

As you only showed part of your query (and probably a simplified one), it's harder to suggest the best option in your particular case. But the following ought to do the job.

Replace this:

AND(SELECT MAX(Revision) FROM Master)>(SELECT MAX(Revision) FROM Master WHERE Revision NOT IN (SELECT MAX(Revision) FROM Master))

with this:

and exists (select 1
              from (select top(2)
                           revision
                      from master
                     order by revision desc
                   ) as a
              having min(a.revision)=max(a.revision)-1
           )
1 Like

That does appear to work and I get different results than I was getting. I am new here and working with SQL.

Let me post the full query:

SELECT A.OP__ID AS FoundDocid, A.DateDoc AS FoundDocDate, A.Revision_Date AS FoundRevStartDate, ABS(DATEDIFF(mi, getdate(), A.DateDoc)) AS UnitsElapsed, CASE WHEN ABS(DATEDIFF(mi, getdate(), A.DateDoc))>=10080 AND ABS(DATEDIFF(mi, A.DateDoc, A.Revision_Date)) IS NULL THEN 'Notify List 1' WHEN ABS(DATEDIFF(mi, getdate(), A.DateDoc)) - ABS(DATEDIFF(mi, A.DateDoc, A.Revision_Date))>=10080 AND(SELECT MAX(Revision) FROM Master)>(SELECT MAX(Revision) FROM Master WHERE Revision NOT IN (SELECT MAX(Revision) FROM Master)) AND Revision<>0 THEN 'Notify List 2' END AS NotificationList, A.Clientkey AS FaceKey, A.OP__id AS SourceDocid, A.Revision_Date AS SourceDate, ABS(DATEDIFF(mi, A.DateDoc, A.Revision_Date)) UnitsElapsedRevStartDate, ABS(DATEDIFF(mi, getdate(), A.DateDoc)) - ABS(DATEDIFF(mi, A.DateDoc, A.Revision_Date)) as UnitSum, A.Revision AS Revision, t.op__status AS TgtStatus FROM Master A LEFT JOIN DOCUMENTS T ON T.__ID=A.OP__ID LEFT JOIN ADMISSION B ON B.__ID=A.OP__ID WHERE A.OP__ID IN (SELECT MAX(OP__ID) FROM Master B GROUP BY OP__ID) AND B.Discharge IS NULL

@bitsmed I tried your query and it is working but not accurately and I sure do appreciate your help on this! Here let me show you my results:

As you can see here it is getting results but it is getting the Revision 1, all documents start at revision 0 (which is the initial) then it is final saved and no more ediiting can be on on that document. Instead they choose the document then go to "Review" and it creates a copy of that document and makes it Revision 1, this process gets repeated several time and the revision always is raised by 1 and so on.

I need to make it always check the MAX revision and the one right before the MAX revision.

Example: If MAX is 7 then show 6 also, etc.

This portion of "my" query will show the 2 latest revisions:

                    select top(2)
                           revision
                      from master
                     order by revision desc

When I "wrap" this in the "existing", we will get a row if highest revision is equal to the revision before. If not, we will get no rows. If you want it to return a row if highest revision is NOT equal to the revision before, you should use:

and not exists (select 1
                  from (select top(2)
                               revision
                          from master
                         order by revision desc
                       ) as a
                  having min(a.revision)=max(a.revision)-1
               )

I haven't yet had time to look through your whole query, but I'll be back shortly if I come up with a better method.

1 Like

@bitsmed When I used your code it is working but it is also giving whatever number is after the first SELECT. If I change it to 5 the result is 5, If I change it to 3 the result is 3. I am not sure as to why right now as I am still learning the logic of SQL.

select 1
from (select top(2)
revisionno
from mtp_master
order by revisionno desc
) as a
having min(a.revisionno)=max(a.revisionno)-1;

Why would it not return a 6 if the MAX(revision) = 7?

Thank you for your help!

I found that this one works but would it always work and be correct / accurate? It must align - this is the key to make sure the records and results match together.

`select top 1 max(revisionno) 
from mtp_master 
where revisionno 
NOT IN
(select max(revisionno) 
from mtp_master)`

I just realized from working with a query that this query needs to not just look for 1 max value and that it needs to find the 1 max value and the 2nd max value PER client.

@bitsmed
Here is what I have so far as you can see it is grabbing revisions but it is not working correctly for some reason.

(SELECT MAX(RevisionNo) FROM MTP_Master D WHERE D.OP__DOCID=A.OP__DOCID) AS RevisionNo,
(SELECT MAX( RevisionNo) FROM MTP_Master E WHERE E.OP__DOCID=A.OP__DOCID AND RevisionNo < (SELECT MAX(revisionno) FROM mtp_master)) AS RevisionNext,

What I need is output and as you can see it should show the max and 2nd to max per client. Not just the highest over all clients. Do you have any insight?

Thanks!

RevisionNo     RevisionNext
7                    6
4                    3
4                    3
3                    2
3                    2
select max(revision) as revisionno
      ,min(revision) as revisionnext
  from (select top(2)
               revision
          from master
         order by revision desc
       ) as a
1 Like

Got it thanks!!!