Challenging SQL problem 1

[JCAD1]

I have a table (op) with the following fields:
id, attdate, refdate and attend. If I want to see many occurences of id records, I can use the following:

 select id, count(*)
from op
group by id
having count(*)>1

Now if I want to see the details of just one record, I can use id and order by attdate, e.g,

select *
from ac_op_refs
where id = 'RWEAAR42625730/12/2003'
order by attdate

which gives the following results:

[b]id                     attdate    refdate    attend [/b]
RWEAAR42625730/12/2003 2004-04-01 2003-12-30 2	
RWEAAR42625730/12/2003 2004-05-27 2003-12-30 2 RWEAAR42625730/12/2003 2004-06-17 2003-12-30 3
RWEAAR42625730/12/2003 2004-07-10 2003-12-30 5

Now here is the challenge. I want to replace all second refdate (which in this case is 2003-12-30) with the first attdate (in this case 2004-04-01), the third refdate with the second attdate, etc. This process carries on until it reaches a point where attend is 5 or 6, then it stops. I don't want id to be unique because I will lose some important records. All I am interested in is replace all refdate starting from the 2nd one as explained, where every count of id > 1 and attend is not 5 or 6.

How do I write the code to achieve this? Could someone help me, please.

Thank you in advance for your help.

jcad1

 

[tlbroadbent]

I'm not sure that I fully understand your question. Does the following code do what you need?

Select
 a.[id], a.attdate,
 refdate=case When a.attend < 5 Then coalesce(b.attdate,a.refdate) Else a.refdate end,
 a.attend
from ac_op_refs a
Left Join ac_op_refs b
	On a.id = b.id
 And b.attdate = (select max(attdate) from ac_op_refs where attdate<a.attdate)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[donutman]

Terry, that is very impressive and I believe it will work unless AttDate is not unique for each RefDate. Since he said he wants to replace data, it would probably be best to allow that select to populate a new table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[tlbroadbent]

Karl,

You are correct regarding the need for a unique value. In addition, my code depends on the values to be ascending. An identity column would be very helpful in situation like this.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[donutman]

I was thinking that one of the neat things about the code was that it didn't require a particular order. In what way does it require "values to be ascending." It's true that the result set will not be listed in a particular order, but I think that each row will be valid nonetheless.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JCAD1]

tlbroadbent

I tried to apply the code you have provided to the table and it ran for over 1 hr. I stopped it and created a new table with a few records to test the solution. It worked brillantly, 'doing more than what it says on the tin!' My problem is sorted! However, the orginal table has over 385,000 records. I am wondering if there is any way of making the code more efficient so that I can apply to the table?

Thank you for your help and time. Highly appreciated.

Jcad1

 

[tlbroadbent]

I found an error in the code.

Select
 a.[id], a.attdate,
 refdate=case When a.attend < 5 Then coalesce(b.attdate,a.refdate) Else a.refdate end,
 a.attend
from ac_op_refs a
Left Join ac_op_refs b
    On a.id = b.id
 And b.attdate = (select max(attdate) from ac_op_refs where [COLOR=green][b]id = b.id[/b][/color] attdate<a.attdate)

Make sure the table has an index on the ID column. Creating an index on the ID and attdate columns may also help.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[donutman]

First I would try it again after indexing attDate. I assume that id is a PK. Otherwise, I think tlbroadbent's suggestion about an identity column would be the way to go. It would also make a better PK than id...I assume the table is growing.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[tlbroadbent]

Wow another error! I left out the AND.

where id = b.id [b]AND[/b] attdate<a.attdate)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[JCAD1]

Terry

After amending the code and indexing attdate and id as suggested, I ran the query and this time it took just one minute to complete. Excellent!

Thank you so much for your help. I also want to thank Karl for his contribution.

Jcad1