Need help with cursor

Question

Post reply

Need help with cursor

vandana_j79

Mr or Mrs. 500

Points: 536
More actions
July 9, 2010 at 12:26 pm

#74299

Hi
I am trying to create cursor so need some help with the code
Below is the sample data...
id statusstart_datenew_date
1open24/05/197824/05/1978
1agreed24/06/197824/05/1978
1pending24/06/197824/05/2978
1closed25/07/197824/05/1978
1reopen26/08/197926/08/1979
1closed25/07/198026/08/1979
2closed03/07/198603/07/1986
2pending04/07/198703/07/1986
3open04/07/198604/07/1986
3notified06/07/198904/07/1986
3closed06/05/199004/07/1986
3reopen07/08/199207/08/1992
3reopen09/08/199707/08/1992
3closed09/07/200007/08/1992
3 reopen 10/08/2001 10/08/2001
3 closed 15/09/2002 10/08/2001
At present new_date field is empty and i need to update this field from start_date. New date should be equal to open status start date & will remain same for all the lines unless status change to reopen.
At present using cursor but any other suggestion are welcome...
Many Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

forget the cursor, you can and should do this with a single update:

UPDATE SOMETABLE

SET new_date = start_date

WHERE new_date <> start_date

AND status <> 'reopen'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vandana_j79 Mr or Mrs. 500 Points: 536 More actions · Answer 2

Thank you very much for reply...

I can't use simple select statment because i need min start_date where status is open untill status change to reopen.

for example with id 1 i want to update new date with 24/05/1978 untill status change to reopen

so at present my table is

id statusstart_date new_date

1open24/05/1978

1agreed24/06/1978

1pending24/06/1978

1closed25/07/1978

1reopen26/08/1979

1closed25/07/1980

2closed03/07/1986

2pending04/07/1987

3open04/07/1986

3notified06/07/1989

3closed06/05/1990

3reopen07/08/1992

3reopen09/08/1997

3closed09/07/2000

And after update it will look like below

id statusstart_datenew_date

1open24/05/197824/05/1978

1agreed24/06/197824/05/1978

1pending24/06/197824/05/2978

1closed25/07/197824/05/1978

1reopen26/08/197926/08/1979

1closed25/07/198026/08/1979

2closed03/07/198603/07/1986

2pending04/07/198703/07/1986

3open04/07/198604/07/1986

3notified06/07/198904/07/1986

3closed06/05/199004/07/1986

3reopen07/08/199207/08/1992

3reopen09/08/199707/08/1992

3closed09/07/200007/08/1992

Regards

WayneS SSC Guru Points: 95460 More actions · Answer 3

Thanks for supplying some test data and the expected results. It would be a lot easier to help you if you would have supplied it in the format I've used below. See the first link in my signature for how to do this.

-- See how you start off by actually creating a table

-- and then inserting the data into it? Doing this really

-- makes things a lot easier for all the people you want to

-- help you. So, HELP US HELP YOU by doing this for us! See

-- http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

SET DATEFORMAT DMY

DECLARE @TestTable TABLE (id int, status varchar(10), start_date datetime, new_date datetime, PRIMARY KEY CLUSTERED(id, start_date, status))

insert into @TestTable(id, status, start_date)

SELECT 1, 'open', '24/05/1978' UNION ALL

SELECT 1, 'agreed', '24/06/1978' UNION ALL

SELECT 1, 'pending', '24/06/1978' UNION ALL

SELECT 1, 'closed', '25/07/1978' UNION ALL

SELECT 1, 'reopen', '26/08/1979' UNION ALL

SELECT 1, 'closed', '25/07/1980' UNION ALL

SELECT 2, 'closed', '03/07/1986' UNION ALL

SELECT 2, 'pending', '04/07/1987' UNION ALL

SELECT 3, 'open', '04/07/1986' UNION ALL

SELECT 3, 'notified', '06/07/1989' UNION ALL

SELECT 3, 'closed', '06/05/1990' UNION ALL

SELECT 3, 'reopen', '07/08/1992' UNION ALL

SELECT 3, 'reopen', '09/08/1997' UNION ALL

SELECT 3, 'closed', '09/07/2000'

-- whew!!! now that the test data has been made,

-- let's see how to do what you're asking for!

declare @Date datetime, @ID INT

set @ID = 0

-- This form of the UPDATE statement has some rules for proper usage.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

update t

set @Date = CASE WHEN status = 'open' THEN start_date

WHEN status = 'reopen' THEN start_date

WHEN id <> @ID THEN start_date

ELSE @Date

END,

new_date = @Date,

@ID = ID -- anchor column

from @TestTable t -- WITH (TABLOCKX) -- << use the TABLOCKX hint

OPTION (MAXDOP 1) -- << prevent parallelism!update t

select * from @TestTable

In your expected results, do you want it to reset for each reopen? id 3 has two reopens, but it's showing the first. My results are resetting for each - let us know which way you need it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vandana_j79 Mr or Mrs. 500 Points: 536 More actions · Answer 4

Thanks for reply

Actually i want first reopen date. For example even if i have four rows with reopen status i want new date as first reopen status date.

Other difficult bit is if there is no open & reopen status then i need close status date..

Ideally concept behind is

If id has more then one open rows or reopen rows then i need to pick date from first row.

Second thing i need to check status for other rows if status is changing to reopen then date should change.

Third thing is if member doesn't have any open or reopen status then it should use close status date.

I tried to do it with two temp tables which worke 80% correct not fully. Now tried to use cursor which i again not fully correct.