AnsweredAssumed Answered

Help - update SQL query to add variable

Question asked by Paul Marsman on Feb 13, 2019
Latest reply on Feb 13, 2019 by Charley Saint

I currently have an excel file that uses an SQL query to get the data.  I would like to add the variable "Date Promised" (ID#: 170) to the data table but unable to find a way with my very limited SQL query writing experience.  Could you help add it to this query?

 

SELECT Projects.Path + Documents.Filename as [File Name],

TransitionHistory.DocumentID,

TransitionHistory.TransitionNr,

TransitionHistory.RevNr as [Version],

DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),TransitionHistory.Date) as [Date],

Users.Username,

Workflows.Name + '.' + Transitions.Name as [Workflow and Transition Name],

S2.Name as [From State],

S1.Name as [To State],

TransitionHistory.Comment

FROM TransitionHistory

  Inner Join Documents on Documents.DocumentID = TransitionHistory.DocumentID

  Inner Join Transitions on Transitions.TransitionID = TransitionHistory.TransitionID

  Inner Join Users on Users.UserID = TransitionHistory.UserID

  Join Status S1 on S1.StatusID = Transitions.Destination

  Join Status S2 on S2.StatusID = Transitions.Source

  Join Workflows on Workflows.WorkflowID = Transitions.WorkflowID

  Join DocumentsInProjects on TransitionHistory.DocumentID = DocumentsInProjects.DocumentID

  Join Projects on DocumentsInProjects.ProjectID = Projects.ProjectID

WHERE NOT S1.Name IS NULL and NOT S2.Name IS NULL --Exclude trantions to and from the initial state

AND Projects.Path like '\Projects\%'   --<<< Update with full path relative to vault root, % = wildcard

AND Documents.Filename like '%.slddrw'

AND Documents.Deleted = 0

ORDER BY [File Name], TransitionHistory.TransitionNr

Outcomes