Cursor Help!!!!!! - 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

Transact-SQL (2000)

Cursor Help!!!!!!

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2007-03-08 : 11:59:39

Hello Experts!

I have a tricky transact problem that I cannot get to work properly. I would really appreciate any help if someone has encountered this before. Basically...

I have a table:

ReferralID Date Attendance ReceivedDate AttendanceID
---------- ---- --------- -------- ------------
12345 01/01/01 No 01/11/00 1
12345 01/02/01 Yes 01/11/00 2
12345 01/03/01 Yes 01/11/00 3
12345 01/04/01 Yes 01/11/00 4

There can be more than one attendance in each referral and they have different days.

What I need to do is select the 1st attendance in the referral. This would be easy by just doing a select distinct.

However, if the first attendance is "No" I need to change the received date of all later attendances to the date of the first attendance!!!!!!

I have tried it by creating extra tables etc but the problem is that the first two attendances may show "No" so it means that all future attendances have the date of the second attendance and so on!!!!

This is doing me in big time!!!!!!!

If anyone has any ideas I would really appreciate it.

Cheers

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 12:48:15

Just to be clear on what you're actually trying to do - you want to write an update statement that will update all referrals to have the received date of the most recent referral that has No in the attendance column?

BTW - if you want the received date to always be the same for all referrals then your data is not normalized and should really be redesigned rather than figuring out this query. But you can get help here to do it either way.

billsack
Starting Member

35 Posts

Posted - 2007-03-09 : 04:27:18

Hey there,

"Just to be clear on what you're actually trying to do - you want to write an update statement that will update all referrals to have the received date of the most recent referral that has No in the attendance column?"

Kind of!!!!!

Each Referral has a number of attendances. When the first attendance in the referral is "No" then I need to change the received date of subsequent attendances in that Referral to the Date of the Referral that was not attended.

Hope you can help.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 10:40:36

Well that didn't clear too much up, so this may or may not be what you want

update Referrals
set ReceivedDate = r3.ReceivedDate
from Referrals 
inner join (select r1.ReferralID, r1.ReceivedDate
from Referrals r1
where r1.Attendance = 'No' 
	and r1.[Date] = (select max(r2.[Date]) 
		from Referrals r2
		where r2.Attendance = 'No' and r2.ReferralID = r1.ReferralID)) r3 
on Referrals.ReferralID = r3.ReferralID
where Referrals.Attendance = 'Yes'

If there are multiple No rows this will take the date from the last one and not update the other No rows, only the Yes rows. If you want to update the earlier No rows to be the same as the most recent one, then just remove the where clause from the end
where Referrals.Attendance = 'Yes'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources