AnsweredAssumed Answered

Help SQL query on the EPDM data base

Question asked by Jeremy Ferguson on Jul 29, 2013
Latest reply on Jul 30, 2013 by Jeremy Ferguson

Hi

This is a total SQL question but as it is relating to the EPDM database I am starting my search for help with this post.

I have an INNER JOIN on the “VariableValue” and “DocumentsInProjects” tables to get all of the document in a project.

OK to this point

I now have a list of all the Variable Value that are linked to a document ID with each variable having its own ID?

The problem that I face is that if VariableID 100 is the client address and that value has been change I have 2 instances of VariableID one with a RevisionNo value on 1 and one with a RevisionNo value of 2.

 

The big Q

How do I get only the VariableValue relating to the last revision?

THANKS

 

SELECT [VariableID]

,[VariableValue].[DocumentID]

,[VariableValue].[ProjectID]

      ,[RevisionNo]

,[ConfigurationID]

      ,[ValueText]

      ,[ValueInt]

      ,[ValueFloat]

      ,[ValueDate]

      ,[ValueCache]

      ,[IsLongText]

  FROM [PDMWE_BluewaterDMS].[dbo].[VariableValue]

  INNER JOIN [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects]

  ON [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[DocumentID]=[PDMWE_BluewaterDMS].[dbo].[VariableValue].[DocumentID]

 

  where [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[ProjectID]='895'

  and [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[Deleted]='0'

Outcomes