Update Problem - SQL Server Forums

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

SQL Server Development (2000)

Update Problem

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2005-08-15 : 11:11:48

Hi,

I have an update problem which i'm not sure if its best dealt with a cursor. The Table has the following columns

id,claimreference,FromStatus,ToStatus,StartDate,EndDate


1	DMARP00240	NULL	New Claim	06/12/2004	21/12/2004
2	DMARP00240	NULL	Application Form Sent	21/12/2004	17/05/2005
3	DMARP00240	NULL	Application Form Received	17/05/2005	17/05/2005
4	DMARP00240	NULL	Application Form Received	17/05/2005	23/05/2005
5	DMARP00240	NULL	Information Requested	23/05/2005	23/05/2005
6	DMARP00240	NULL	Information Requested	23/05/2005	13/06/2005
7	DMARP00240	NULL	Information Received	13/06/2005	13/06/2005
8	DMARP00240	NULL	Information Received	13/06/2005	12/07/2005
9	DMARP00240	NULL	Information Under Review	12/07/2005	12/07/2005
10	DMARP00240	NULL	Information Under Review	12/07/2005	18/07/2005
11	DMARP00240	NULL	Assistant Manager Review	18/07/2005	18/07/2005
12	DMARP00240	NULL	Assistant Manager Review	18/07/2005	18/07/2005
13	DMARP00240	NULL	Offer Sent	18/07/2005	22/07/2005
14	DMARP00240	NULL	Offer Accepted	22/07/2005	26/07/2005
15	DMARP00240	NULL	Completed	26/07/2005	26/07/2005
16	DMARP00240	NULL	Completed	26/07/2005	NULL
17	DMARP01039	NULL	New Claim	06/12/2004	21/12/2004
18	DMARP01039	NULL	Application Form Sent	21/12/2004	10/01/2005
19	DMARP01039	NULL	Application Form Received	10/01/2005	14/01/2005
20	DMARP01039	NULL	Information Requested	14/01/2005	06/04/2005
21	DMARP01039	NULL	Information Received	06/04/2005	06/04/2005
22	DMARP01039	NULL	Information Received	06/04/2005	27/06/2005
23	DMARP01039	NULL	Information Under Review	27/06/2005	27/06/2005
24	DMARP01039	NULL	Information Under Review	27/06/2005	15/07/2005
25	DMARP01039	NULL	Assistant Manager Review	15/07/2005	15/07/2005
26	DMARP01039	NULL	Assistant Manager Review	15/07/2005	18/07/2005
27	DMARP01039	NULL	Offer Sent	18/07/2005	22/07/2005
28	DMARP01039	NULL	Offer Accepted	22/07/2005	26/07/2005
29	DMARP01039	NULL	Completed	26/07/2005	26/07/2005
30	DMARP01039	NULL	Completed	26/07/2005	NULL

The FromStatus needs to be filled using ToStatus column. Also the FromStatus column in row 17 has to remain NULL as the EndDate in row 16 was NULL.

The result should be


1	DMARP00240	NULL	New Claim	06/12/2004	21/12/2004
2	DMARP00240	New Claim	Application Form Sent	21/12/2004	17/05/2005
3	DMARP00240	Application Form Sent	Application Form Received	17/05/2005	17/05/2005
4	DMARP00240	Application Form Received	Application Form Received	17/05/2005	23/05/2005
5	DMARP00240	Application Form Received	Information Requested	23/05/2005	23/05/2005
6	DMARP00240	Information Requested	Information Requested	23/05/2005	13/06/2005
7	DMARP00240	Information Requested	Information Received	13/06/2005	13/06/2005
8	DMARP00240	Information Received	Information Received	13/06/2005	12/07/2005
9	DMARP00240	Information Received	Information Under Review	12/07/2005	12/07/2005
10	DMARP00240	Information Under Review	Information Under Review	12/07/2005	18/07/2005
11	DMARP00240	Information Under Review	Assistant Manager Review	18/07/2005	18/07/2005
12	DMARP00240	Assistant Manager Review	Assistant Manager Review	18/07/2005	18/07/2005
13	DMARP00240	Assistant Manager Review	Offer Sent	18/07/2005	22/07/2005
14	DMARP00240	Offer Sent	Offer Accepted	22/07/2005	26/07/2005
15	DMARP00240	Offer Accepted	Completed	26/07/2005	26/07/2005
16	DMARP00240	Completed	Completed	26/07/2005	NULL
17	DMARP01039	NULL	New Claim	06/12/2004	21/12/2004
18	DMARP01039	New Claim	Application Form Sent	21/12/2004	10/01/2005
19	DMARP01039	Application Form Sent	Application Form Received	10/01/2005	14/01/2005
20	DMARP01039	Application Form Received	Information Requested	14/01/2005	06/04/2005
21	DMARP01039	Information Requested	Information Received	06/04/2005	06/04/2005
22	DMARP01039	Information Received	Information Received	06/04/2005	27/06/2005
23	DMARP01039	Information Received	Information Under Review	27/06/2005	27/06/2005
24	DMARP01039	Information Under Review	Information Under Review	27/06/2005	15/07/2005
25	DMARP01039	Information Under Review	Assistant Manager Review	15/07/2005	15/07/2005
26	DMARP01039	Assistant Manager Review	Assistant Manager Review	15/07/2005	18/07/2005
27	DMARP01039	Assistant Manager Review	Offer Sent	18/07/2005	22/07/2005
28	DMARP01039	Offer Sent	Offer Accepted	22/07/2005	26/07/2005
29	DMARP01039	Offer Accepted	Completed	26/07/2005	26/07/2005
30	DMARP01039	Completed	Completed	26/07/2005	NULL

Chet

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 11:41:03

UPDATE TABLE SET FromStatus=ToStatus WHERE EndDate IS NOT NULL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ChetShah
Starting Member

37 Posts

Posted - 2005-08-15 : 11:57:42

Hi,

The problem with this query is that it doesn't display the results of the ToStatus column of previous row.

1 DMARP00240 NULL New Claim 06/12/2004 21/12/2004
2 DMARP00240 NULL Application Form Sent 21/12/2004 17/05/2005

so in row 2 i'd like the result of ToStatus column from row 1 ie

1 DMARP00240 NULL New Claim 06/12/2004 21/12/2004
2 DMARP00240 New Claim Application Form Sent 21/12/2004 17/05/2005

Your query produces:

1 DMARP00240 New Claim New Claim 06/12/2004 21/12/2004
2 DMARP00240 Application Form Sent Application Form Sent 21/12/2004 17/05/2005

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 13:32:17

Ahhhh...

The order of data in a database is meaningless....who define what the previous row is?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-16 : 04:24:26

If you are sure you don't have holes in your id values you can outer join with the same table on a.id = b.id - 1...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources