4 Replies Latest reply on Mar 14, 2013 11:54 AM by Brian Dalton

    SQL Question: Get Date when File entered Current State

    Brian Dalton

      I need to be able to show when a file entered its current state (when it was last transitioned) to display the 'age' of the file in that state. Using SQL.

       

      The following query (with document and state hard-coded for simplicity) works fine in most cases.

       

      SELECT * FROM TransitionHistory TH2

      INNER JOIN Transitions T ON TH2.TransitionID = T.TransitionID

      WHERE TH2.DocumentID = 79360

      AND T.Destination = 38

       

      Sometimes, however, a file enters the same state multiple times (because of loops) and/or through multiple transitions (because of different paths through the loop).  In such cases the query returns multiple results for the same file.  I want to filter it so that it only shows the most recent transition into the current state.  I tried this:

       

      SELECT * FROM TransitionHistory TH2

      INNER JOIN Transitions T ON TH2.TransitionID = T.TransitionID

      WHERE TH2.DocumentID = 79360

      AND T.Destination = 38

       

      AND TH2.Date = (SELECT MAX(TH2a.Date)

                          FROM TransitionHistory TH2a

                          WHERE TH2a.TransitionID = T.TransitionID

                          AND TH2a.DocumentID = 79360)                   

       

      But it makes no difference, and I continue to get multiple results.  I've spent 3 hours combing through it and trying stuff (I tried using TransitionNr instead of Date) but I can't see any problem.  I hope someone can spot my (probably obvious) mistake and help me to discover the key to making this query work.

       

      Thanks.

        • Re: SQL Question: Get Date when File entered Current State
          Lee CS Young

          How about this?

           

          SELECT TOP 1 *
          FROM TransitionHistory th
          INNER JOIN Transitions t ON t.TransitionID = th.TransitionID
          WHERE th.DocumentID = 79360
              AND t.Destination = 38
          ORDER BY DATE DESC
          
            • Re: SQL Question: Get Date when File entered Current State
              Brian Dalton

              That's a clever way of addressing the problem that I haven't thought of, and it works great in my little test query I posted.

               

              A potential problem is that my actual query would not be hard-coded to a single document, but will instead return a set of records for a selection of documents based on other criteria, which are already sorted using ORDER BY.  I'm not sure how I would implement your suggestion in a multi-document scenario.  Here's my full query:

               

              DECLARE @DCRList TABLE (DocumentID INT)

              INSERT INTO @DCRList

              SELECT D.DocumentID FROM Documents D

              INNER JOIN Status S ON D.CurrentStatusID = S.StatusID

              INNER JOIN VariableValue VV ON D.DocumentID = VV.DocumentID -- for DocumentType variable

              WHERE (S.Name <> 'DCR Approved' AND S.Name <> 'Killed' AND S.Name <> '')

              AND (D.Deleted = 0)

              AND (VV.VariableID = 141)

              AND (VV.ValueCache = 'XL_DCR')

              AND (VV.RevisionNo = (SELECT MAX(VVa.RevisionNo)

                                                  FROM VariableValue VVa

                                                  WHERE VVa.DocumentID = D.DocumentID

                                                  AND (VVa.VariableID = 141)))

               

              SELECT T.TransitionID, D.DocumentID, LEFT(D.Filename,8) AS [DCR No],

                          U.Initials AS [Initiator], TH2.Date,

                          S.Name AS [Current State], DATEDIFF(day, TH2.Date, SYSDATETIME()) AS [Days Waiting],

                          S.StatusID

              FROM Documents D

              INNER JOIN @DCRList DCR ON D.DocumentID = DCR.DocumentID            --filter Documents by @DCRList

              LEFT JOIN TransitionHistory TH ON TH.DocumentID = D.DocumentID        --for displaying Initiator Initials

              LEFT JOIN Users U ON TH.UserID = U.UserID                            --for displaying Initiator Initials

              LEFT JOIN Status S ON D.CurrentStatusID = S.StatusID                --for displaying Current State

              INNER JOIN Transitions T ON T.Destination = S.StatusID                --get Transition that led to Current State

              INNER JOIN TransitionHistory TH2 ON TH2.TransitionID = T.TransitionID

               

              WHERE TH.TransitionNr = 1

              AND TH2.DocumentID = D.DocumentID

              ORDER BY [DCR No]

               

              If the current state has more than one transition leading into it, Transitions T yields multiple record (multiple destinations), therefore TransitionHistory TH2 yields multiple records.  I have no way of knowing from the Transitions table which transition was used last, but the Date and TransitionNr fields in the TransitionHistory table would both be useful.  How would I apply your concept to this multi-document query?

                • Re: SQL Question: Get Date when File entered Current State
                  Ravi Teja

                  Brian,

                   

                  Following example should help you solve your problem

                   

                  ---------------------------------------------------------------------------------------------------------------------------------------

                   

                  DECLARE @A TABLE( Transition VARCHAR(50), PartNo Varchar(50), Date DATE)

                   

                   

                  INSERT  @A

                  SELECT  'For_Approval' Transition, '123' PartNo, '2013-03-14' Date  UNION ALL

                  SELECT  'For_Review', '123', '2013-03-16' UNION ALL

                  SELECT  'For_ECN', '123', '2013-03-14' UNION ALL

                  SELECT  'For_Review', '456', '2013-03-16' UNION ALL

                  SELECT  'For_ECN', '456', '2013-03-14' UNION ALL

                  SELECT  'For_Change', '456', '2013-03-14'

                   

                   

                  --get data in Temp Table

                  SELECT * FROM @A

                   

                   

                   

                   

                   

                  --below query will get the highest date for each part number

                  ;WITH cte AS

                  (

                     SELECT *,

                           ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY Date DESC) AS rn

                     FROM @A

                  )

                  SELECT *

                  FROM cte

                  WHERE rn = 1

                  ------------------------------------------------------------------------------------------------------------------------------------------

                   

                   

                   

                  execute above query and compare the resulting tables

                   

                   

                  Regards

                  Ravi T

              • Re: SQL Question: Get Date when File entered Current State
                Brian Dalton

                Whew!!  Finally figured it out!

                 

                I just needed to delete one line:

                 

                SELECT * FROM TransitionHistory TH2

                INNER JOIN Transitions T ON TH2.TransitionID = T.TransitionID

                WHERE TH2.DocumentID = 79360

                AND T.Destination = 38

                 

                AND TH2.Date = (SELECT MAX(TH2a.Date)

                                    FROM TransitionHistory TH2a

                                    WHERE TH2a.TransitionID = T.TransitionID  <---- KILL THIS LINE

                                    AND TH2a.DocumentID = 79360)

                 

                Thanks to all who replied.