Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Using @@ROWCOUNT in an IF STATEMENT

Author  Topic 

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-23 : 10:02:10
Hi Reader!

Is there any way to use the number of rows in a select statement in an if statement. See below.


If NumberRows > 1 
	UPDATE tblScoreDesction
	SET Survey = 1

Esle If NumberRows = 1 
	If Survey = 1 
		UPDATE tblScoreDesction
		SET Survey = 0
	Else
		UPDATE tblScoreDesction
		SET Survey = 1
	End
End




Thanks.

Leah




Edited by - leahsmart on 05/23/2003 10:06:10

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-23 : 10:16:32
Yes. You have to store the rowcount in a variable like this:

declare @NumberRows int

select ... (your select statement)

select @NumberRows = @@ROWCOUNT

If @NumberRows > 1
UPDATE tblScoreDesction
SET Survey = 1

Esle If @NumberRows = 1
If Survey = 1
UPDATE tblScoreDesction
SET Survey = 0
Else
UPDATE tblScoreDesction
SET Survey = 1
End
End
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2003-05-23 : 10:17:02
DECLARE @RC BIGINT

SELECT *
FROM TableX

SET @RC = @@ROWCOUNT

IF @RC > 1
BEGIN
--SQL HERE
END
ELSE
BEGIN
--SQL HERE
END

or
DECLARE @RC BIGINT

SELECT *
FROM TableX

SET @RC = @@ROWCOUNT

update tblScoreDesction
set Survey =
case
when @RC > 1 then 1
when @RC = 1 AND Survey = 1 THEN 0
WHEN @RC = 1 AND Survey <> 1 THEN 1
end
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-23 : 10:24:17
Where would I put the where statement. The below gives me this error: Incorrect syntax near the keyword 'WHERE'.


DECLARE @NumberRows INT 

Select 	DISTINCT
	CompanyType,
	Phase,
	Survey

FROM	tblScoreDescription

WHERE	CompanyType = 'All Construction KPIs'
	AND
	Phase = 'Construction Industry'
	
SET @NumberRows = @@ROWCOUNT 

UPDATE	tblScoreDescription 
SET	Survey = 
CASE 
	WHEN @NumberRows > 1 then 1 
	WHEN @NumberRows = 1 AND Survey = 1 THEN 0 
	WHEN @NumberRows = 1 AND Survey <> 1 THEN 1 

WHERE CompanyType = 'All Construction KPIs' and 'Construction Industry'

END


Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-23 : 10:25:57
The "CASE" statement should be ended with an "END":

UPDATE tblScoreDescription
SET Survey =
CASE
WHEN @NumberRows > 1 then 1
WHEN @NumberRows = 1 AND Survey = 1 THEN 0
WHEN @NumberRows = 1 AND Survey <> 1 THEN 1
END
WHERE CompanyType = 'All Construction KPIs' and 'Construction Industry'




Edited by - andraax on 05/23/2003 10:26:29
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-23 : 10:35:55


Wonderfull!! It works a treat!!! Where would I be without you lot.
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2003-05-24 : 03:43:49
hmmmm...Key Performance Indicators and Industry scores...fancy Quality Management stuff (Balanced Scorecard et al?)

OS
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 05:48:24
Hello!

Me again. I have got the above all working nicey. All I need now is to be able to write it. So I want to return a value of either 0 or 1. RETURN @Survey it does not like. Do you have any ideas?


SET	@Survey = 
CASE 
	WHEN @NumberRows = 2 THEN 1
	WHEN @NumberRows = 1 AND Survey = 1 THEN 0
	WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END

RETURN @Survey




Edited by - leahsmart on 05/30/2003 05:49:09
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 06:30:52
Hello again Leah!

How to return it depends on where you are running the code. You use RETURN if it is a user defined function. If it is a stored procedure, you either use an output parameter, or do a select at the end of the proc to return the value in a rowset.

If you only want to show the value, use either

print @survey

or

select @survey

If you tell us what you are trying to do with the code, or post all your code, we will be glad to help you.





Edited by - andraax on 05/30/2003 06:34:57
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 06:52:21
Hi,

Basically it's the same as below but instead of updating I want it to return the value. I am using a stored procedure. Below is the code I use for updating.


CREATE PROCEDURE [dbo].[sp__DPM_TemplateMark]

@CompanyType		VarChar(50),
@Phase		VarChar(50)

AS

DECLARE @NumberRows INT 

Select 	DISTINCT
	CompanyType,
	Phase,
	Survey

FROM	tblScoreDescription

WHERE	CompanyType = @CompanyType
		AND
		Phase = @Phase
	
SET @NumberRows = @@ROWCOUNT 

UPDATE	tblScoreDescription 
SET	Survey = 
CASE 
	WHEN @NumberRows = 2 THEN 1
	WHEN @NumberRows = 1 AND Survey = 1 THEN 0
	WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END
WHERE CompanyType = @CompanyType AND Phase = @Phase
GO
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 06:58:55
OK, then you can either return the value with a select statement, or use an output parameter:


CREATE PROCEDURE [dbo].[sp__DPM_TemplateMark]

@CompanyType VarChar(50),
@Phase VarChar(50),
@survey bit OUTPUT
...
Return @survey



Then when you call the proc:


declare @out bit

exec @out=sp__DPM_TemplateMark ...





Edited by - andraax on 05/30/2003 06:59:12
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 07:06:33
I am unsure on the code. Below is what I have done.



--@CompanyType	VarChar(50),
--@Phase		VarChar(50)
@survey 	bit	OUTPUT 

AS

DECLARE @NumberRows INT 

Select 	DISTINCT
	CompanyType,
	Phase,
	Survey

FROM	tblScoreDescription

WHERE	CompanyType = '_Leah'
	AND
	Phase = 'Measures'
	
SET @NumberRows = @@ROWCOUNT 

SET	@survey = 
CASE 
	WHEN @NumberRows = 2 THEN 1
	WHEN @NumberRows = 1 AND Survey = 1 THEN 0
	WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END

WHERE CompanyType = '_Leah' AND Phase = 'Measures'

GO



And below is the error message I GET.


Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@survey'.
Server: Msg 137, Level 15, State 1, Line 29
Must declare the variable '@survey'.
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 07:11:26
This will only work inside the proc... Try this


CREATE PROC test
--@CompanyType VarChar(50),
--@Phase VarChar(50)
@survey bit OUTPUT

AS

DECLARE @NumberRows INT

Select DISTINCT
CompanyType,
Phase,
Survey

FROM tblScoreDescription

WHERE CompanyType = '_Leah'
AND
Phase = 'Measures'

SET @NumberRows = @@ROWCOUNT

SET @survey =
CASE
WHEN @NumberRows = 2 THEN 1
WHEN @NumberRows = 1 AND Survey = 1 THEN 0
WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END

WHERE CompanyType = '_Leah' AND Phase = 'Measures'

RETURN @survey
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 07:13:53
I get the below error.


Server: Msg 156, Level 15, State 1, Procedure test, Line 30
Incorrect syntax near the keyword 'WHERE'.
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 07:17:07
My mistake, didn't see that statement:

SELECT @survey =
CASE
WHEN @NumberRows = 2 THEN 1
WHEN @NumberRows = 1 AND Survey = 1 THEN 0
WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END

WHERE CompanyType = '_Leah' AND Phase = 'Measures'


(SELECT instead of SET)
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 07:18:39
I now get all the below errors.


Server: Msg 207, Level 16, State 3, Procedure test, Line 23
Invalid column name 'Survey'.
Server: Msg 207, Level 16, State 1, Procedure test, Line 23
Invalid column name 'Survey'.
Server: Msg 207, Level 16, State 1, Procedure test, Line 23
Invalid column name 'CompanyType'.
Server: Msg 207, Level 16, State 1, Procedure test, Line 23
Invalid column name 'Phase'.
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 07:23:38
OK, now the errors indicate that the columns do not exist in the table. And in this statement:

SELECT @survey =
CASE
WHEN @NumberRows = 2 THEN 1
WHEN @NumberRows = 1 AND Survey = 1 THEN 0
WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END

WHERE CompanyType = '_Leah' AND Phase = 'Measures'

there is no from clause.

Maybe it would be better if you post the DDL for the tblScoreDescription table, and tell us what result you want of the procedure.
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 07:31:28
What is a DDL, how do I create it? Below is the table, top row is headers. Basically if the template has a value of 1 then it is not in use and I want to set a variable to 1 otherwise its in use I want to set it to 0 and that's it basically. I will then pass this back to my asp page that will add a nice little star to the template name if it's not in use. I will run the sproc for every template. Is there a better way to do this. So overall, I just want to list all the templates CompanyType/Phase and add a star if it's not in use. Hope thats enough info for you.

"ScoreDescriptionID","Phase","CompanyType","ScoreDescription","ShortScoreDescription","StandardTarget","Weighting","TargetIncrease","Answer1","Answer2","Answer3","Answer4","Answer5","Survey","ScorecardType","ScoreType","Score1","Score2","Score3","Score4","Score5","SectionTitle","SequenceNumber","Best","Worst","ChildType","ChildPhase","ChildCalc","Answer6","Answer7","Answer8","Answer9","Answer10","Score6","Score7","Score8","Score9","Score10","Guidance","ConsolidatorID","DataSetCountMin","GuidanceHTML","PrivacyOption","DateAdded","DateLastUpdated","ABUserID","ABMemberID","UpdatedByUserID"
13,"All Construction KPIs","Construction Industry","Determine the client's overall level of satisfaction with the completed product/facility. Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - product",75,1,2,"Client satisfaction - product (1 - 10)","","","","",False,55,"#,##0",1,,,,,"1. Client Satisfaction",1,100,0,"","","","Performance (number)","","","#,##0.0","[A]",,,,,,"Carry out a post project review to determine the clients level of satisfaction. Typically this question would be asked as part of a more comprehensive review or survey.
Satisfaction should be expressed a number between 1 and 10. Where:
10 = Totall",4,,"",17,2002-12-12 16:45:01,2003-04-09 11:35:30.657000000,,,4
14,"All Construction KPIs","Construction Industry","Determine the client's satisfaction with the consultants and main contractor during the project. Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - service",75,1,2,"Client satisfaction - service (1 - 10)","","","","",False,55,"#,##0",1,0,0,0,0,"1. Client Satisfaction",2,100,0,"","","","Performance (number)","","","#,##0.0","[A]",,,,,,"Carry out a post project review to determine the clients level of satisfaction. Typically this question would be asked as part of a more comprehensive review or survey.

Satisfaction should be expressed a number between 1 and 10. Where:
10 = Tota",,,"",,2002-12-12 16:45:01,,,,
15,"All Construction KPIs","Construction Industry","The degree to which the handover of a project to the client was delayed by defects which affected the ability of the client to put the facility to use.

Use a 1 to 10 scale; see Guidance for more details.","Defects",75,1,2,"Level of defects (1 - 10)","","","","",False,55,"#,##0",1,0,0,0,0,"2. Quality",1,100,0,"","","","Performance (number)","","","#,##0.0","[A]",,,,,,"Carry out a post project review with the client to determine the condition of the facility in respect of defects.

Use a 1 to 10 scale where:
10 = Defect-free
8 = Some defects, no significant impact on client
5/6 = Some defects, some ",1,,"",1,2002-12-12 16:45:01,2003-03-03 14:43:08.350000000,,,1
16,"All Construction KPIs","Construction Industry","To measure the reliability of cost estimates for design.","Predictability cost - design",75,1,2,"Est. design cost at Commit to Invest (£)","Actual design cost at Available for Use (£)","","","",False,55,"#,##0",2,2,0,0,0,"3. Cost Predictability",1,100,0,"","","","Performance (%)","","","#,##0","(([B]-[A])/[A])*100",,,,,,"Objective
To measure the reliability of cost estimates for design.

Design cost (%) = ((Actual cost at Available for Use (C) - Estimated cost at Commit to Invest (A))/ Estimated cost at Commit to Invest (A)) * 100

Example
Using the following data f",,,"",,2002-12-12 16:45:01,,,,
17,"All Construction KPIs","Construction Industry","To measure the reliability of cost estimates for construction.","Predictability cost - construction",75,1,2,"Est. construction cost at Commit to Invest (£)","Actual construction cost at Available for Use (£)","","","",False,55,"#,##0",2,2,0,0,0,"3. Cost Predictability",2,100,0,"","","","Performance (%)","","","#,##0","(([B]-[A])/[A])*100",,,,,,"Objective
To measure the reliability of cost estimates for construction.

Construction cost (%) = ((Actual Construction cost at (C) - Estimated Construction cost (B))/ Estimated Construction cost (B)) * 100

Example
Using the following data for a pr",,,"",,2002-12-12 16:45:01,,,,
18,"All Construction KPIs","Construction Industry","To measure the reliability of time estimates for design.","Predictability time - design",75,1,2,"Anticipated design period A to B (months)","Actual design period A to B (months)","","","",False,55,"#,##0",1,1,0,0,0,"4. Time Predictability",1,100,0,"","","","Performance (%)","","","#,##0","(([B]-[A])/[A])*100",,,,,,"Objective
To measure the reliability of time estimates for design.

Design time (%) = ((Actual duration at commit to construct (B) - Estimated duration at Commit to Invest (A))/ Estimated duration at Commit to Invest (A)) * 100

Example
Using the fo",,,"",,2002-12-12 16:45:01,,,,
19,"All Construction KPIs","Construction Industry","To measure the reliability of time estimates for construction.","Predictability time - construction",75,1,2,"Construction contract period B to C (weeks)","Actual construction period B to C (weeks)","","","",False,55,"#,##0",1,1,,,,"4. Time Predictability",2,100,0,"","","","Performance (%)","","","#,##0","(([B]-[A])/[A])*100",,,,,,"Objective
To measure the reliability of time estimates for construction.

Construction time (%) = ((Actual duration at Available for use (C) - Estimated duration at Commit to Construct (B))/ Estimated duration at Commit to Construct (B)) * 100

Examp",4,,"",17,2002-12-12 16:45:01,2003-03-28 13:07:33.423000000,,,4
20,"All Construction KPIs","Construction Industry","To measure the pre-tax profitability of a construction industry company.","Profitability",75,1,2,"Profit before tax and interest (£)","Value of sales (£)","","","",False,55,"#,##0",2,2,0,0,0,"5. Profitability",1,100,0,"","","","Performance (%)","","","#,##0","DefaultA=LAST;DefaultB=LAST;C1=[B];([A]/[B])*100",,,,,,"Objective
To measure the profitability of a construction company before tax and interest.

Using the following data for a company:

Profit before'tax and'interest = £2,250,000
Value of sales = £30,000,000


Profitabi",,,"",,2002-12-12 16:45:01,,,,
21,"All Construction KPIs","Construction Industry","To determine the output per employee.","Productivity",75,1,2,"Value of sales (£)","Total value of subcontracted services (£)","Total value of goods supplied (£)","Total full time equivalent employees (number)","",False,55,"#,##0",2,2,2,1,0,"6. Productivity",2,100,0,"","","","Performance (£k)","","","#,##0","DefaultA=[C1];DefaultB=LAST;DefaultC=LAST;DefaultD=LAST;(([A]-[B]-[C])/[D])/1000",,,,,,"Objective
To determine the Value-Added per employee of a construction company.

Method
1 From the annual accounts, identify the value of
sales and deduct the value of goods and
services subcontracted to, or supplied by, other
parties. Di",,,"",,2002-12-12 16:45:01,,,,
22,"All Construction KPIs","Construction Industry","To measure the number of reportable accidents per 100,000 employed - the Accident Incidence Rate (AIR). The AIR formula makes no allowance for variations in part-time work or overtime.","Safety",75,1,2,"Number of reportable accidents (number)","Average number of directly employed (number)","Avg no. of subcontractors employed (number)","","",False,55,"#,##0",1,1,1,0,0,"7. Safety",1,100,0,"","","","Performance (AIR)","","","#,##0","DefaultA=LAST;DefaultB=LAST;DefaultC=LAST;IIF(isnull([C]),Null,CInt(([A]/([B]+[C]))*1000)*100)",,,,,,"Objective
To measure the number of reportable accidents56 per 100,000 employed -the accident
incidence rate (AIR). The AIR formula makes no allowance for variations in part-time work or
overtime.
Many organisations measure accidents per 100,000 hours ",,,"",,2002-12-12 16:45:01,,,,
23,"All Construction KPIs","Construction Industry","To measure the number of reportable accidents per 100,000 employed - the Accident Incidence Rate (AIR). The AIR formula makes no allowance for variations in part-time work or overtime. This measure is for companies with a turnover greater than £10M.","Safety over £10M",75,1,2,"Number of reportable accidents (number)","Average number of directly employed (number)","Avg no. of subcontractors employed (number)","","",False,55,"#,##0",1,1,1,0,0,"7. Safety",3,100,0,"","","","Performance (AIR)","","","#,##0","DefaultA=LAST;DefaultB=LAST;DefaultC=LAST;IIF(isnull([C]),Null,CInt(([A]/([B]+[C]))*1000)*100)",,,,,,"Objective
To measure the number of reportable accidents56 per 100,000 employed -the accident
incidence rate (AIR). The AIR formula makes no allowance for variations in part-time work or
overtime.
Many organisations measure accidents per 100,000 hours ",,,"",,2002-12-12 16:45:01,,,,
24,"All Construction KPIs","Construction Industry","To measure the change in real cost of construction from one year to the next.","Construction Cost",75,1,2,"Tender price for current project (£)","Price for similar project in previous year (£)","adjustment for Specification (%)","adjustment for Location (%)","adjustment for Inflation (%)",False,55,"#,##0",2,2,1,1,1,"8. Construction Cost",1,100,0,"","","","Performance (%)","","","#,##0","M1=[A]+([C]*-0.01*[A]);M2=[M1]+([D]*-0.01*[M1]);M3=[M2]+([E]*-0.01*[M2]);(([M3]-[B])/[B])*100",,,,,,"Objective
To measure the change in real cost of construction from one year to the next.
If two identical structures were built in successive years, the construction cost indicator shows by how much construction costs have changed in the year. It is un",,,"",,2002-12-12 16:45:01,,,,
25,"All Construction KPIs","Construction Industry","To measure the change in construction time from one year to the next.","Construction Time",75,1,2,"Contract period for current project (weeks)","Period for similar project from prev. year (no.)","adjustment for Specification (%)","adjustment for Size (%)","",False,55,"#,##0",1,1,1,1,0,"9. Construction Time",1,100,0,"","","","Performance (%)","","","#,##0","M1=[A]+([C]*-0.01*[A]);M2=[M1]+([D]*-0.01*[M1]);(([M2]-[B])/[B])*100",,,,,,"Objective
To measure the change in construction time from one year to the next.
If two identical structures were built in successive years, the construction time indicator shows by how much construction times have changed in the year. It is unusual to h",,,"",,2002-12-12 16:45:01,,,,
26,"KPIs","Respect for People","Measures how satisfied employees are overall with the influence they have over their jobs, the pay they receive, the sense of achievement they get, and the respect they get from managers/supervisors.","Employee satisfaction",75,1,2,"Employee satisfaction (no.)","","","","",True,55,"#,##0",1,0,0,0,0,"1. Employee satisfaction",1,100,0,"","","","Performance score","","","#,##0.0","[A]",0,,,,,"Conduct a survey, in which all your firms direct employees answers the following questions relating to employee satisfaction:

a) How satisfied are you with the amount of influence you have over your job?

b) How satisfied are you with the amount of p",0,,"",,2002-12-12 16:45:03,,,,
27,"KPIs","Respect for People","The number of direct employees that have left, and been replaced, expressed as a percentage of the number of direct employees per year.","Staff turnover",75,1,2,"Direct employees replaced in last year (no.)","Average direct employees in last year (no.)","","","",True,55,"#,##0",1,1,,,,"2. Staff turnover",2,100,0,"","","","Performance score (%)","","","#,##0","C1=[B];([A]/[B])*100",0,,,,,"From your firms records, determine the number of direct employees that have left your employment and been replaced and the average number of direct employees in the last year.

Performance score (%)staff turnover =Number of direct employees who have lef",4,,"",17,2002-12-12 16:45:03,2003-03-28 12:40:02.977000000,,,4
28,"KPIs","Respect for People","The number of working days lost due to sickness per direct employee per year.","Sickness absence",75,1,2,"Total working days lost due to sickness (no.)","Avg. no. of direct employees in last year (no.)","","","",True,55,"#,##0",1,1,0,0,0,"3. Sickness absence",3,100,0,"","","","Sickness absence (days per employee per year)","","","#,##0.0","DefaultB=[C1];[A]/[B]",0,,,,,"From your firm’s records determine the total number of working days lost due to sickness among direct employees and the average number of direct employees in the last year.

Performance score (days) sickness absence = Total number of working days lost ",0,,"",,2002-12-12 16:45:03,,,,
29,"KPIs","Respect for People","The number of reportable accidents per 100,000 employed (AIR).","Safety",75,1,2,"Reportable accidents in last year (no.)","Avg. no. of all employees in last year (all)","","","",True,55,"#,##0",1,1,0,0,0,"4. Safety",4,100,0,"","","","Safety (AIR)","","","#,##0","CInt(([A]/[B])*10000)*10",0,,,,,"From your firm’s records determine the number of reportable accidents and the average number employed (whether directly in your employ, self employed or sub-contracted) in the last year.

Performance score (AIR) safety = Number of reportable accidents ",0,,"",,2002-12-12 16:45:03,,,,
30,"KPIs","Respect for People","The number of usual hours worked per week per direct employee in his/her main job.","Working hours",75,1,2,"Average hours worked by direct employees (no.)","Avg. no. of direct employees in last year (no.)","","","",True,55,"#,##0",1,1,0,0,0,"5. Working hours",5,100,0,"","","","Working hours","","","#,##0","DefaultB=[C1];[A]/[B]",0,,,,,"Conduct a survey in which all your firms direct employees answer a question relating to their number of usual working hours each week. From the survey determine the total number of usual hours worked each week by all direct employees and the number of dir",0,,"",,2002-12-12 16:45:03,,,,
31,"KPIs","Respect for People","The number of minutes spent travelling on the single journey from home to work each day per direct employee.","Travelling time",75,1,2,"Total minutes spent travelling to work (no.)","Avg. no. of direct employees (no.)","","","",True,55,"#,##0",1,1,0,0,0,"6. Travelling time",6,100,0,"","","","Travelling time","","","#,##0","DefaultB=[C1];[A]/[B]",0,,,,,"Conduct a worker survey in which all your firms direct employees answer a question relating to the number of minutes they spend travelling on the single journey from home to work each day.

From the survey determine the total number of minutes spent tr",0,,"",,2002-12-12 16:45:03,,,,
32,"KPIs","Respect for People","The degree to which the balance of diversity among direct employees in respect of ethnicity, gender, age and disability corresponds with the balance of diversity in the total available workforce.","Diversity",75,1,2,"Diversity (no.)","","","","",True,55,"#,##0",1,0,0,0,0,"7. Diversity",7,100,0,"","","","Diversity","","","#,##0.0","[A]",0,,,,,"Definitions

The degree to which the balance of diversity amongst employees in respect of ethnicity, gender, age and disability corresponds with the balance of diversity in the total available work force, using the 1 to 10 scale where

10 = fully (eg ",0,,"",,2002-12-12 16:45:03,,,,
33,"KPIs","Respect for People","The number of training days provided per employee per year.","Training",75,1,2,"Total training days provided last year (no.)","Avg. no. of direct employees in last year (no.)","","","",True,55,"#,##0",1,1,0,0,0,"8. Training",8,100,0,"","","","Training days per employee","","","#,##0.0","DefaultB=[C1];[A]/[B]",0,,,,,"From your firm’s records, determine the total number of training days provided and the average number of employees in the last year

Performance score (days) training = Total number of training days provided in the last year",0,,"",,2002-12-12 16:45:03,,,,
34,"KPIs","Respect for People","The gross weekly earnings (before tax) per full time direct employee.","Pay",75,1,2,"Total gross earnings (£)","Avg. no. of direct employees (no.)","","","",True,55,"#,##0",2,1,0,0,0,"9. Pay",9,100,0,"","","","Pay (£'s per week per employee)","","","#,##0","DefaultB=[C1];iif(isnull([A]),null,cint((([A]/[B])/52)/10)*10)",0,,,,,"From your firm’s records, determine the total amount of gross earnings (before tax) for all, full time direct employees and the total number of full time direct employees in the last year.

Performance score (£) pay = Total amount of gross earnings (be",0,,"",,2002-12-12 16:45:03,,,,
35,"KPIs","Respect for People","The involvement of firms in the Investors in People (IiP) scheme using a 1 to 3 scale where:
3=fully recognised as an IiP,
2=committed to attaining IiP status,
1=not involved in the IiP scheme.","Investor in People",75,1,2,"Investor in people (no. 1 to 3)","","","","",True,55,"#,##0",1,0,0,0,0,"9. Investor in People",10,100,0,"","","","Investor in people","","","#,##0","[A]",0,,,,,"The involvement of firms in the Investors in People scheme against:

a) Fully recognised as an Investor in People;
b) Committed to attaining Investor in People status
c) Not involved in the Investor in People scheme

using the 1 to 3 scale, where:
",0,,"",,2002-12-12 16:45:03,,,,
38,"Monthly Summary KPIs","Kier Regional","To measure the number of reportable accidents per 100,000 employed - the Accident Incidence Rate (AIR). The AIR formula makes no allowance for variations in part-time work or overtime.","Accident incidence rate",100,1,1,"Accident Incidence Rate","","","","",False,55,"#,##0",1,,,,,"1 Safety",1,100,0,"","","","Performance (AIR)","","","#,##0","DefaultA=@Kier01;[A]",,,,,,"",4,,"",17,2002-12-12 16:53:37,2003-04-04 11:21:19.597000000,,,4
39,"Monthly Summary KPIs","Kier Regional","The number of direct employees that have left, and been replaced, expressed as a percentage of the number of direct employees during the preceding twelve-month period.","Staff turnover",75,1,1,"Staff Turnover %","","","","",False,55,"Fixed",1,,,,,"6 Personnel",24,100,0,"","","","Performance score (%)","","","#,##0","DefaultA=@Kier24;[A]",0,,,,,"",4,,"",17,2002-12-12 17:00:36,2003-04-04 11:23:51.553000000,,,4
40,"Monthly Summary KPIs","Kier Regional","The number of working days lost due to sickness per direct employee during the preceding twelve-month period.","Sickness absence",75,1,1,"Sicknes Absence","","","","",False,55,"Fixed",1,,,,,"6 Personnel",25,100,0,"","","","Sickness absence (days per employee per year)","","","#,##0.0","DefaultA=@Kier25;[A]",0,,,,,"",4,,"",17,2002-12-12 17:00:48,2003-04-04 11:24:21.180000000,,,4
41,"Monthly Summary KPIs","Kier Regional","The number of training days provided per employee during the preceding twelve-month period.","Training",75,1,1,"Training days per employess","","","","",False,55,"Fixed",1,,,,,"6 Personnel",26,100,0,"","","","Training days per employee","","","#,##0.0","DefaultA=@Kier26;[A]",0,,,,,"",4,,"",17,2002-12-12 17:01:04,2003-04-04 11:24:55.307000000,,,4
42,"Monthly Summary KPIs","Kier Regional","Determine the client's overall level of satisfaction with the completed product/facility.

Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - product",75,1,1,"Client satisfaction - product","","","","",False,55,"#,##0",1,,,,,"7 Marketing",31,100,0,"","","","Performance (number)","","","#,##0.0","DefaultA=@Kier31;[A]",,,,,,"",4,,"",17,2002-12-12 17:01:40,2003-04-04 11:25:30.387000000,,,4
43,"Monthly Summary KPIs","Kier Regional","Determine the client's satisfaction with the consultants and main contractor during the project.

Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - service",75,1,1,"Client satisfaction - service","","","","",False,55,"#,##0",1,,,,,"7 Marketing",32,100,0,"","","","Performance (number)","","","#,##0.0","DefaultA=@Kier32;[A]",,,,,,"",4,,"",17,2002-12-12 17:01:52,2003-04-04 11:26:23.950000000,,,4
47,"Monthly Summary KPIs","Kier Regional","The Accident Frequency Rate (AFR) gives the accident rate per 100,000 person hours worked. Unlike the AIR, the AFR formula makes allowance for variations in part-time work or overtime","Accident frequency rate",0,1,1,"Accident Frequency Rate","","","","",False,55,"Fixed",1,,,,,"1 Safety",2,0,1,"","","","Performance (AFR)","","","#,##0","DefaultA=@Kier02;[A]",,,,,,"",4,,"",17,2002-12-12 17:05:11,2003-04-04 11:21:45.583000000,,,4
49,"Monthly Summary KPIs","Kier Regional","To measure the cost of disposing of construction site or building maintenance waste, by skip during the preceding twelve-month period.","Cost of waste",0.25,1,1,"Cost of waste","","","","",False,55,"Fixed",2,0,0,0,0,"2 Environmental",3,0.10000000000000001,0.5,"","","","Cost of waste","","","","DefaultA=@Kier03;[A]",0,,,,,"",0,,"",,2002-12-12 17:15:25,2002-12-12 19:14:56,,,
50,"Monthly Summary KPIs","Kier Regional","To measure the energy used at permanent office locations during the preceding twelve-month period.","Energy usage",4,1,1,"Energy use","","","","",False,55,"Currency",2,0,0,0,0,"2 Environmental",4,2,7,"","","","Energy use in £/m2","","","","DefaultA=@Kier04;[A]",0,,,,,"",0,,"",,2002-12-12 17:19:26,2003-02-03 11:31:12,,,
51,"Monthly Summary KPIs","Kier Regional","To measure the level of environmental training provision during the preceding twelve-month period.","Environmental training",0.14999999999999999,1,1,"Environmental training %","","","","",False,55,"Fixed",1,0,0,0,0,"2 Environmental",5,0.25,0,"","","","Environmental Training %","","","","DefaultA=@Kier05;[A]",0,,,,,"",0,,"",,2002-12-12 17:24:34,2003-02-03 11:31:39,,,
52,"Monthly Summary KPIs","Kier Regional","To measure the amount of CO2 emitted by a business unitÂ’s vehicle fleet for business use during the preceding twelve-month period.

Note: This KPI is only scored in April each year, you should skip this measure in any other month.","CO2 emissions",600,1,1,"Business Miles per Head","Total No of Company Cars","","","",False,55,"#,##0",1,1,,,,"2 Environmental",6,500,800,"","","","Co2 Emissions","","","","(229*[A]/1000)*([B]/1000)",0,,,,,"",8,,"",22,2002-12-12 18:35:02,2003-04-23 12:03:40.937000000,,,8
53,"Monthly Summary KPIs","Kier Regional","To measure environmental infringements in the preceding twelve month period.","Infringements",0,1,1,"No of infringements","","","","",False,55,"#,##0",1,0,0,0,0,"2 Environmental",7,0,5,"","","","Total","","","","DefaultA=@Kier07;[A]",0,,,,,"",0,,"",,2002-12-12 18:59:59,2003-01-14 07:55:00,,,
54,"Monthly Summary KPIs","Kier Regional","To measure the volume of work carried out by our supply chain partners sub-contractors over the preceding twelve-month period","Turnover with SCPs (sub-con)",40,1,1,"Turnover with SCP (sub-contractors)","","","","",False,55,"Fixed",1,0,0,0,0,"3 Supply Chain",8,60,0,"","","","% Share","","","","DefaultA=@Kier08;[A]",0,,,,,"",4,,"",17,2002-12-12 19:15:18,2003-03-03 22:11:14.530000000,,,4
55,"Monthly Summary KPIs","Kier Regional","To measure the value of materials supplied by our supply chain partner suppliers over the preceding twelve-month period","Turnover with SCPs (materials)",7,1,1,"Turnover with SCP (materials)","","","","",False,55,"Fixed",1,0,0,0,0,"3 Supply Chain",9,10,0,"","","","% Share","","","","DefaultA=@Kier09:[A]",0,,,,,"",0,,"",,2002-12-12 19:21:56,2003-01-14 07:56:41,,,
56,"Monthly Summary KPIs","Kier Regional","To measure the number of accidents per 100,000 hours worked (the Accident Frequency Rate, or AFR) per each supply chain partner.","Sub-contract AFR",0,1,1,"Supply Chain Partner AFR","","","","",False,55,"Fixed",1,,,,,"3 Supply Chain",10,0,1,"","","","Performance (AFR)","","","#,##0","DefaultA=@Kier10;[A]",,,,,,"",4,,"",17,2002-12-12 19:24:34,2003-04-04 11:22:13.630000000,,,4
57,"Monthly Summary KPIs","Kier Regional","To measure the reliability of Supply Chain Partner (sub-contractor) time estimates for construction during the preceding twelve-month period.","Sub-contract predictability time",100,1,2,"% change in time","","","","",False,55,"#,##0.0",1,0,0,0,0,"3 Supply Chain",11,100,0,"","","","Performance %","","","#,##0.0","DefaultA=@Kier11;[A]",0,,,,,"",0,,"",,2002-12-12 19:27:31,2002-12-12 19:28:17,,,
59,"Monthly Summary KPIs","Kier Regional","To assess the level of Supply Chain Partner (sub-contractor) defects, at Practical Completion.","Sub-contract defects at PC",5,1,1,"Defect per £M","","","","",False,55,"Fixed",1,0,0,0,0,"3 Supply Chain",12,0,100,"","","","Defect per £M","","","","DefaultA=@Kier12;[A]",0,,,,,"",0,,"",,2002-12-12 19:35:20,2003-01-21 12:17:04,,,
60,"Monthly Summary KPIs","Kier Regional","To assess the level of defects, at Practical Completion.","Defects at PC",5,1,1,"Defects per £M","","","","",False,55,"Fixed",1,,,,,"4 Construction Activities",13,0,2000,"","","","Defects per £M","","","","DefaultA=@Kier13;[A]",0,,,,,"",4,,"",17,2002-12-12 19:39:11,2003-04-04 11:22:43.363000000,,,4
61,"Monthly Summary KPIs","Kier Regional","To assess the level of defects during the defects liability period.","No. of new defects during DLP",5,1,1,"Defects per £M","","","","",False,55,"Fixed",1,0,0,0,0,"4 Construction Activities",14,0,100,"","","","Defects per £M","","","","DefaultA=@Kier14;[A]",0,,,,,"",0,,"",,2002-12-12 19:41:52,2003-01-14 08:03:07,,,
62,"Monthly Summary KPIs","Kier Regional","To measure the reliability of time estimates for construction","Predictability time",100,1,1,"% change in time","","","","",False,55,"#,##0.0",1,,,,,"4 Construction Activities",15,100,0,"","","","Performance %","","","#,##0.0","DefaultA=@Kier15;[A]",0,,,,,"",4,,"",17,2002-12-12 19:43:21,2003-04-04 11:23:14.367000000,,,4
64,"Monthly Summary KPIs","Kier Regional","To measure the number of projects which are completed early or on time during the preceding twelve-month period","No. complete within original contract",60,1,1,"% Project completed within original contract","","","","",False,55,"Fixed",1,0,0,0,0,"4 Construction Activities",16,100,0,"","","","% Complete","","","","DefaultA=@Kier16;[A]",0,,,,,"",0,,"",,2002-12-12 21:53:53,2003-01-14 08:04:47,,,
65,"Monthly Summary KPIs","Kier Regional","To measure the number of projects which are completed within extended contract period during the preceding twelve-month period","No. complete within extended contract",100,1,1,"% Complete","","","","",False,55,"Fixed",1,0,0,0,0,"4 Construction Activities",18,100,0,"","","","% Complete","","","","DefaultA=@Kier18;[A]",0,,,,,"",0,,"",,2002-12-12 21:59:21,2003-01-14 08:09:06,,,
66,"Monthly Summary KPIs","Kier Regional","To determine the ratio of construction contracts won to the number of tenders submitted in competition during the preceding twelve-month period.","Strike rate (number)",6,1,1,"Strike rate (number)","","","","",False,55,"Fixed",1,0,0,0,0,"5 Estimating",20,2,10,"","","","Strike rate","","","","DefaultA=@Kier20;[A]",0,,,,,"",0,,"",,2002-12-12 22:13:27,2002-12-20 11:00:25,,,
67,"Monthly Summary KPIs","Kier Regional","To determine the value of tenders prepared by each Estimator during the preceding twelve-month period.","Tenders per estimator (value)",1.71,1,1,"Tender per estimator (value)","","","","",False,55,"Currency",2,0,0,0,0,"5 Estimating",23,2.25,1.1699999999999999,"","","","£ million per estimator","","","","DefaultA=@Kier23;[A]",0,,,,,"",0,,"",,2002-12-12 22:17:39,2003-01-14 08:13:20,,,
68,"Monthly Summary KPIs","Kier Regional","To compare turnover generated by key accounts to total turnover during the preceding twelve-month period.","Turnover from key accounts",10,1,1,"Key Accounts Turnover %","","","","",False,55,"Fixed",1,0,0,0,0,"7 Marketing",27,20,0,"","","","% of Key Accounts","","","","DefaultA=@Kier27;[A]",0,,,,,"",0,,"",,2002-12-12 22:22:56,2002-12-12 22:23:33,,,
69,"Monthly Summary KPIs","Kier Regional","To compare turnover generated by two-stage contracts to total turnover during the preceding twelve-month period","Turnover from two-stage contracts",10,1,1,"Two-stage contract turnover %","","","","",False,55,"Fixed",1,0,0,0,0,"7 Marketing",28,15,0,"","","","% Two-stage contracts","","","","DefaultA=@Kier28;[A]",0,,,,,"",0,,"",,2002-12-12 22:25:31,2002-12-12 22:26:08,,,
70,"Monthly Summary KPIs","Kier Regional","To compare turnover generated by negotiated contracts to total turnover during the preceding twelve-month period","Turnover from negotiated contracts",40,1,1,"Negotiated contract turnover %","","","","",False,55,"Fixed",1,0,0,0,0,"7 Marketing",29,60,0,"","","","% Negotiated contracts","","","","DefaultA=@Kier29;[A]",0,,,,,"",0,,"",,2002-12-12 22:28:54,2002-12-12 22:29:44,,,
71,"Monthly Summary KPIs","Kier Regional","To compare turnover generated by D+B contracts to total turnover during the preceding twelve-month period","Turnover from D+B Contracts",30,1,1,"D+B contract turnover %","","","","",False,55,"Fixed",1,0,0,0,0,"7 Marketing",30,50,0,"","","","% D+B contracts","","","","DefaultA=@Kier30;[A]",0,,,,,"",0,,"",,2002-12-12 22:29:14,2003-01-14 08:28:15,,,
72,"Monthly Summary KPIs","Kier Regional","To determine the turnover per employee during the preceding twelve-month period.","Turnover per staff member",0.25,1,1,"Turnover per staff member (£M)","","","","",False,55,"Currency",2,,,,,"8 Finance",33,0.29999999999999999,0.20000000000000001,"","","","Turnover per staff member (£M)","","","","DefaultA=@Kier33;[A]",0,,,,,"",4,,"",17,2002-12-12 22:32:46,2003-04-04 11:27:16.187000000,,,4
73,"Monthly Summary KPIs","Kier Regional","To determine cash as a percentage of annual turnover during the preceding twelve-month period.","Cash as a % of turnover",15,1,1,"Cash %","","","","",False,55,"Fixed",1,0,0,0,0,"8 Finance",34,20,0,"","","","Cash as a % of turnover","","","","DefaultA=@Kier34;[A]",0,,,,,"",0,,"",,2002-12-12 22:35:30,2002-12-12 22:36:00,,,
74,"Monthly Summary KPIs","Kier Regional","To determine the percentage of staff costs and overheads compared to turnover during the preceding twelve-month period","Total staff cost and overheads",10,1,1,"Staff costs and overheads %","","","","",False,55,"Fixed",1,0,0,0,0,"8 Finance",35,5,15,"","","","Total staff cost & overhead %","","","","DefaultA=@Kier35;[A]",0,,,,,"",0,,"",,2002-12-12 22:38:19,2002-12-23 09:37:24,,,
75,"Monthly Summary KPIs","Kier Regional","To determine the value of retentions outstanding beyond the defects liability period and compare this with turnover.","Retention beyond DLP",2,1,1,"% retention beyond DLP","","","","",False,55,"Fixed",1,0,0,0,0,"8 Finance",36,0,5,"","","","% of retention beyond DLP","","","","DefaultA=@Kier36;[A]",0,,,,,"",0,,"",,2002-12-12 22:40:32,2002-12-12 22:40:57,,,
76,"Monthly Summary KPIs","Kier Regional","To determine the average number of days taken by debtors to settle their accounts during the preceding twelve-month period.","Debtors days",30,1,1,"Average debtors days","","","","",False,55,"Fixed",1,0,0,0,0,"8 Finance",37,15,45,"","","","Average debtor days","","","","DefaultA=@Kier37;[A]",0,,,,,"",0,,"",,2002-12-12 22:42:38,2002-12-12 22:43:00,,,
77,"Monthly Summary KPIs","Kier Regional","To measure the time taken to settle final accounts during the preceding twelve-month period.","Final account settlement",5.9999999999999998E-2,1,1,"% time to settle final account","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",38,0.02,0.12,"","","","% time to settle final account","","","","DefaultA=@Kier38;[A]",0,,,,,"",0,,"",,2002-12-12 22:50:57,2002-12-12 22:51:22,,,
78,"Monthly Summary KPIs","Kier Regional","To measure the reliability of cost estimates for construction.","Predictability cost",100,1,1,"% Change in cost","","","","",False,55,"Fixed",1,,,,,"9 Commercial",39,100,0,"","","","% Change in cost","","","#,##0.0","DefaultA=@Kier39;[A]",0,,,,,"",4,,"",17,2002-12-12 22:58:54,2003-04-04 11:27:48.903000000,,,4
79,"Monthly Summary KPIs","Kier Regional","To compare final operating profit generated by key accounts with key account turnover at final certificate","Final operating profit - key accounts",7.5,1,1,"Final operating profit %","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",40,15,0,"","","","Final operating profit %","","","","DefaultA=@Kier40;[A]",0,,,,,"",0,,"",,2002-12-12 23:01:30,2003-01-14 08:39:53,,,
81,"Monthly Summary KPIs","Kier Regional","To compare final operating profit generated on negotiated contracts with turnover on negotiated contracts at final certificate","Final operating profit - negotiated",7.5,1,1,"Fianl operating profit %","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",41,15,0,"","","","Final operating profit %","","","","DefaultA=@Kier41;[A]",0,,,,,"",0,,"",,2002-12-12 23:04:38,2003-01-14 08:40:32,,,
82,"Monthly Summary KPIs","Kier Regional","To compare final operating profit generated on D+B contracts with turnover on D+B contracts at final certificate.","Final operating profit - D+B",7.5,1,1,"Final operating profit %","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",42,15,0,"","","","Final operating profit %","","","","DefaultA=@Kier42;[A]",0,,,,,"",0,,"",,2002-12-12 23:04:57,2003-01-14 08:41:11,,,
83,"Monthly Summary KPIs","Kier Regional","To ascertain the buying savings achieved on materials orders placed in the preceding twelve month period.","Buying gains - materials",15,1,1,"Buying gains %","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",43,20,0,"","","","Buying gains","","","","DefaultA=@Kier43;[A]",0,,,,,"",0,,"",,2002-12-12 23:07:28,2003-01-14 08:42:11,,,
84,"Monthly Summary KPIs","Kier Regional","To ascertain the buying savings achieved on sub-contract orders placed in the preceding twelve-month period.","Buying gains - sub-contract",7,1,1,"Buying gains %","","","","",False,55,"Fixed",1,0,0,0,0,"9 Commercial",44,10,0,"","","","Buying gains","","","","DefaultA=@Kier44;[A]",0,,,,,"",4,,"",17,2002-12-12 23:09:56,2003-03-03 21:50:10.700000000,,,4
137,"Monthly Summary KPIs","Kier Regional","To measure the value of projects which are completed early or on time during the preceding twelve-month period","Value complete within original contract",60,1,1,"% complete by value","","","","",False,55,"Fixed",1,0,0,0,0,"4 Construction Activities",17,100,0,"","","","% Complete by value","","","","DefaultA=@Kier17;[A]",0,,,,,"",0,,"",,2002-12-20 09:02:41,2002-12-20 11:07:17,,,
138,"Monthly Summary KPIs","Kier Regional","To measure the value of projects which are completed within extended contract period during the preceding twelve-month period","Value complete within extended contract",100,1,1,"% Complete by value","","","","",False,55,"Fixed",1,0,0,0,0,"4 Construction Activities",19,100,0,"","","","% Complete by value","","","","DefaultA=@Kier19;[A]",0,,,,,"",0,,"",,2002-12-20 09:09:19,2003-01-14 08:07:59,,,
140,"Monthly Summary KPIs","Kier Regional","To determine the ratio of construction contracts won to the value of tenders submitted in competition during the preceding twelve-month period.","Strike rate (value)",6,1,1,"Strike rate (value)","","","","",False,55,"Fixed",2,0,0,0,0,"5 Estimating",21,2,10,"","","","Strike rate","","","","DefaultA=@Kier21;[A]",0,,,,,"",0,,"",,2002-12-20 11:09:26,2003-01-14 08:11:44,,,
142,"Monthly Summary KPIs","Kier Regional","To determine the quantity of tenders prepared by each Estimator during the preceding twelve-month period.","Tenders per estimator (number)",1,1,1,"Estimates per Estimator","","","","",False,55,"Fixed",1,0,0,0,0,"5 Estimating",22,1.3300000000000001,0.67000000000000004,"","","","Estimates per estimator","","","","DefaultA=@Kier22;[A]",0,,,,,"",0,,"",,2002-12-20 11:16:13,,,,
370,"Environmental","Kier Company Monthly","To measure the energy used at permanent office locations during the preceding month.","Energy usage",4,1,1,"Cost of energy used (£)","Floor area in m2","","","",False,55,"Currency",2,1,0,0,0,"2 Environmental",1,2,7,"","","","Energy use in £/m2","","","","[A]/[B]",0,,,,,"Definition:
The total cost of energy used for a business unit per m² office floor area over the same period.

Method:
Divide the total cost of energy used at the office location in the period by the office floor area.

Formula:
Energy use in £/m² =",0,,"",,2003-02-05 07:50:17,,,,
371,"Environmental","Kier Company Monthly","To measure the level of environmental training provision during the preceding month.","Environmental training",0.14999999999999999,1,1,"No of employee environmental training days","No of employee working days","","","",False,55,"Fixed",1,1,0,0,0,"2 Environmental",2,0.25,0,"","","","Environmental Training","","","","[A]/[B]*100",0,,,,,"Definition:
The number of environmental training days provided to employees, compared to the total number of employee working days in the same period.

Method:
Divide the number of employee environmental training days by the number of employee working",0,,"",,2003-02-05 07:50:17,,,,
405,"Estimating","Kier Company Monthly","To determine the ratio of construction contracts won to the number of tenders submitted in competition during the relevant month.","Strike rate (number)",4,1,1,"Number of tenders submitted","Number of contracts won","","","",False,55,"Fixed",1,1,,,,"5 Estimating",1,1,10,"","","","Strike rate ratio 1:","","","","[A]/[B]",0,,,,,"1. A 'contract won' does not include those projects where the lowest bid is submitted but the job does not progress.
2. A 'tender' is defined as all competitive bids prepared by the Estimating Department with the exception of the following:
Projects",4,,"",17,2003-02-05 07:53:31,2003-04-23 16:17:53.467000000,,,4
406,"Estimating","Kier Company Monthly","To determine the ratio of construction contracts won to the value of tenders submitted in competition during the relevant month.","Strike rate (value)",4,1,1,"Value of tenders submitted (£M)","Value of contracts won (£M)","","","",False,55,"Fixed",1,1,,,,"5 Estimating",2,1,10,"","","","Strike rate ratio 1:","","","","[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 07:53:31,2003-04-23 16:18:34.437000000,,,4
407,"Estimating","Kier Company Monthly","To determine the quantity of tenders prepared by each Estimator during the relevant month.","Tenders per estimator (number)",2,1,1,"Number of estimates","Number of estimators","","","",False,55,"Fixed",1,1,,,,"5 Estimating",3,3,1,"","","","Estimates per estimator","","","","C1=[B];[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 07:53:31,2003-04-23 16:20:03.643000000,,,4
408,"Estimating","Kier Company Monthly","To determine the value of tenders prepared by each Estimator during the relevant month.","Tenders per estimator (value)",2,1,1,"Value of tenders prepared (£M)","Number of estimators","","","",False,55,"Currency",2,1,,,,"5 Estimating",4,5,1,"","","","£ million per estimator","","","","DefaultB=[C1];[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 07:53:31,2003-04-23 16:21:11.707000000,,,4
427,"Supply Chain","Kier Company Monthly","To measure the number of accidents per 100,000 hours worked (the Accident Frequency Rate, or AFR) per each supply chain partner.","Sub-contract AFR",0,1,1,"SCP AFR","","","","",False,55,"Fixed",1,0,0,0,0,"3 Supply Chain",1,0,1,"","","","Performance (AFR)","","","#,##0","[A]",,,,,,"",0,,"",,2003-02-05 07:53:55,,,,
459,"Construction Activities","Kier Company Monthly","To measure the number of projects which are completed early or on time during the relevant month.","No. complete within original contract",60,1,1,"No. of projects completed early or on time","Total no. of projects completed","","","",False,55,"Fixed",1,1,,,,"4 Construction Activities",1,100,0,"","","","% Complete","","","","[A]/[B]*100",0,,,,,"1. Projects of value <£05M not to be included.
",4,,"",17,2003-02-05 07:54:15,2003-04-23 16:11:52.943000000,,,4
460,"Construction Activities","Kier Company Monthly","To measure the value of projects which are completed early or on time during the relevant month.","Value complete within original contract",60,1,1,"Turnover completed early or on time (£Ms)","Turnover all projects completed (£Ms)","","","",False,55,"Fixed",1,1,,,,"4 Construction Activities",2,100,0,"","","","% Complete by value","","","","[A]/[B]*100",0,,,,,"1. Projects of value <£05M not to be included.
2. Value means certified value.
3. Turnover = certified restricted turnover (turnover from all projects of value <£.5M in th relevant month).",4,,"",17,2003-02-05 07:54:15,2003-04-23 16:12:30.770000000,,,4
461,"Construction Activities","Kier Company Monthly","To measure the number of projects which are completed within extended contract period during the relevant month.","No. complete within extended contract",100,1,1,"No. of projects completed within extended period","Total no. of projects completed","","","",False,55,"Fixed",1,1,,,,"4 Construction Activities",3,100,0,"","","","% Complete","","","","[A]/[B]*100",0,,,,,"1. Projects of value <£.5M not to be included.
",4,,"",17,2003-02-05 07:54:15,2003-04-23 16:13:20.617000000,,,4
462,"Construction Activities","Kier Company Monthly","To measure the value of projects which are completed within extended contract period during the relevant month.","Value complete within extended contract",100,1,1,"Value completed within extended period (£Ms)","Turnover from projects completed (£Ms)","","","",False,55,"Fixed",1,1,,,,"4 Construction Activities",4,100,0,"","","","% Complete by value","","","","[A]/[B]*100",0,,,,,"1. Projects of value <£05M not to be include.
2. Value means certified value.
3. Turnover = certified restricted turnover (turnover from all projects of value <£.5M in th relevant month).",4,,"",17,2003-02-05 07:54:15,2003-04-17 15:16:32.960000000,,,4
528,"Marketing","Kier Company Monthly","To compare turnover generated by key accounts to total turnover during the relevant month.","Turnover from key accounts",30,1,1,"Key Accounts Turnover (£Ms)","Total Turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"7 Marketing",1,100,0,"a","a","a","% of Key Accounts","","","","C1=[B];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:54:50,2003-04-23 16:32:45.083000000,,,4
529,"Marketing","Kier Company Monthly","To compare turnover generated by two-stage contracts to total turnover during the relevant month.","Turnover from two-stage contracts",30,1,1,"Two-stage contract turnover (£Ms)","Total turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"7 Marketing",2,100,0,"","","","% Two-stage contracts","","","","DefaultB=[C1];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:54:50,2003-04-23 16:33:48.587000000,,,4
530,"Marketing","Kier Company Monthly","To compare turnover generated by negotiated contracts to total turnover during the relevant month.","Turnover from negotiated contracts",40,1,1,"Negotiated contract turnover (£Ms)","Total turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"7 Marketing",3,100,0,"a","a","a","% Negotiated contracts","","","","DefaultB=[C1];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:54:50,2003-04-23 16:34:39.320000000,,,4
531,"Marketing","Kier Company Monthly","To compare turnover generated by D+B contracts to total turnover during the relevant month.","Turnover from D+B Contracts",50,1,1,"D+B contract turnover (£Ms)","Total turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"7 Marketing",4,100,0,"","","","% D+B contracts","","","","DefaultB=[C1];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:54:50,2003-04-23 16:35:35.510000000,,,4
561,"Finance","Kier Company Monthly","To determine the turnover per employee during the relevant month.","Turnover per staff member",0.40000000000000002,1,1,"Total Turnover (£Ms)","Total full time equivalent employees","","","",False,55,"Fixed",2,1,,,,"8 Finance",1,0.5,0.20000000000000001,"","","","Turnover per staff member (£Ms)","","","","C1=[A];[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 07:55:07,2003-04-24 10:36:13.920000000,,,4
562,"Finance","Kier Company Monthly","To determine cash as a percentage of annual turnover during the relevant month.","Cash as a % of turnover",15,1,1,"Cash (£Ms)","Group loans (£Ms)","Total Turnover (£Ms)","","",False,55,"Fixed",2,2,2,,,"8 Finance",2,30,-15,"","","","Cash as a % of turnover","","","","DefaultC=[C1];(([A]+[B])/[C])*100",0,,,,,"",4,,"",17,2003-02-05 07:55:07,2003-04-23 16:25:34.807000000,,,4
563,"Finance","Kier Company Monthly","To determine the percentage of staff costs and overheads compared to turnover during the relevant month.","Total staff cost and overheads",11.5,1,1,"Staff costs and overheads (£Ms)","Total Turnover (£Ms)","","","",False,55,"Fixed",2,2,0,0,0,"8 Finance",3,9,16,"","","","Total staff cost & overhead %","","","","DefaultB=[C1];[A]/[B]*100",0,0,0,0,0,"",4,,"",0,2003-02-05 07:55:07,2003-05-16 13:48:37.153000000,,,0
564,"Finance","Kier Company Monthly","To determine the value of retentions outstanding beyond the defects liability period and compare this with turnover.","Retention beyond DLP",0,1,1,"Value of retentions (£Ms)","Restricted Turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"8 Finance",4,0,5,"","","","% of retention beyond DLP","","","","[C2]=[B];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:55:07,2003-04-23 16:27:24.873000000,,,4
565,"Finance","Kier Company Monthly","To determine the average number of days taken by debtors to settle their accounts during the relevant month.","Debtors days",30,1,1,"Contract sales ledger balance (£Ms)","Sales ledger balance (£Ms)","Restricted Turnover (£Ms)","","",False,55,"Fixed",2,2,2,,,"8 Finance",5,15,45,"","","","Average debtor days","","","","DefaultC=[C2];(([A]+[B])/[C])*365",0,,,,,"",4,,"",17,2003-02-05 07:55:07,2003-04-23 16:28:19.233000000,,,4
595,"Commerical","Kier Company Monthly","To measure the time taken to settle final accounts during the relevant month.","Final account settlement",180,1,1,"Total no days between PC and settlement","Number of projects","","","",False,55,"Fixed",1,1,,,,"9 Commercial",1,0,1000,"","","","% time to settle final account","","","","A/B",0,,,,,"",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:00:49.097000000,,,4
596,"Commerical","Kier Company Monthly","To compare final operating profit generated by key accounts with key account turnover at final certificate","Final operating profit - key accounts",7.5,1,1,"Final operating profit (£Ms)","Key Account turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"9 Commercial",2,15,-5,"","","","Final operating profit %","","","","[A]/[B]*100",0,,,,,"1. Final operating profit generated by key accounts, after recharge, as shown in the quarterly forecast.
2. Key account means an account identified in the Kier Construction Division Marketing Forum status summary as a Kier Group account.
3. Turnover means",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:01:45.960000000,,,4
597,"Commerical","Kier Company Monthly","To compare final operating profit generated on negotiated contracts with turnover on negotiated contracts at final certificate","Final operating profit - negotiated",10,1,1,"Final operating profit (£Ms)","Negotiated contracts turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"9 Commercial",3,15,-5,"","","","Final operating profit %","","","","[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:02:52.130000000,,,4
598,"Commerical","Kier Company Monthly","To compare final operating profit generated on D+B contracts with turnover on D+B contracts at final certificate.","Final operating profit - D+B",7.5,1,1,"Final operating profit (£Ms)","D+B Contract turnover (£Ms)","","","",False,55,"Fixed",2,2,,,,"9 Commercial",4,15,-5,"","","","Final operating profit %","","","","[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:03:33.867000000,,,4
599,"Commerical","Kier Company Monthly","To ascertain the buying savings achieved on materials orders placed in the relevant month.","Buying gains - materials",15,1,1,"Sum of buying savings (£Ms)","Sum of order values (£Ms)","","","",False,55,"Fixed",2,2,,,,"9 Commercial",5,20,-5,"","","","Buying gains","","","","[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:04:16.150000000,,,4
600,"Commerical","Kier Company Monthly","To ascertain the buying savings achieved on sub-contract orders placed in the relevant month","Buying gains - sub-contract",7.5,1,1,"Sum of buying savings (£Ms)","Sum of s/c order values (£Ms)","","","",False,55,"Fixed",2,2,,,,"9 Commercial",6,10,-5,"","","","Buying gains","","","","[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 07:55:20,2003-04-23 16:05:03.993000000,,,4
605,"Supply Chain","Kier Project End","To measure the volume of materials supplied by our supply chain partner suppliers on the project.","Turnover with SCPs (materials)",7,1,1,"Value of materials (£Ms)","Turnover (£Ms)","","","",False,55,"Fixed",2,2,0,0,0,"3 Supply Chain",1,10,0,"","","","Turnover with SCP (materials)","","","","C1=[B];[A]/[B]*100",0,,,,,"",0,,"",,2003-02-05 08:43:31,,,,
607,"Supply Chain","Kier Project End","To measure the volume of work carried out by our supply chain partners sub-contractors on this project.","Turnover with SCPs (sub-con)",40,1,1,"Value of work executed - sub-contract (£Ms)","Turnover (£Ms)","","","",False,55,"Fixed",2,2,0,0,0,"3 Supply Chain",2,60,0,"","","","Turnover with SCP (sub-con)","","","","DefaultB=[C1];[A]/[B]*100",0,,,,,"",4,,"",17,2003-02-05 08:43:31,2003-03-03 21:34:01.163000000,,,4
615,"Construction Activities","Kier Project End","To assess the level of defects, at Practical Completion.","Defects at PC",0,1,1,"No of defects at end of project","Project turnover to date (£Ms)","","","",False,55,"#,##0.0",1,2,,,,"4 Construction Activities",1,0,100,"","","","Defect per £M","","","","[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 15:06:04,2003-04-23 16:14:30.697000000,,,4
633,"Marketing","Kier Project End","Determine the client's overall level of satisfaction with the completed product/facility.

Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - product",75,1,2,"Client satisfaction - product (1 - 10)","","","","",False,55,"#,##0",1,,,,,"7 Marketing",1,100,0,"","","","Performance (number)","","","#,##0.0","[A]",,,,,,"Carry out a post project review to determine the clients level of satisfaction. Typically this question would be asked as part of a more comprehensive review or survey.



Satisfaction should be expressed as a number between 1 and 10. Where:

10 = T",4,,"",17,2003-02-05 15:09:51,2003-04-09 11:34:56.060000000,,,4
634,"Marketing","Kier Project End","Determine the client's satisfaction with the consultants and main contractor during the project.

Use a 1 to 10 scale where 10=Totally Satisfied and 1=Totally Dissatisfied.","Client satisfaction - service",75,1,2,"Client satisfaction - service (1 - 10)","","","","",False,55,"#,##0",1,,,,,"7 Marketing",2,100,0,"","","","Performance (number)","","","#,##0.0","[A]",,,,,,"Carry out a post project review to determine the clients level of satisfaction. Typically this question would be asked as part of a more comprehensive review or survey.

Satisfaction should be expressed a number between 1 and 10. Where:
10 = Tota",4,,"",17,2003-02-05 15:10:05,2003-04-09 11:34:15.590000000,,,4
642,"Commerical","Kier Project End","To measure the reliability of cost estimates for construction.","Predictability cost",100,1,2,"Actual construction cost","Estimated construction cost","","","",False,55,"#,##0.0",2,2,0,0,0,"9 Commercial",1,100,0,"","","","% Change in cost","","","#,##0.0","(([A]-[B])/[B])*100",0,,,,,"",4,,"",17,2003-02-05 15:18:48,2003-03-03 22:09:47.013000000,,,4
643,"Construction Activities","Kier Project End","To measure the reliability of time estimates for construction.","Predictability time",100,1,2,"Actual construction time","Estimated construction time","","","",False,55,"#,##0.0",1,1,,,,"4 Construction Activities",2,100,0,"Kier Project End","Kier Project End","","Calculated score","","","#,##0.0","(([A]-[B])/[B])*100",0,,,,,"",4,,"",17,2003-02-05 15:46:01,2003-04-23 16:15:59.120000000,,,4
645,"Construction Activities","Kier Project End DLP","To assess the level of defects during the defects liability period.","No. of new defects during DLP",5,1,1,"Aggregate no of defects at end of DLP","Turnover £M","","","",False,55,"Fixed",1,2,0,0,0,"4 Construction Activities",1,0,100,"","","","Defects per £M","","","","[A]/[B]",0,,,,,"",0,,"",,2003-02-05 15:56:13,,,,
652,"Personnel","Kier Company Monthly","The number of direct employees that have left, and been replaced in the month, expressed as a percentage of the number of direct employees.","Staff turnover",95,1,2,"Direct employees replaced in relevant month (no.)","Average direct employees in relevant month (no.)","","","",False,55,"#,##0",1,1,,,,"6 Personnel",1,100,0,"","","","Performance score (%)","","","#,##0","C1=[B];([A]/[B])*100",0,,,,,"From your firms records, determine the number of direct employees that have left your employment and been replaced and the average number of direct employees in the relevant month.



Performance score (%)staff turnover =Number of direct employees who hav",4,,"",17,2003-02-05 16:11:25,2003-04-23 16:37:30.140000000,,,4
653,"Personnel","Kier Company Monthly","The number of working days lost in the relevant month due to sickness per direct employee.","Sickness absence",95,1,2,"Total working days lost due to sickness (no.)","Avg. no. of direct employees in last month (no.)","","","",False,55,"#,##0",1,1,,,,"6 Personnel",2,100,0,"","","","Sickness absence (days per employee per year)","","","#,##0.0","DefaultB=[C1];[A]/[B]",0,,,,,"From your firm's records determine the total number of working days lost due to sickness among direct employees and the average number of direct employees in the relevant month.



Performance score (days) sickness absence = Total number of working days l",4,,"",17,2003-02-05 16:11:38,2003-04-23 16:38:47.733000000,,,4
654,"Personnel","Kier Company Monthly","The number of training days provided per employee in the relevant month.","Training",5,1,1,"Total training days provided last month (no.)","Avg. no. of direct employees in last month (no.)","","","",False,55,"#,##0",1,1,,,,"6 Personnel",3,20,0,"","","","Training days per employee","","","#,##0.0","DefaultB=[C1];[A]/[B]",0,,,,,"From your firms records, determine the total number of training days provided and the average number of employees in the relevant month.



Performance score (days) training = Total number of training days provided in the relevant month DIVIDED BY Averag",4,,"",17,2003-02-05 16:11:56,2003-04-23 16:40:04.550000000,,,4
655,"Monthly KPIs","Kier Project","To measure the cost of disposing of construction site or building maintenance waste in the preceding month.","Cost of waste",0.25,1,1,"Cost of waste disposal (£)","Last months certified project turnover (£Ms)","","","",False,55,"Fixed",2,2,0,0,0,"2 Environmental",3,0.10000000000000001,0.5,"Kier Project End","Marketing","a","Cost of waste","","","","[A]/([B]*1000000)*100",0,,,,,"Method:
Divide the total cost of waste disposal for the project in the preceding month by the turnover of the project for the same period, and express as a percentage.

Formula:
Cost of Waste = Cost of waste disposal/Turnover x 100

Example:
Cost ",0,,"",,2003-02-05 16:17:10,,,,
656,"Monthly KPIs","Kier Project","To measure the number of reportable accidents per 100,000 employed - the Accident Incidence Rate (AIR). The AIR formula makes no allowance for variations in part-time work or overtime.","Accident incidence rate",100,1,1,"No. of reportable accidents (number)","Avg no. of directly employed (number)","Avg no. of subcontractors employed (number)","","",False,55,"#,##0",1,1,1,,,"1 Safety",1,0.12,0.28999999999999998,"","","","Performance (AIR)","","","#,##0","C1=[A];C2=[B];C3=[C];IIF(isnull([C]),Null,CInt(([A]/([B]+[C]))*1000)*100)",,,,,,"Objective

To measure the number of reportable accidents per 100,000 employed -the accident

incidence rate (AIR). The AIR formula makes no allowance for variations in part-time work or

overtime.

Many organisations measure accidents per 100,000 hours wo",2,,"",42,2003-02-05 16:17:10,2003-04-30 05:42:45.110000000,,,2
657,"Monthly KPIs","Kier Project","To measure the number of environmental infringements on the project in the preceding month..","Infringements",0,1,1,"Enter the total number of infringements","","","","",False,55,"#,##0",1,0,0,0,0,"2 Environmental",4,0,2,"Kier Project End","Kier Project End","","No of infringements","","","","[A]",0,,,,,"",0,,"",,2003-02-05 16:17:10,,,,
658,"Monthly KPIs","Kier Project","The Accident Frequency Rate (AFR) gives the accident rate per 100,000 person hours worked. Unlike the AIR, the AFR formula makes allowance for variations in part-time work or overtime","Accident frequency rate",0,1,1,"No. of reportable accidents (number)","Avg no. of directly employed (number)","Avg no. of subcontractors employed (number)","","",False,55,"Fixed",1,1,1,,,"1 Safety",2,250,1250,"","","","Performance (AFR)","","","#,##0","DefaultA=[C1];DefaultB=[C2];DefaultC=[C3];([A]/(([B]+[C])*2163)*100000)",,,,,,"",2,,"",42,2003-02-05 16:17:10,2003-04-30 05:42:01.187000000,,,2
706,"Supply Chain","Kier Project End SCP","To assess the level of Supply Chain Partner (sub-contractor) defects, at Practical Completion.","Sub-contract defects at PC",5,1,1,"No of defects at end of project","Tunover (£Ms)","","","",False,55,"Fixed",1,2,,,,"3 Supply Chain",2,0,50,"","","","Defect per £M","","","","DefaultB=[C1];[A]/[B]",0,,,,,"",4,,"",17,2003-02-05 17:24:31,2003-04-17 16:07:15.023000000,,,4
712,"Supply Chain","Kier Project End SCP","To measure the reliability of Supply Chain Partner (Sub-contractor) time estimates for construction on this project.","Sub-contract predictability time",0,1,2,"Actual construction time","Estimated construction time","","","",False,55,"#,##0.0",1,1,,,,"3 Supply Chain",1,100,0,"","","","Performance %","","","#,##0.0","([A]-[B])/[B]*100",0,,,,,"",4,,"",17,2003-02-06 14:25:24,2003-04-09 16:44:56.303000000,,,4
716,"Child","Child","How much do you like banana's?","Banana's",8,1,1,"100 - 91%","90 - 81%","80 - 71%","70 - 61%","60 - 51%",True,52,"#,##0",10,9,8,7,6,"1. Fruit",1,10,1,"","","","50 - 41%","40 - 31%","30 - 21%","20 - 11%","10 - 0%",5,4,3,2,1,"",,,"",0,2003-05-19 11:16:25,2003-05-19 11:18:00,0,0,0
722,"Child 2","Child","How much do you like banana's?","Banana's",8,1,1,"100 - 91%","90 - 81%","80 - 71%","70 - 61%","60 - 51%",False,52,"",10,9,8,7,6,"1. Fruit",1,10,1,"","","","50 - 41%","40 - 31%","30 - 21%","20 - 11%","10 - 0%",5,4,3,2,1,"",,,"",0,2003-05-23 12:56:01.187000000,2003-05-23 12:56:01.187000000,0,0,0
723,"Child 3","Child","How much do you like banana's?","Banana's",8,1,1,"100 - 91%","90 - 81%","80 - 71%","70 - 61%","60 - 51%",False,52,"",10,9,8,7,6,"1. Fruit",1,10,1,"","","","50 - 41%","40 - 31%","30 - 21%","20 - 11%","10 - 0%",5,4,3,2,1,"",,,"",0,2003-05-23 12:56:25.903000000,2003-05-23 12:56:25.903000000,0,0,0
741,"Measures","_Leah","blah","Scored Answers",4,1,1,"Excellent","Good","Average","Below Average","Poor",True,52,"#,##0",5,4,3,2,1,"1. Measures",1,5,1,"","","","","","","","",0,0,0,0,0,"",,,"",0,2003-05-29 14:40:01.670000000,2003-05-29 14:40:38.403000000,0,0,0
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 07:38:00
Wow that was a lot of info. I don't really have time to go through it all, since I'm supposed to work here :)

With DDL, I meant the code to create the table:

CREATE TABLE blah ...

And what you are trying to do with that specific procedure.
Go to Top of Page

leahsmart
Posting Yak Master

133 Posts
Posted - 2003-05-30 : 07:40:30
If the template is not in use sent back false, otherwise send true. The above was a comma derlimted thingy, you can import into the database to test the code.


CREATE TABLE [tblScoreDescription] (
[ScoreDescriptionID] [int] IDENTITY (1, 1) NOT NULL ,
[Phase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScoreDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShortScoreDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StandardTarget] [float] NULL ,
[Weighting] [float] NULL ,
[TargetIncrease] [float] NULL ,
[Answer1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Survey] [bit] NULL ,
[ScorecardType] [smallint] NULL ,
[ScoreType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score1] [smallint] NULL ,
[Score2] [smallint] NULL ,
[Score3] [smallint] NULL ,
[Score4] [smallint] NULL ,
[Score5] [smallint] NULL ,
[SectionTitle] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SequenceNumber] [smallint] NULL ,
[Best] [float] NULL ,
[Worst] [float] NULL ,
[ChildType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChildPhase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChildCalc] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer6] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer7] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer8] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer9] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer10] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score6] [smallint] NULL ,
[Score7] [smallint] NULL ,
[Score8] [smallint] NULL ,
[Score9] [smallint] NULL ,
[Score10] [smallint] NULL ,
[Guidance] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConsolidatorID] [int] NULL ,
[DataSetCountMin] [int] NULL ,
[GuidanceHTML] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrivacyOption] [int] NULL ,
[DateAdded] [datetime] NULL ,
[DateLastUpdated] [datetime] NULL ,
[ABUserID] [int] NULL ,
[ABMemberID] [int] NULL ,
[UpdatedByUserID] [int] NULL ,
CONSTRAINT [PK_tblScoreDescription] PRIMARY KEY CLUSTERED
(
[ScoreDescriptionID]
) ON [PRIMARY]
) ON [PRIMARY]
GO






Edited by - leahsmart on 05/30/2003 07:41:37
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-05-30 : 07:49:57
OK, another try :)

CREATE PROCEDURE [dbo].[sp__DPM_TemplateMark]

@CompanyType VarChar(50),
@Phase VarChar(50),
@survey bit OUTPUT

AS

DECLARE @NumberRows INT

SELECT DISTINCT
CompanyType,
Phase,
Survey
FROM tblScoreDescription
WHERE CompanyType = @CompanyType
AND
Phase = @Phase

SET @NumberRows = @@ROWCOUNT

SELECT @survey =
CASE
WHEN @NumberRows = 2 THEN 1
WHEN @NumberRows = 1 AND Survey = 1 THEN 0
WHEN @NumberRows = 1 AND Survey = 0 THEN 1
END
FROM tblScoreDescription
WHERE CompanyType = @CompanyType AND Phase = @Phase

RETURN @survey
GO
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site