DECLARE @LEANTEMP TABLE
(
ID INT IDENTITY(1,1),
SETUPDETID BIGINT ,
LEVELVALUEID BIGINT,
METRICSID BIGINT,
METRICNAME VARCHAR(250),
DIMENSIONID BIGINT,
DIMENSIONVALUEID BIGINT,
TODATE DATE
)
INSERT @LEANTEMP ( SETUPDETID, LEVELVALUEID , METRICSID , METRICNAME, DIMENSIONID , DIMENSIONVALUEID ,TODATE )
SELECT
SCHEMAMANLOG.TrnMetricsSetupDetails.SetupDetailsId,
NOTEXIST.LEVELVALUEID,
METDET.METRICSID,
METDET.MetricsName,
SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONID ,
SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONVALUEID,
NOTEXIST.TODATE
FROM @PROJECTSETUPNOTEXIST "NOTEXIST"
INNER JOIN SCHEMAMANLOG.TRNMETRICSSETUPDETAILS
ON SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.SETUPID = NOTEXIST.SETUPID
INNER JOIN SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS
ON SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.SETUPDETAILSID = SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.SETUPDETAILSID
INNER JOIN SCHEMAMANLOG.MASMETRICSDIMENSIONS
ON SCHEMAMANLOG.MASMETRICSDIMENSIONS.DIMENSIONID = SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONID
AND SCHEMAMANLOG.MASMETRICSDIMENSIONS.ISACTIVE = 1
INNER JOIN SCHEMAMANLOG.MasMetrics AS METDET
ON METDET.TagId =1
AND METDET.IsMeasure = 0
AND METDET.METRICSID = SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.METRICSID
DECLARE @COUNT INT
DECLARE @LOOPCOUNT INT
DECLARE @COLUMNNAME VARCHAR(200);
DECLARE @METRICID INT
DECLARE @QUERY VARCHAR(max);
DECLARE @Dimension INT
DECLARE @DIMENSIONVALUE BIGINT
DECLARE @SETUPDET BIGINT
DECLARE @LEVELVID BIGINT
DECLARE @TDATE DATE
SET @LOOPCOUNT = ( SELECT COUNT(ID) FROM @LEANTEMP )
SET @COUNT = 1
WHILE( @COUNT <= @LOOPCOUNT )
BEGIN
SET @COLUMNNAME = NULL
SELECT @METRICID = METRICSID,@LEVELVID = LEVELVALUEID,@TDATE=TODATE,@DIMENSION = DIMENSIONID,
@SETUPDET = SETUPDETID,@DIMENSIONVALUE = DIMENSIONVALUEID,@COLUMNNAME =METRICNAME FROM @LEANTEMP WHERE ID = @COUNT
SET @COLUMNNAME = REPLACE(@COLUMNNAME, 'trnLeanMetricsValue.', '')
IF(@DIMENSION=1 AND @METRICID in(143,144,145,146,147,149,150,151,152,153,154,155,156,157,158,195,197,200,205,206,207,208))
BEGIN
set @Query = 'UPDATE SETDIM SET SETDIM.TARGET = ISNULL('+@ColumnName+'_TARGET,SETDIM.GOAL) FROM
SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS SETDIM
INNER JOIN SCHEMAMANLOG.trnLeanMetricsValue METVAL
ON METVAL.C20PhaseID is null
AND C20ProcessDisciplineID is null
AND SETDIM.SetupDetailsId='+ CAST( @SETUPDET AS VARCHAR(10)) +'
AND SETDIM.DIMENSIONID=1
AND METVAL.LevelValueID=' + CAST( @LEVELVID AS VARCHAR(10)) +'
AND METVAL.TODATE='''+ CAST( @TDATE AS VARCHAR(12)) +''''
END
END
Here I am updating TRNMETRICSSETUPMETRICSDIMENSIONS Table.