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.
Example:
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
--SELECT WHICH VARIABLE TO GET
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
and
( 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
