June 21, 2004 at 1:49 am
hello,
can anyone pls help me build the sp for this problem. I need to update the end_date column with the next start date from the next row of data. Im attaching sample data to make it easier to understand
Before Update
| id | svc_id | stage | start_date | end_date |
| 1 | 2 | 1 | 5/10/2004 | null |
| 2 | 2 | 2 | 5/11/2004 | null |
| 3 | 2 | 3 | 5/12/2004 | null |
| 4 | 2 | 4 | 5/13/2004 | null |
| 5 | 3 | 1 | 5/14/2004 | null |
| 6 | 3 | 2 | 5/15/2004 | null |
| 7 | 3 | 3 | 5/16/2004 | null |
| 8 | 4 | 1 | 5/17/2004 | null |
| 9 | 4 | 2 | 5/18/2004 | null |
| 10 | 5 | 1 | 5/19/2004 | null |
| 11 | 5 | 2 | 5/20/2004 | null |
data should look like this after update procedure
| id | svc_id | stage | start_date | end_date |
| 1 | 2 | 1 | 5/10/2004 | 5/11/2004 |
| 2 | 2 | 2 | 5/11/2004 | 5/12/2004 |
| 3 | 2 | 3 | 5/12/2004 | 5/13/2004 |
| 4 | 2 | 4 | 5/13/2004 | null |
| 5 | 3 | 1 | 5/14/2004 | 5/15/2004 |
| 6 | 3 | 2 | 5/15/2004 | 5/16/2004 |
| 7 | 3 | 3 | 5/16/2004 | null |
| 8 | 4 | 1 | 5/17/2004 | 5/18/2004 |
| 9 | 4 | 2 | 5/18/2004 | null |
| 10 | 5 | 1 | 5/19/2004 | 5/20/2004 |
| 11 | 5 | 2 | 5/20/2004 | null |
id column is incremental
Thanks
June 21, 2004 at 2:02 am
Well, if the stage field is always in sequence then this should do it:
June 21, 2004 at 2:01 pm
You can try (this works, if stage is not in sequence)
update tempdata set end_date=(select start_date from tempdata t2
where t2.svc_id=tempdata.svc_id and t2.stage=(select min(stage) from tempdata t3 where t3.stage > tempdata.stage)
)
--- if stage is in sequence, you can try:
update tempdata set end_date=(select start_date from tempdata t2
where t2.svc_id=tempdata.svc_id and t2.stage=tempdata.stage+1)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply