AnsweredAssumed Answered

SQL Variablevalue table query with version overwrite results

Question asked by Eero Luotio on Jun 8, 2020

I'm doing data validation for couple variables after big system migration and I'm having small issue with files that where updated with version overwrite. Basically I will have example 3 different values for configurations for same version after version overwrite.



1. File checked in with 200 configurations

2. File updated with version overwrite with 2 configurations

3. I'm getting all 200(those were already in Items_description queries earlier)  configuration values as latest variable version, though there is only anymore 2 cofigurations in Solidworks pdm datacard & Also I might have multiple "latest" version variablevalues values


Have any one found way to select correct variablevalues from version overwritten files?


Declare @variable int
Set @variable =44
--Select path to restrict selection
set @path = '\EDM_FOLDERPATH\%'


--[MyOptionsTest].[dbo].[Items_description] contains documentid & configuration id list of the files with descriptions


SELECT d.filename as Filename, dip.DocumentID, dc.ConfigurationName,d.DocumentID, 'C:\EDM_Development' + P.Path as Path, d.DocumentID, VV.Valuetext as [Description]
FROM variablevalue as VV
  inner join [MyOptionsTest].[dbo].[Items_description] as FINAL on Final.docid = vv.DocumentID
  inner join Documents as D On vv.DocumentID = d.DocumentID
  inner join DocumentsInProjects as DIP on d.DocumentID = DIP.DocumentID
  inner join DocumentConfiguration as DC ON vv.ConfigurationID = Final.Conf_id
  inner join Projects AS P on DIP.ProjectID = P.ProjectID
  inner join revisions as R on R.DocumentID = d.DocumentID


 where d.Deleted = 0 and p.Path like @path
 and R.RevNr = D.LatestRevisionNo
 ( vv.variableid = @variable)
and vv.RevisionNo = (
 Select Max( Vv1.RevisionNo )
                          From VariableValue Vv1
                               where Vv1.VariableID = Vv.VariableID And
                vv1.documentid = d.documentid and
                                Vv1.ValueText is not null and vv1.ConfigurationID = dc.ConfigurationID )
                                and (vv.VariableID = @variable)
 order by filename desc