sql server programming: I am in the preliminary design of creating a table based off some other tables. The table is to show how long each of our clients was with us each time they were working with us. (They are long term unemployed that come to us for a set period of time during which we attempt to help them get a job).

I want to include a field called [Attempt] which will be filled with the numbers 1,2,3.

Essentially the table I can create at the moment looks like this.
ClientID StartDate EndDate
1 1/1/2000 2/2/2001
1 5/5/2001 10/10/2001
1 3/3/2003 6/6/2003
1 9/9/2003 Null (represents currently in this attempt)

I want to put an additional field like this in.
ClientID StartDate EndDate Attempt
1 1/1/2000 2/2/2001 1
1 5/5/2001 10/10/2001 2
1 3/3/2003 6/6/2003 3
1 9/9/2003 Null 4

I can do this using a cursor - running through each client in startdate order and adding the Attempt value in.

However I have managed to code the rest without a cursor. Since we will easily end up with tens of thousands of clients (maybe even 100,000), I was hoping to write something directly in SQL.

You can start with this kb:
http://support.microsoft.com/?kbid=186133


[quoted text, click to view]
tables. The table is to show how long each of our clients was with us each time
they were working with us. (They are long term unemployed that come to us for a
set period of time during which we attempt to help them get a job).
[quoted text, click to view]
easily end up with tens of thousands of clients (maybe even 100,000), I was
hoping to write something directly in SQL.
[quoted text, click to view]

This is a common pattern for temporal data. Somewhere you have a
procedure to insert a new event that find the only row where (end_date
IS NULL) and sets it to the CURRRENT_TIMESTAMP or a date passed as a
parameter.

Just add another column for "attempt_nbr" and in this procedure, set it
to (n+1) from the updated row or to ((SELECT COUNT(*) FROM Attempts
WHERE client_id = @my_guy) +1)with a subquery.

Same idea applies to doing the initial set-up:

UPDATE Attempts
SET attempt_nbr
= (SELECT SELECT COUNT(*)
FROM Attempts AS A1
WHERE A1.client_id = Attempts.client_id
AND A1.start_date <= Attempts.start_date);


--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Derek
What is about adding an IDENTITY property to the table?

[quoted text, click to view]
tables. The table is to show how long each of our clients was with us each
time they were working with us. (They are long term unemployed that come to
us for a set period of time during which we attempt to help them get a job).
[quoted text, click to view]
easily end up with tens of thousands of clients (maybe even 100,000), I was
hoping to write something directly in SQL.
[quoted text, click to view]

Thanks guys. Worked great.

So obvious once you've seen it.

[quoted text, click to view]