July 5, 2012 at 11:24 pm
HI Guys,
Please help, I need to update multiple fields in MEAS table based on Metrics ID from LEAN Table, so i have used below query, but the below query is not updating, I am getting only null value can u please help
UPDATE MEAS
SET MEAS.TSPP_PERFORMANCE = (CASE when LEAN.METRICSID=195 THEN MCD.PERFORMANCE ELSE MEAS.TSPP_Performance END),
MEAS.PCE_PERFORMANCE = (CASE when LEAN.METRICSID=143 THEN MCD.PERFORMANCE ELSE MEAS.PCE_PERFORMANCE END),
MEAS.ADL_PERFORMANCE = (CASE when LEAN.METRICSID=144 THEN MCD.PERFORMANCE ELSE MEAS.ADL_PERFORMANCE END),
MEAS.ADLS1_PERFORMANCE = (CASE when LEAN.METRICSID=145 THEN MCD.PERFORMANCE ELSE MEAS.ADLS1_PERFORMANCE END),
MEAS.ADLS2_PERFORMANCE = (CASE when LEAN.METRICSID=146 THEN MCD.PERFORMANCE ELSE MEAS.ADLS2_PERFORMANCE END),
MEAS.EV_PERFORMANCE = (CASE when LEAN.METRICSID=147 THEN MCD.PERFORMANCE ELSE MEAS.EV_PERFORMANCE END),
MEAS.ADLS3_PERFORMANCE = (CASE when LEAN.METRICSID=148 THEN MCD.PERFORMANCE ELSE MEAS.ADLS3_PERFORMANCE END),
MEAS.EDR_PERFORMANCE = (CASE when LEAN.METRICSID=149 THEN MCD.PERFORMANCE ELSE MEAS.EDR_PERFORMANCE END),
MEAS.RSI_PERFORMANCE = (CASE when LEAN.METRICSID=150 THEN MCD.PERFORMANCE ELSE MEAS.RSI_PERFORMANCE END),
MEAS.TCDP_PERFORMANCE = (CASE when LEAN.METRICSID=151 THEN MCD.PERFORMANCE ELSE MEAS.TCDP_PERFORMANCE END),
MEAS.TCEP_PERFORMANCE = (CASE when LEAN.METRICSID=152 THEN MCD.PERFORMANCE ELSE MEAS.TCEP_PERFORMANCE END),
MEAS.LF_PERFORMANCE = (CASE when LEAN.METRICSID=153 THEN MCD.PERFORMANCE ELSE MEAS.LF_PERFORMANCE END),
MEAS.CPUOM_PERFORMANCE = (CASE when LEAN.METRICSID=154 THEN MCD.PERFORMANCE ELSE MEAS.CPUOM_PERFORMANCE END),
MEAS.RDL_PERFORMANCE = (CASE when LEAN.METRICSID=155 THEN MCD.PERFORMANCE ELSE MEAS.RDL_PERFORMANCE END),
MEAS.SV_PERFORMANCE = (CASE when LEAN.METRICSID=156 THEN MCD.PERFORMANCE ELSE MEAS.SV_PERFORMANCE END),
MEAS.TCPP_PERFORMANCE = (CASE when LEAN.METRICSID=157 THEN MCD.PERFORMANCE ELSE MEAS.TCPP_PERFORMANCE END),
MEAS.TE_PERFORMANCE = (CASE when LEAN.METRICSID=158 THEN MCD.PERFORMANCE ELSE MEAS.TE_PERFORMANCE END),
MEAS.PRE_PERFORMANCE = (CASE when LEAN.METRICSID=197 THEN MCD.PERFORMANCE ELSE MEAS.PRE_PERFORMANCE END),
MEAS.COQ_PERFORMANCE = (CASE when LEAN.METRICSID=200 THEN MCD.PERFORMANCE ELSE MEAS.COQ_PERFORMANCE END),
MEAS.ACOQ_PERFORMANCE = (CASE when LEAN.METRICSID=205 THEN MCD.PERFORMANCE ELSE MEAS.ACOQ_PERFORMANCE END),
MEAS.PCOQ_PERFORMANCE = (CASE when LEAN.METRICSID=206 THEN MCD.PERFORMANCE ELSE MEAS.PCOQ_PERFORMANCE END),
MEAS.FCOQ_PERFORMANCE = (CASE when LEAN.METRICSID=207 THEN MCD.PERFORMANCE ELSE MEAS.FCOQ_PERFORMANCE END),
MEAS.PREEFF_PERFORMANCE = (CASE when LEAN.METRICSID=208 THEN MCD.PERFORMANCE ELSE MEAS.PREEFF_PERFORMANCE END),
MEAS.ADD_PERFORMANCE = (CASE when LEAN.METRICSID=214 THEN MCD.PERFORMANCE ELSE MEAS.ADD_PERFORMANCE END),
MEAS.ADDS1_PERFORMANCE = (CASE when LEAN.METRICSID=218 THEN MCD.PERFORMANCE ELSE MEAS.ADDS1_PERFORMANCE END),
MEAS.ADDS2_PERFORMANCE = (CASE when LEAN.METRICSID=219 THEN MCD.PERFORMANCE ELSE MEAS.ADDS2_PERFORMANCE END),
MEAS.RDD_PERFORMANCE = (CASE when LEAN.METRICSID=220 THEN MCD.PERFORMANCE ELSE MEAS.RDD_PERFORMANCE END),
MEAS.SVTD_PERFORMANCE = (CASE when LEAN.METRICSID=221 THEN MCD.PERFORMANCE ELSE MEAS.SVTD_PERFORMANCE END),
MEAS.SVTE_PERFORMANCE = (CASE when LEAN.METRICSID=225 THEN MCD.PERFORMANCE ELSE MEAS.SVTE_PERFORMANCE END),
MEAS.TC_PERFORMANCE = (CASE when LEAN.METRICSID=229 THEN MCD.PERFORMANCE ELSE MEAS.TC_PERFORMANCE END),
MEAS.EC_PERFORMANCE = (CASE when LEAN.METRICSID=230 THEN MCD.PERFORMANCE ELSE MEAS.EC_PERFORMANCE END),
MEAS.TED_PERFORMANCE = (CASE when LEAN.METRICSID=234 THEN MCD.PERFORMANCE ELSE MEAS.TED_PERFORMANCE END),
MEAS.EPM_PERFORMANCE = (CASE when LEAN.METRICSID=237 THEN MCD.PERFORMANCE ELSE MEAS.EPM_PERFORMANCE END),
MEAS.RME_PERFORMANCE = (CASE when LEAN.METRICSID=239 THEN MCD.PERFORMANCE ELSE MEAS.RME_PERFORMANCE END),
MEAS.CDS_PERFORMANCE = (CASE when LEAN.METRICSID=242 THEN MCD.PERFORMANCE ELSE MEAS.CDS_PERFORMANCE END),
MEAS.RDDE_PERFORMANCE = (CASE when LEAN.METRICSID=244 THEN MCD.PERFORMANCE ELSE MEAS.RDDE_PERFORMANCE END)
FROM schemamanlog.trnleanmetricsperformance "MEAS"
INNER JOIN @METRICCOLORDETAILS MCD
ON MCD.DIMENSIONID = 1
AND MCD.SETUPID = MEAS.SetupId
inner join @LEANTEMP LEAN
ON
MCD.METRICID=LEAN.METRICSID
WHERE MEAS.c20phaseid IS NULL
AND MEAS.c20processdisciplineid IS NOT NULL
AND MEAS.c20processdisciplineid = LEAN.PDID
AND MEAS.levelvalueID= @LEVELVALUEID
AND MEAS.fromdate = @FROMDATE
AND MEAS.todate = @TODATE
AND MEAS.isActive = 1
July 6, 2012 at 12:17 am
Execute the below mentioned query
I had created a SELECT query from your UPDATE query
This results should give you an idea of what is happening
SELECTLEAN.METRICSID, MCD.PERFORMANCE, MEAS.*
FROM schemamanlog.trnleanmetricsperformance "MEAS"
INNER JOIN @METRICCOLORDETAILS MCD
ON MCD.DIMENSIONID = 1
AND MCD.SETUPID = MEAS.SetupId
inner join @LEANTEMP LEAN
ON MCD.METRICID=LEAN.METRICSID
WHERE MEAS.c20phaseid IS NULL
AND MEAS.c20processdisciplineid IS NOT NULL
AND MEAS.c20processdisciplineid = LEAN.PDID
AND MEAS.levelvalueID= @LEVELVALUEID
AND MEAS.fromdate = @FROMDATE
AND MEAS.todate = @TODATE
AND MEAS.isActive = 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2012 at 12:29 am
Hi I know what is happening, but i need to update fields in MEAS table based on Metric ID, the update statement which i have provided is not updating correctly, I dont know why. Can you please help in update statement.
July 6, 2012 at 12:42 am
vijayarani87.s (7/6/2012)
Hi I know what is happening, but i need to update fields in MEAS table based on Metric ID, the update statement which i have provided is not updating correctly, I dont know why. Can you please help in update statement.
We will need the logic of the UPDATE to check your query
Explain your UPDATE logic and also provide some sample data along with DDL and the expected results
This will help us to give you tested answers
If you don't know how to do this, please check the link in my signature
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2012 at 12:55 am
THIS is the lean TEMP table
DECLARE @LEANTEMP TABLE
(
ID INT IDENTITY(1,1),
TYPE INT,
GROUPID BIGINT,
SETUPID BIGINT,
LEVELVALUEID bIGINT,
METRICSID BIGINT,
METRICSNAME VARCHAR(150),
DIMENSIONID BIGINT,
DIMENSIONVALUEID BIGINT,
METRICSVALUE FLOAT,
GOAL FLOAT,
SETUPUOM VARCHAR(200),
UOM VARCHAR(300),
PERFORMANCE VARCHAR(25),
TOOLTIP VARCHAR(100),
TARGET Float ,
PDID BIGINT,
PHASEID BIGINT,
SORTORDER INT
)
HERE is the METRICCOLOR DETAILS TABLE
DECLARE @METRICCOLORDETAILS TABLE
(
ID INT IDENTITY(1,1),
SETUPID BIGINT,
DIMENSIONID INT,
PERFORMANCE varchar(50),
METRICID INT
)
THis is tenleanmeteric performanceTable
CREATE TABLE [SCHEMAMANLOG].[trnLeanMetricsPerformance](
[FrequencyID] [bigint] NOT NULL,
[FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL,
[SetupId] [bigint] NOT NULL,
[LevelValueID] [bigint] NOT NULL,
[FunctionalModelDimensionLevel0ID] [bigint] NULL,
[ReleaseModelDimensionLevel0ID] [bigint] NULL,
[C20ReleaseModelID] [bigint] NULL,
[C20FunctionalModelID] [bigint] NULL,
[PhaseDimensionLevel0ID] [bigint] NULL,
[C20PhaseID] [bigint] NULL,
[C20PhaseName] [nvarchar](200) NULL,
[ProcessDisciplineLevel0ID] [bigint] NULL,
[C20ProcessDisciplineID] [bigint] NULL,
[C20ProcessDisciplineName] [nvarchar](200) NULL,
[WorkTypeDimensionLevel0ID] [bigint] NULL,
[C20WorkTypeID] [bigint] NULL,
[C20WorkTypeName] [nvarchar](200) NULL,
[SolutionType] [nvarchar](200) NULL,
[LevelValueName] [nvarchar](200) NULL,
[LevelID] [bigint] NULL,
[LevelName] [nvarchar](200) NULL,
[OrganizationHierarchyID] [bigint] NULL,
[OrganizationHierarchyName] [nvarchar](200) NULL,
[OrganizationUnitID] [bigint] NULL,
[OrganizationUnitName] [nvarchar](200) NULL,
[TCDP_Performance] varchar(25) NULL,
[TCEP_Performance] varchar(25) NULL,
[TSPP_Performance] varchar(25) NULL,
[TCPP_Performance] varchar(25) NULL,
[SV_Performance] varchar(25) NULL,
[EV_Performance] varchar(25) NULL,
[ADL_Performance] varchar(25) NULL,
[ADLS1_Performance] varchar(25) NULL,
[ADLS2_Performance] varchar(25) NULL,
[ADLS3_Performance] varchar(25) NULL,
[RDL_Performance] varchar(25) NULL,
[EDR_Performance] varchar(25) NULL,
[TE_Performance] varchar(25) NULL,
[LF_Performance] varchar(25) NULL,
[RSI_Performance] varchar(25) NULL,
[CPUOM_Performance] varchar(25) NULL,
[PCE_Performance] varchar(25) NULL,
[PREEFF_Performance] varchar(25) NULL,
[PRE_Performance] varchar(25) NULL,
[COQ_Performance] varchar(25) NULL,
[ACOQ_Performance] varchar(25) NULL,
[PCOQ_Performance] varchar(25) NULL,
[FCOQ_Performance] varchar(25) NULL,
[ADD_Performance] varchar(25) NULL,
[ADDS1_Performance] varchar(25) NULL,
[ADDS2_Performance] varchar(25) NULL,
[RDD_Performance] varchar(25) NULL,
[SVTD_Performance] varchar(25) NULL,
[SVTE_Performance] varchar(25) NULL,
[TC_Performance] varchar(25) NULL,
[EC_Performance] varchar(25) NULL,
[TED_Performance] varchar(25) NULL,
[EPM_Performance] varchar(25) NULL,
[RME_Performance] varchar(25) NULL,
[CDS_Performance] varchar(25) NULL,
[RDDE_Performance] varchar(25) NULL,
[IsActive] [bit] NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
) ON [PRIMARY]
I need to update performance for each column in metric performance table based on the metricID. I am taking the performance from Metric COlor Details table. I need to update the metric ID which is in the LEan TEmp Table. So i am using LEan TEmp Table here.
July 6, 2012 at 12:57 am
Currently my updte statement is updating only for the first metric ID in the lean table
July 6, 2012 at 1:31 am
pls check your where class values .
you are send values correct or not.
July 6, 2012 at 2:16 am
vijayarani87.s (7/6/2012)
Currently my updte statement is updating only for the first metric ID in the lean table
Can you tell how many rows are being returned by the SELECT query that I had given earlier
If it is returning more than 1 row, can you attach the results of that query in the forum as an Excel sheet
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2012 at 2:47 am
That will return 118 rows here is the record for that
IDTYPEGROUPIDSETUPIDLEVELVALUEIDMETRICSIDMETRICSNAMEDIMENSIONIDDIMENSIONVALUEIDMETRICSVALUEGOALSETUPUOMUOMPERFORMANCETOOLTIPTARGETPDIDPHASEIDSORTORDER
11116313145143% Core Effort1143NULL1004NULLNULL3100NULLNULL1
21216313145144Application Defect Leakage %114415.751004NULLNULL2100NULLNULL1
31216313145145Application Defect Leakage by Severity S1%11454.111004NULLNULL2100NULLNULL1
41216313145145System Test Execution429630701004NULLNULL2100NULL2963071
51216313145145System Integration Test Execution429630801004NULLNULL2100NULL2963081
61216313145145Regression Test Suite Execution429630901004NULLNULL2100NULL2963091
71216313145146Application Defect Leakage by Severity S2 %11466.161004NULLNULL2100NULLNULL1
81216313145146System Test Execution429630701004NULLNULL2100NULL2963071
91216313145146System Integration Test Execution429630801004NULLNULL2100NULL2963081
101216313145146Regression Test Suite Execution429630901004NULLNULL2100NULL2963091
111116313145147Effort Variation1147NULL1004NULLNULL1100NULLNULL1
121116313145147Definition4296297NULL1004NULLNULL1100NULL2962971
131116313145147Project Planning4296298NULL1004NULLNULL1100NULL2962981
141116313145147Knowledge Transition4296299NULL1004NULLNULL1100NULL2962991
151116313145147Test Requirements4296300NULL1004NULLNULL1100NULL2963001
161116313145147Test Planning4296301NULL1004NULLNULL1100NULL2963011
171116313145147System Test Design4296302NULL1004NULLNULL1100NULL2963021
181116313145147System Integration Test Design4296303NULL1004NULLNULL1100NULL2963031
191116313145147System Test Development4296304NULL1004NULLNULL1100NULL2963041
201116313145147System Integration Test Development4296305NULL1004NULLNULL1100NULL2963051
211116313145147Regression Test Suite Development4296306NULL1004NULLNULL1100NULL2963061
221116313145147System Test Execution4296307NULL1004NULLNULL1100NULL2963071
231116313145147System Integration Test Execution4296308NULL1004NULLNULL1100NULL2963081
241116313145147Regression Test Suite Execution4296309NULL1004NULLNULL1100NULL2963091
251116313145147Test Delivery4296310NULL1004NULLNULL1100NULL2963101
261116313145147Acceptance Testing4296311NULL1004NULLNULL1100NULL2963111
271116313145147Project Tracking4296312NULL1004NULLNULL1100NULL2963121
281116313145147Project Closure4296313NULL1004NULLNULL1100NULL2963131
291216313145149Error Discovery Rate11495.861004NULLNULL1100NULLNULL1
301216313145149System Test Execution42963073.891004NULLNULL1100NULL2963071
311216313145149System Integration Test Execution429630811.51004NULLNULL1100NULL2963081
321216313145149Regression Test Suite Execution42963095.251004NULLNULL1100NULL2963091
331216313145150RSI11502.561004NULLNULL2100NULLNULL1
341116313145151Test Design Productivity1151NULL1004NULLNULL3100NULLNULL1
351116313145151System Test Development4296304NULL1004NULLNULL3100NULL2963041
361116313145151System Integration Test Development4296305NULL1004NULLNULL3100NULL2963051
371116313145151Regression Test Suite Development4296306NULL1004NULLNULL3100NULL2963061
381116313145152Test Execution Productivity1152NULL1004NULLNULL3100NULLNULL1
391116313145152System Test Execution4296307NULL1004NULLNULL3100NULL2963071
401116313145152System Integration Test Execution4296308NULL1004NULLNULL3100NULL2963081
411116313145152Regression Test Suite Execution4296309NULL1004NULLNULL3100NULL2963091
421116313145153Load Factor115301004NULLNULL1100NULLNULL1
431116313145154Cost per Unit of Measure11540.071004NULLNULL1100NULLNULL1
441216313145155Review Defect Leakage %1155NULL1004NULLNULL2100NULLNULL1
451216313145155Knowledge Transition319NULL1004NULLNULL210019NULL1
461216313145155Planning and Strategy327NULL1004NULLNULL210027NULL1
471216313145155Test Requirements328NULL1004NULLNULL210028NULL1
481216313145155Test Design330NULL1004NULLNULL210030NULL1
491116313145156Schedule Variation1156NULL1004NULLNULL1100NULLNULL1
501116313145156Definition4296297NULL1004NULLNULL1100NULL2962971
511116313145156Project Planning4296298NULL1004NULLNULL1100NULL2962981
521116313145156Knowledge Transition4296299NULL1004NULLNULL1100NULL2962991
531116313145156Test Requirements4296300NULL1004NULLNULL1100NULL2963001
541116313145156Test Planning4296301NULL1004NULLNULL1100NULL2963011
551116313145156System Test Design4296302NULL1004NULLNULL1100NULL2963021
561116313145156System Integration Test Design4296303NULL1004NULLNULL1100NULL2963031
571116313145156System Test Development4296304NULL1004NULLNULL1100NULL2963041
581116313145156System Integration Test Development4296305NULL1004NULLNULL1100NULL2963051
591116313145156Regression Test Suite Development4296306NULL1004NULLNULL1100NULL2963061
601116313145156System Test Execution4296307NULL1004NULLNULL1100NULL2963071
611116313145156System Integration Test Execution4296308NULL1004NULLNULL1100NULL2963081
621116313145156Regression Test Suite Execution4296309NULL1004NULLNULL1100NULL2963091
631116313145156Test Delivery4296310NULL1004NULLNULL1100NULL2963101
641116313145156Acceptance Testing4296311NULL1004NULLNULL1100NULL2963111
651116313145156Project Tracking4296312NULL1004NULLNULL1100NULL2963121
661116313145156Project Closure4296313NULL1004NULLNULL1100NULL2963131
671116313145157Test Preparation Productivity1157NULL1004NULLNULL3100NULLNULL1
681116313145157System Test Development4296304NULL1004NULLNULL3100NULL2963041
691116313145157System Integration Test Development4296305NULL1004NULLNULL3100NULL2963051
701116313145157Regression Test Suite Development4296306NULL1004NULLNULL3100NULL2963061
711216313145158Test effectiveness115877.41004NULLNULL1100NULLNULL1
721116313145195Test Script Preparation Productivity11950.891004NULLNULL3100NULLNULL1
731216313145197% Rework Effort1197NULL1004NULLNULL2100NULLNULL1
741216313145197Knowledge Transition4296299NULL1004NULLNULL2100NULL2962991
751216313145197Test Requirements4296300NULL1004NULLNULL2100NULL2963001
761216313145197Test Planning4296301NULL1004NULLNULL2100NULL2963011
771216313145197System Test Design4296302NULL1004NULLNULL2100NULL2963021
781216313145197System Integration Test Design4296303NULL1004NULLNULL2100NULL2963031
791216313145197System Test Development4296304NULL1004NULLNULL2100NULL2963041
801216313145197System Integration Test Development4296305NULL1004NULLNULL2100NULL2963051
811216313145197System Test Execution4296307NULL1004NULLNULL2100NULL2963071
821216313145197System Integration Test Execution4296308NULL1004NULLNULL2100NULL2963081
831216313145197Regression Test Suite Execution4296309NULL1004NULLNULL2100NULL2963091
841216313145197Test Delivery4296310NULL1004NULLNULL2100NULL2963101
851216313145200Cost of Quality %1200NULL1004NULLNULL2100NULLNULL1
861216313145205Appraisal Cost of Quality %1205NULL1004NULLNULL2100NULLNULL1
871216313145206Prevention Cost of Quality %1206NULL1004NULLNULL2100NULLNULL1
881216313145207Failure Cost of Quality %1207NULL1004NULLNULL2100NULLNULL1
891216313145208% Review Efficiency120855.911004NULLNULL1100NULLNULL1
901216313145208Knowledge Transition31958.331004NULLNULL110019NULL1
911216313145208Planning and Strategy32755.561004NULLNULL110027NULL1
921216313145208Test Requirements32845.831004NULLNULL110028NULL1
931216313145208Test Design33066.671004NULLNULL110030NULL1
941216313145214Application Defect Density by Size12144.11004NULLNULL2100NULLNULL1
951216313145218Application Defect Density by Size for S1 application defects12181.31004NULLNULL2100NULLNULL1
961216313145219Application Defect Density by Size for S2 application defects12191.41004NULLNULL2100NULLNULL1
971216313145220Review Defect Density by Size1220136.841004NULLNULL2100NULLNULL1
981116313145221Size Variation % - Test Design122111.761004NULLNULL2100NULLNULL1
991116313145221System Test Development429630412.51004NULLNULL2100NULL2963041
1001116313145221System Integration Test Development4296305-42.861004NULLNULL2100NULL2963051
1011116313145221Regression Test Suite Development4296306-16.671004NULLNULL2100NULL2963061
1021116313145225Size Variation % - Test Execution12253.451004NULLNULL2100NULLNULL1
1031116313145225System Test Execution429630701004NULLNULL2100NULL2963071
1041116313145225System Integration Test Execution429630801004NULLNULL2100NULL2963081
1051116313145225Regression Test Suite Execution4296309-37.51004NULLNULL2100NULL2963091
1061216313145229Test Coverage %1229103.451004NULLNULL3100NULLNULL1
1071216313145229System Test Execution42963071001004NULLNULL3100NULL2963071
1081216313145229System Integration Test Execution42963081001004NULLNULL3100NULL2963081
1091216313145229Regression Test Suite Execution429630962.51004NULLNULL3100NULL2963091
1101216313145230% Execution Complete1230115.631004NULLNULL3100NULLNULL1
1111216313145230System Test Execution42963074501004NULLNULL3100NULL2963071
1121216313145230System Integration Test Execution4296308133.331004NULLNULL3100NULL2963081
1131216313145230Regression Test Suite Execution4296309114.291004NULLNULL3100NULL2963091
1141116313145234Test Environment Downtime %1234NULL1004NULLNULL2100NULLNULL1
1151116313145237% Effort for Project Management1237NULL1004NULLNULL2100NULLNULL1
1161216313145239Risk Mitigation Effectiveness123901004NULLNULL3100NULLNULL1
1171116313145242Code Delivery Slippage1242NULL1004NULLNULL2100NULLNULL1
1181216313145244% Requirements defect detection effectiveness12441501004NULLNULL3100NULLNULL1
July 6, 2012 at 2:58 am
vijayarani87.s (7/6/2012)
Currently my updte statement is updating only for the first metric ID in the lean table
Of course. Only one row from LEAN will be used to update one row in MEAS, even if several rows in LEAN match - this is not a "deterministic update". SQL Server won't cycle through all matching rows in LEAN updating one row in MEAS, as this code expects.
You have two choices here:
1.Join and pivot the rowwise data in @METRICCOLORDETAILS and @LEANTEMP to provide columnwise data matching the columns in MEAS
2.Update one data element at a time in MEAS, using a filter on LEAN.METRICSID
I'd choose option 1 because option 2 will result in about 30 updates of MEAS, and also because @METRICCOLORDETAILS and @LEANTEMP are work tables - make the join/pivot part of the process which creates them.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply