3 Replies Latest reply on Jul 30, 2013 12:36 PM by Jeremy Ferguson

    Help SQL query on the EPDM data base

    Jeremy Ferguson
    Jeremy Ferguson Jul 29, 2013 1:54 PM

      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'

        • Re: Help SQL query on the EPDM data base
          Jason Capriotti
          Jason Capriotti Jul 29, 2013 3:08 PM (in response to Jeremy Ferguson)

          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 )