How to use store procedure to create a new field ?

[lucyc]

Hello,

I am new to stored procedure and I have no idea how to do this.
I have a table like this:
client# ServiceDate GroupCode ServiceCode
--------------------------------------------------
123 2/2/2003 0100 411
123 2/3/2003 0231 356
123 2/3/2003 0100 422
234 2/10/2003 0100 423
234 2/7/2003 0231 788
123 2/2/2003 0630 319
123 2/2/2003 0888 111
234 2/7/2003 0100 433

and I want to create a new field called NewField. The NewField value is the ServiceCode value if the GroupCode equal 0100, this NewField value will be NewField value for the same client which has the same ServiceDate for GroupCode 0100. It is hard to explain, but here is the output that I want to get:

client# ServiceDate GroupCode ServiceCode NewField
-----------------------------------------------------------
123 2/2/2003 0100 411 411
123 2/3/2003 0231 356 422
123 2/3/2003 0100 422 422
234 2/10/2003 0100 423 423
234 2/7/2003 0231 788 433
123 2/2/2003 0630 319 411
123 2/2/2003 0888 111 411
234 2/7/2003 0100 433 433

As you can see from the example, client# 123 received 3 different ServiceCode(411,319,111) on 2/2/2003. Since for GroupCode 0100 , the ServiceCode is 411, so the NewField values for client# 123 with service date on 2/2/2003 are 411. I hope you understand what I try to do. Thanks in advance.

 

[nigelrivett]

Is there always a single 0100 rec for each client per day?

alter table tbl add NewField varchar(3)

update tbl
set NewField = t2.ServiceCode
from tbl t1
join (select Client#, SeviceDate, ServiceCode from tbl where ServiceCode = '0100') t2
on t1.Client# = t2.Client# and t1.SeviceDate = t2.SeviceDate

If there isn't always a rec and you want another value then use a left outer join and coalesce.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[lucyc]

Yes. There is a single 0100 rec for each client per day. Instead of creating a new table or alter the table, can you create a recordset using stored procedure?