Update problem

Question

Post reply

Update problem

ChetShah

SSC Rookie

Points: 43

August 16, 2005 at 6:00 am

#363464

Hi,

I'd like to update the FromStatus column with values from the previous rows of the ToStatus column. When the EndDate occurs FromStatus column of the next row should not be updated.

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

The results that i'm looking for are:

id	claimreference	FromStatus	ToStatus	StartDate	EndDate
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

I have looked into cursors but would like to avoid them if possible as i have a large table to update. Any help would be much appreciated

Chet

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Pallavi-252310 SSC Veteran Points: 259 More actions · Answer 1

Pallavi-252310

SSC Veteran

Points: 259

August 16, 2005 at 6:21 am

#582399

Pls Can u explain more abt it

I want to know when u want to cheange the data

It will b better if u tell the senario.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

August 16, 2005 at 6:30 am

#582403

Yup, is it for reporting or for a permanent table. If it's for a table then I strongly suggest against that design as it is denormalized. A simple self join will provide the previous status when needed.

ChetShah SSC Rookie Points: 43 More actions · Answer 3

Hi,

This is for a permanent table. The scenarios is the FromStatus column has all NULLS. it needs to be updated using the previous values from ToStatus column. for example,

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

After update the FromStatus in row2 (or where id=2) should be NewClaim.

Also when the EndDate column is NULL in row16 below:

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

Then in row 17 the FromStatus should not be updated and in row 18 normal update resumes from ToStatus value of row 17 = New Claim

Hope this makes it clearer.

Chet

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us

SSC Guru

Points: 294069

August 16, 2005 at 8:19 am

#582467

Same problem... it seems like a flawed design. It can be done with a self join.

VivekKumar Gupta Ten Centuries Points: 1159 More actions · Answer 5

--Hi,

--Following SCRIPT will serve your purpose.

DECLARE @ID int

SELECT @ID = Max(ID) From Sheet

--SELECT @ID

WHILE @ID <> 0

BEGIN

UPDATE Sheet Set

FromStatus

= (

SELECT

CASE IsNull(EndDate,1)

WHEN 1 THEN 'NULL'

ELSE ToStatus

END

FROM

Sheet

WHERE

ID = @ID

)

WHERE ID = @ID + 1

SET @ID = @ID - 1

END

--Any doubt, regarding the same, feel free 2 ask.

--Vivek

/**A strong positive mental attitude will create more miracles than any wonder drug**/

ChetShah SSC Rookie Points: 43 More actions · Answer 6

ChetShah

SSC Rookie

Points: 43

August 17, 2005 at 3:52 am

#582716

Hi Vivek,

Cheers mate - this works really well . Thanks once again...

Chet

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

As Remi mentioned design can be different and better.

Anyway for existing design updating using a loop will be a performance problem for large data rows. I would do it like

SET NOCOUNT ON

DECLARE @MyTable TABLE

(

MyID INT IDENTITY,

claimreference VARCHAR(20),

FromStatus VARCHAR(50),

ToStatus VARCHAR(50),

StartDate DATETIME,

EndDate DATETIME

)

SET DATEFORMAT DMY

INSERT @MyTable (claimreference, FromStatus, ToStatus, StartDate, EndDate)

SELECT 'DMARP00240', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

SELECT 'DMARP00240', NULL, 'Application Form Sent', '21/12/2004', '17/05/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '17/05/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '23/05/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '23/05/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '13/06/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '13/06/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '12/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '12/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '18/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Offer Sent', '18/07/2005', '22/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Offer Accepted', '22/07/2005', '26/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', '26/07/2005' UNION ALL

SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', NULL UNION ALL

SELECT 'DMARP01039', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

SELECT 'DMARP01039', NULL, 'Application Form Sent', '21/12/2004', '10/01/2005'

UPDATE A

SET

FromStatus = B.ToStatus

FROM

@MyTable A

JOIN

@MyTable B

ON

A.MyID = B.MyID + 1

WHERE

A.ToStatus <> 'New Claim'

SELECT * FROM @MyTable

Regards,
gova

Murthy-152277 Right there with Babe Points: 720 More actions · Answer 8

Hi Remi ,

The above query will work fine with the above data .If a record exists with 'New Claim' and Valid EndDate Value.Then it will fail. I think following will work.