5 Replies Latest reply on Jan 24, 2018 6:31 AM by Radoslaw Markun

    SQL Query to Replicate File History

    Andre Davis

      I've written a query that mimics the "History on ..." report, except that I can't figure out where the Event column is coming from.

       

      I'm missing several Event names when I run the attached "File History.sql" query. You can run the query and compare it to a file history in your own system by setting the @filename variable at the beginning of the query.

       

      Does anyone know where I can find the missing event names? I've attached the results of my query for a file and the file history. The highlighted event on the pdf  (file history from solidworks) is an example.

        • Re: SQL Query to Replicate File History
          Andre Davis

          Additional Information:

          The reason I need to replicate the file history is to target SCRs (change Request stored as a different file) that are created just after specific events. This is the method we're using to match an SCR with an Order Line's operation when an operation number hasn't been entered on the SCR.

           

          The query example is a smaller piece to the puzzle, but an essential piece to our approach.

           

          If anyone has another approach to matching the SCR to the Order Line Operation, that would be appreciated as well.

          • Re: SQL Query to Replicate File History
            Radoslaw Markun

            Hi,

            Another solution:

            SELECT  [Name]

                   ,[Date]

                   ,[Username]

                   ,[Comment]

                   ,[TransitionInfo].[SourceStateName]

                   ,[TransitionInfo].[DestinationStateName]

                   ,[RevNr]

            FROM [MyVault].[dbo].[TransitionHistory]

            INNER JOIN [MyVault].[dbo].[Users] ON [Users].[UserID]=[TransitionHistory].[UserID]

            INNER JOIN (SELECT [TransitionID], [Transitions].[Name] ,[SourceState].[Name] AS [SourceStateName] ,[DestinationState].[Name] AS [DestinationStateName]

                          FROM [MyVault].[dbo].[Transitions]

                          INNER JOIN [MyVault].[dbo].[Status] AS [SourceState] ON [SourceState].[StatusID]=[Transitions].[Source]

                          INNER JOIN [MyVault].[dbo].[Status] AS [DestinationState] ON [DestinationState].[StatusID]=[Transitions].[Destination]) AS [TransitionInfo]

            ON [TransitionInfo].[TransitionID] = [TransitionHistory].[TransitionID]

            WHERE [TransitionHistory].[DocumentID]=00001  -- document id from [Documents]

            UNION ALL

            SELECT 'Label added:' + [Labels].[Name]

                    ,[Labels].[Date]

                    ,[Users].[Username]

                    ,[Labels].[Comment]

                    ,NULL

                    ,NULL

                    ,[RevNr]

            FROM [MyVault].[dbo].[DocumentLabels]

            INNER JOIN [MyVault].[dbo].[Labels] ON [Labels].[LabelID]=[DocumentLabels].[LabelID]

            INNER JOIN [MyVault].[dbo].[Users] ON [Users].[UserID]=[Labels].[UserID]

            WHERE [DocumentID]=00001  -- document id from [Documents]

            UNION ALL

            SELECT 'Checked In'

                    ,[Date]

                    ,[Users].[Username]

                    ,[Comment]

                    ,NULL

                    ,NULL

                    ,[RevNr]

            FROM [MyVault].[dbo].[Revisions]

            INNER JOIN [MyVault].[dbo].[Users] ON [Users].[UserID]=[Revisions].[UserID]

            WHERE [DocumentID]=00001  -- document id from [Documents]

            ORDER BY [Date] DESC

            Regards,

            Radek