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