2 Replies Latest reply on Jun 29, 2017 10:49 AM by Brad Letourneau

    Sql Query with Variable Value

    Brad Letourneau

      What I am attempting to do is create a SQL status report that shows the file name, the state, the workflow the file is in and how long it has been in this state.  I have got that to work as expected but a request has came in from Engineering to display a variable value of each of the files (order number).  I know how to query this separately but I would like to add it into this query if possible. I have very little experience with SQL queries so I am looking for some help or some reference material that may be able to help me out on this.

       

      select Distinct D.Filename, S.Name as State, W.Name as 'Workflow Name',

      CONVERT(VarChar, TH.Date, 101) AS [Entered State],

      DATEDIFF (dd, TH.Date, GETUTCDATE()) as [Days In State]

      From  Status as S inner join

            Documents as D ON D.CurrentStatusID = S.StatusID Inner Join

            Workflows as W ON W.WorkflowID = S.WorkflowID

      JOIN TransitionHistory AS TH ON D.DocumentID = TH.DocumentID and

                                    D.LatestRevisionNo-1 = TH.RevNr

      Where d.Filename not like '%test%' and d.filename not like  '%^%' and

            S.Name in ('MS R&D ECN Notified', 'B Project E% Notified', 'Manager Approval',

                     'Drafting Manager Assigns', 'In Progress', 'Engineer Approval',

                     'MFG Notifed', 'MFG Engineer Approval', 'QA Notified', 'Quality Approval',

                     'Doc Control Approval', 'Drafter Creates Documents', 'Checking Approval',

              'eCheck', 'Drafting Manager Sign Off')and

                     W.Name IN ('MS_Orders', 'MS_Checking')

       

       

      Order By Filename