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
SELECT LEAN.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
ID TYPE GROUPID SETUPID LEVELVALUEID METRICSID METRICSNAME DIMENSIONID DIMENSIONVALUEID METRICSVALUE GOAL SETUPUOM UOM PERFORMANCE TOOLTIP TARGET PDID PHASEID SORTORDER
1 1 1 163 13145 143 % Core Effort 1 143 NULL 100 4 NULL NULL 3 100 NULL NULL 1
2 1 2 163 13145 144 Application Defect Leakage % 1 144 15.75 100 4 NULL NULL 2 100 NULL NULL 1
3 1 2 163 13145 145 Application Defect Leakage by Severity S1% 1 145 4.11 100 4 NULL NULL 2 100 NULL NULL 1
4 1 2 163 13145 145 System Test Execution 4 296307 0 100 4 NULL NULL 2 100 NULL 296307 1
5 1 2 163 13145 145 System Integration Test Execution 4 296308 0 100 4 NULL NULL 2 100 NULL 296308 1
6 1 2 163 13145 145 Regression Test Suite Execution 4 296309 0 100 4 NULL NULL 2 100 NULL 296309 1
7 1 2 163 13145 146 Application Defect Leakage by Severity S2 % 1 146 6.16 100 4 NULL NULL 2 100 NULL NULL 1
8 1 2 163 13145 146 System Test Execution 4 296307 0 100 4 NULL NULL 2 100 NULL 296307 1
9 1 2 163 13145 146 System Integration Test Execution 4 296308 0 100 4 NULL NULL 2 100 NULL 296308 1
10 1 2 163 13145 146 Regression Test Suite Execution 4 296309 0 100 4 NULL NULL 2 100 NULL 296309 1
11 1 1 163 13145 147 Effort Variation 1 147 NULL 100 4 NULL NULL 1 100 NULL NULL 1
12 1 1 163 13145 147 Definition 4 296297 NULL 100 4 NULL NULL 1 100 NULL 296297 1
13 1 1 163 13145 147 Project Planning 4 296298 NULL 100 4 NULL NULL 1 100 NULL 296298 1
14 1 1 163 13145 147 Knowledge Transition 4 296299 NULL 100 4 NULL NULL 1 100 NULL 296299 1
15 1 1 163 13145 147 Test Requirements 4 296300 NULL 100 4 NULL NULL 1 100 NULL 296300 1
16 1 1 163 13145 147 Test Planning 4 296301 NULL 100 4 NULL NULL 1 100 NULL 296301 1
17 1 1 163 13145 147 System Test Design 4 296302 NULL 100 4 NULL NULL 1 100 NULL 296302 1
18 1 1 163 13145 147 System Integration Test Design 4 296303 NULL 100 4 NULL NULL 1 100 NULL 296303 1
19 1 1 163 13145 147 System Test Development 4 296304 NULL 100 4 NULL NULL 1 100 NULL 296304 1
20 1 1 163 13145 147 System Integration Test Development 4 296305 NULL 100 4 NULL NULL 1 100 NULL 296305 1
21 1 1 163 13145 147 Regression Test Suite Development 4 296306 NULL 100 4 NULL NULL 1 100 NULL 296306 1
22 1 1 163 13145 147 System Test Execution 4 296307 NULL 100 4 NULL NULL 1 100 NULL 296307 1
23 1 1 163 13145 147 System Integration Test Execution 4 296308 NULL 100 4 NULL NULL 1 100 NULL 296308 1
24 1 1 163 13145 147 Regression Test Suite Execution 4 296309 NULL 100 4 NULL NULL 1 100 NULL 296309 1
25 1 1 163 13145 147 Test Delivery 4 296310 NULL 100 4 NULL NULL 1 100 NULL 296310 1
26 1 1 163 13145 147 Acceptance Testing 4 296311 NULL 100 4 NULL NULL 1 100 NULL 296311 1
27 1 1 163 13145 147 Project Tracking 4 296312 NULL 100 4 NULL NULL 1 100 NULL 296312 1
28 1 1 163 13145 147 Project Closure 4 296313 NULL 100 4 NULL NULL 1 100 NULL 296313 1
29 1 2 163 13145 149 Error Discovery Rate 1 149 5.86 100 4 NULL NULL 1 100 NULL NULL 1
30 1 2 163 13145 149 System Test Execution 4 296307 3.89 100 4 NULL NULL 1 100 NULL 296307 1
31 1 2 163 13145 149 System Integration Test Execution 4 296308 11.5 100 4 NULL NULL 1 100 NULL 296308 1
32 1 2 163 13145 149 Regression Test Suite Execution 4 296309 5.25 100 4 NULL NULL 1 100 NULL 296309 1
33 1 2 163 13145 150 RSI 1 150 2.56 100 4 NULL NULL 2 100 NULL NULL 1
34 1 1 163 13145 151 Test Design Productivity 1 151 NULL 100 4 NULL NULL 3 100 NULL NULL 1
35 1 1 163 13145 151 System Test Development 4 296304 NULL 100 4 NULL NULL 3 100 NULL 296304 1
36 1 1 163 13145 151 System Integration Test Development 4 296305 NULL 100 4 NULL NULL 3 100 NULL 296305 1
37 1 1 163 13145 151 Regression Test Suite Development 4 296306 NULL 100 4 NULL NULL 3 100 NULL 296306 1
38 1 1 163 13145 152 Test Execution Productivity 1 152 NULL 100 4 NULL NULL 3 100 NULL NULL 1
39 1 1 163 13145 152 System Test Execution 4 296307 NULL 100 4 NULL NULL 3 100 NULL 296307 1
40 1 1 163 13145 152 System Integration Test Execution 4 296308 NULL 100 4 NULL NULL 3 100 NULL 296308 1
41 1 1 163 13145 152 Regression Test Suite Execution 4 296309 NULL 100 4 NULL NULL 3 100 NULL 296309 1
42 1 1 163 13145 153 Load Factor 1 153 0 100 4 NULL NULL 1 100 NULL NULL 1
43 1 1 163 13145 154 Cost per Unit of Measure 1 154 0.07 100 4 NULL NULL 1 100 NULL NULL 1
44 1 2 163 13145 155 Review Defect Leakage % 1 155 NULL 100 4 NULL NULL 2 100 NULL NULL 1
45 1 2 163 13145 155 Knowledge Transition 3 19 NULL 100 4 NULL NULL 2 100 19 NULL 1
46 1 2 163 13145 155 Planning and Strategy 3 27 NULL 100 4 NULL NULL 2 100 27 NULL 1
47 1 2 163 13145 155 Test Requirements 3 28 NULL 100 4 NULL NULL 2 100 28 NULL 1
48 1 2 163 13145 155 Test Design 3 30 NULL 100 4 NULL NULL 2 100 30 NULL 1
49 1 1 163 13145 156 Schedule Variation 1 156 NULL 100 4 NULL NULL 1 100 NULL NULL 1
50 1 1 163 13145 156 Definition 4 296297 NULL 100 4 NULL NULL 1 100 NULL 296297 1
51 1 1 163 13145 156 Project Planning 4 296298 NULL 100 4 NULL NULL 1 100 NULL 296298 1
52 1 1 163 13145 156 Knowledge Transition 4 296299 NULL 100 4 NULL NULL 1 100 NULL 296299 1
53 1 1 163 13145 156 Test Requirements 4 296300 NULL 100 4 NULL NULL 1 100 NULL 296300 1
54 1 1 163 13145 156 Test Planning 4 296301 NULL 100 4 NULL NULL 1 100 NULL 296301 1
55 1 1 163 13145 156 System Test Design 4 296302 NULL 100 4 NULL NULL 1 100 NULL 296302 1
56 1 1 163 13145 156 System Integration Test Design 4 296303 NULL 100 4 NULL NULL 1 100 NULL 296303 1
57 1 1 163 13145 156 System Test Development 4 296304 NULL 100 4 NULL NULL 1 100 NULL 296304 1
58 1 1 163 13145 156 System Integration Test Development 4 296305 NULL 100 4 NULL NULL 1 100 NULL 296305 1
59 1 1 163 13145 156 Regression Test Suite Development 4 296306 NULL 100 4 NULL NULL 1 100 NULL 296306 1
60 1 1 163 13145 156 System Test Execution 4 296307 NULL 100 4 NULL NULL 1 100 NULL 296307 1
61 1 1 163 13145 156 System Integration Test Execution 4 296308 NULL 100 4 NULL NULL 1 100 NULL 296308 1
62 1 1 163 13145 156 Regression Test Suite Execution 4 296309 NULL 100 4 NULL NULL 1 100 NULL 296309 1
63 1 1 163 13145 156 Test Delivery 4 296310 NULL 100 4 NULL NULL 1 100 NULL 296310 1
64 1 1 163 13145 156 Acceptance Testing 4 296311 NULL 100 4 NULL NULL 1 100 NULL 296311 1
65 1 1 163 13145 156 Project Tracking 4 296312 NULL 100 4 NULL NULL 1 100 NULL 296312 1
66 1 1 163 13145 156 Project Closure 4 296313 NULL 100 4 NULL NULL 1 100 NULL 296313 1
67 1 1 163 13145 157 Test Preparation Productivity 1 157 NULL 100 4 NULL NULL 3 100 NULL NULL 1
68 1 1 163 13145 157 System Test Development 4 296304 NULL 100 4 NULL NULL 3 100 NULL 296304 1
69 1 1 163 13145 157 System Integration Test Development 4 296305 NULL 100 4 NULL NULL 3 100 NULL 296305 1
70 1 1 163 13145 157 Regression Test Suite Development 4 296306 NULL 100 4 NULL NULL 3 100 NULL 296306 1
71 1 2 163 13145 158 Test effectiveness 1 158 77.4 100 4 NULL NULL 1 100 NULL NULL 1
72 1 1 163 13145 195 Test Script Preparation Productivity 1 195 0.89 100 4 NULL NULL 3 100 NULL NULL 1
73 1 2 163 13145 197 % Rework Effort 1 197 NULL 100 4 NULL NULL 2 100 NULL NULL 1
74 1 2 163 13145 197 Knowledge Transition 4 296299 NULL 100 4 NULL NULL 2 100 NULL 296299 1
75 1 2 163 13145 197 Test Requirements 4 296300 NULL 100 4 NULL NULL 2 100 NULL 296300 1
76 1 2 163 13145 197 Test Planning 4 296301 NULL 100 4 NULL NULL 2 100 NULL 296301 1
77 1 2 163 13145 197 System Test Design 4 296302 NULL 100 4 NULL NULL 2 100 NULL 296302 1
78 1 2 163 13145 197 System Integration Test Design 4 296303 NULL 100 4 NULL NULL 2 100 NULL 296303 1
79 1 2 163 13145 197 System Test Development 4 296304 NULL 100 4 NULL NULL 2 100 NULL 296304 1
80 1 2 163 13145 197 System Integration Test Development 4 296305 NULL 100 4 NULL NULL 2 100 NULL 296305 1
81 1 2 163 13145 197 System Test Execution 4 296307 NULL 100 4 NULL NULL 2 100 NULL 296307 1
82 1 2 163 13145 197 System Integration Test Execution 4 296308 NULL 100 4 NULL NULL 2 100 NULL 296308 1
83 1 2 163 13145 197 Regression Test Suite Execution 4 296309 NULL 100 4 NULL NULL 2 100 NULL 296309 1
84 1 2 163 13145 197 Test Delivery 4 296310 NULL 100 4 NULL NULL 2 100 NULL 296310 1
85 1 2 163 13145 200 Cost of Quality % 1 200 NULL 100 4 NULL NULL 2 100 NULL NULL 1
86 1 2 163 13145 205 Appraisal Cost of Quality % 1 205 NULL 100 4 NULL NULL 2 100 NULL NULL 1
87 1 2 163 13145 206 Prevention Cost of Quality % 1 206 NULL 100 4 NULL NULL 2 100 NULL NULL 1
88 1 2 163 13145 207 Failure Cost of Quality % 1 207 NULL 100 4 NULL NULL 2 100 NULL NULL 1
89 1 2 163 13145 208 % Review Efficiency 1 208 55.91 100 4 NULL NULL 1 100 NULL NULL 1
90 1 2 163 13145 208 Knowledge Transition 3 19 58.33 100 4 NULL NULL 1 100 19 NULL 1
91 1 2 163 13145 208 Planning and Strategy 3 27 55.56 100 4 NULL NULL 1 100 27 NULL 1
92 1 2 163 13145 208 Test Requirements 3 28 45.83 100 4 NULL NULL 1 100 28 NULL 1
93 1 2 163 13145 208 Test Design 3 30 66.67 100 4 NULL NULL 1 100 30 NULL 1
94 1 2 163 13145 214 Application Defect Density by Size 1 214 4.1 100 4 NULL NULL 2 100 NULL NULL 1
95 1 2 163 13145 218 Application Defect Density by Size for S1 application defects 1 218 1.3 100 4 NULL NULL 2 100 NULL NULL 1
96 1 2 163 13145 219 Application Defect Density by Size for S2 application defects 1 219 1.4 100 4 NULL NULL 2 100 NULL NULL 1
97 1 2 163 13145 220 Review Defect Density by Size 1 220 136.84 100 4 NULL NULL 2 100 NULL NULL 1
98 1 1 163 13145 221 Size Variation % - Test Design 1 221 11.76 100 4 NULL NULL 2 100 NULL NULL 1
99 1 1 163 13145 221 System Test Development 4 296304 12.5 100 4 NULL NULL 2 100 NULL 296304 1
100 1 1 163 13145 221 System Integration Test Development 4 296305 -42.86 100 4 NULL NULL 2 100 NULL 296305 1
101 1 1 163 13145 221 Regression Test Suite Development 4 296306 -16.67 100 4 NULL NULL 2 100 NULL 296306 1
102 1 1 163 13145 225 Size Variation % - Test Execution 1 225 3.45 100 4 NULL NULL 2 100 NULL NULL 1
103 1 1 163 13145 225 System Test Execution 4 296307 0 100 4 NULL NULL 2 100 NULL 296307 1
104 1 1 163 13145 225 System Integration Test Execution 4 296308 0 100 4 NULL NULL 2 100 NULL 296308 1
105 1 1 163 13145 225 Regression Test Suite Execution 4 296309 -37.5 100 4 NULL NULL 2 100 NULL 296309 1
106 1 2 163 13145 229 Test Coverage % 1 229 103.45 100 4 NULL NULL 3 100 NULL NULL 1
107 1 2 163 13145 229 System Test Execution 4 296307 100 100 4 NULL NULL 3 100 NULL 296307 1
108 1 2 163 13145 229 System Integration Test Execution 4 296308 100 100 4 NULL NULL 3 100 NULL 296308 1
109 1 2 163 13145 229 Regression Test Suite Execution 4 296309 62.5 100 4 NULL NULL 3 100 NULL 296309 1
110 1 2 163 13145 230 % Execution Complete 1 230 115.63 100 4 NULL NULL 3 100 NULL NULL 1
111 1 2 163 13145 230 System Test Execution 4 296307 450 100 4 NULL NULL 3 100 NULL 296307 1
112 1 2 163 13145 230 System Integration Test Execution 4 296308 133.33 100 4 NULL NULL 3 100 NULL 296308 1
113 1 2 163 13145 230 Regression Test Suite Execution 4 296309 114.29 100 4 NULL NULL 3 100 NULL 296309 1
114 1 1 163 13145 234 Test Environment Downtime % 1 234 NULL 100 4 NULL NULL 2 100 NULL NULL 1
115 1 1 163 13145 237 % Effort for Project Management 1 237 NULL 100 4 NULL NULL 2 100 NULL NULL 1
116 1 2 163 13145 239 Risk Mitigation Effectiveness 1 239 0 100 4 NULL NULL 3 100 NULL NULL 1
117 1 1 163 13145 242 Code Delivery Slippage 1 242 NULL 100 4 NULL NULL 2 100 NULL NULL 1
118 1 2 163 13145 244 % Requirements defect detection effectiveness 1 244 150 100 4 NULL NULL 3 100 NULL NULL 1
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