Kevin

Thanks for responding

i take your point about providing some data creation scripts.

Please find my attempt below.

About your other comments. I can understand the hit on storage however I am intrigued about the performance hit.

if i were to use a code for the status, every time i insert or read the code i would have to have a join to another lookup table to receive from or provide the response to the user.

Is there much of a performance hit if a value is a 10 character string as against a 1 character string or an int.

The issue i have here is that the data is inserted and retrieved by a 3rd party interpreted application. its much faster for the user interface to receive the data that has to be displayed.

Thanks again

Peter

create table mydata (

ID varchar(10),

Stepnumber varchar(10),

Stepgroup varchar(10),

Stepstatus varchar(10),

Groupstatus varchar(10)

);

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('123','Step1','Group1','Approved','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('234','Step2','Group1','Complete','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('345','Step3','Group1','Incomplete','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('456','Step4','Group2','Bypassed','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('567','Step5','Group2','Approved','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('678','Step6','Group3','Approved','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('789','Step7','Group3','Approved','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('890','Step8','Group4','Omitted','');

insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('901','Step8','Group4','Completed','');

select * from mydata order by ID;

create table statustable (

statustring varchar(10),

statuspriority int

);

insert into statustable (statustring,statuspriority) values ('Bypassed', 10);

insert into statustable (statustring,statuspriority) values ('Omitted',20);

insert into statustable (statustring,statuspriority) values ('Approved', 30);

insert into statustable (statustring,statuspriority) values ('Complete', 40);

insert into statustable (statustring,statuspriority) values ('Incomplete', 50);

insert into statustable (statustring,statuspriority) values ('',50 );

insert into statustable (statustring,statuspriority) values (NULL,50);

select * from statustable order by statuspriority;