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'
You need a select max statement on the where condition for the "RevisionNo" column.
SELECT VV.*,DOC.Filename
FROM VariableValue VV
inner join DocumentsInProjects DIP on DIP.DocumentID=VV.DocumentID
inner join Documents DOC on DOC.DocumentID=VV.DocumentID
where DIP.ProjectID=42352 and DIP.Deleted=0 and VariableID=61
and VV.RevisionNo=(Select Max( Vv1.RevisionNo ) From VariableValue Vv1
where Vv1.VariableID = Vv.VariableID And Vv1.documentid = VV.DocumentID and Vv1.ValueText is not null )