February 27, 2014 at 5:29 am
Hi
The following code outputs the data below the code (I have only included the relevant fields):
select
r.ClientID,
AppointmentDate,
appointmenttype,
Outcome,
o.CodeDescription as outcomedesc,
GenActivityCode,
ga.CodeDescription as Activity,
r.ReferralNumber,
ROW_NUMBER() OVER(PARTITION BY r.clientid ORDER BY appointmentdate asC) AS FollowUp_No,--numbers the follow ups
case when GenActivityCode = 'NS' then '1' else '2' end as partitioncode --just trying something
--into #JP_Followups
from dbo.vwNSReferrals R
left join dbo.vwNSAppointmentsPD A on r.ClientID = a.ClientID and r.ReferralNumber = a.ReferralNumber
left join SCHEMANS.AmsOutcome O on o.Code = a.outcome
left join SCHEMANS.AmsAppointmentContactActivity ACA on ACA.ClientID = a.ClientID and aca.SequenceID = a.SequenceID
left join SCHEMANS.GenActivity ga on aca.GenActivityCode = ga.code
where ServiceTeam like 'pod%aqp%'
and GenActivityCode in ('ns','red','S57_4')--Nail surgery,Redressing or nail re-dressing
and AppointmentType = 'FUA'
and Outcome is not null
and Outcome not in ('i')
--and AppointmentDate between '01-apr-13' and '30-apr-13'
order by r.ClientID,appointmentdate
ClientIDAppointmentDateGenActivityCodeActivity partitioncodeFollowUp_No
129/04/2013NS Nail Surgery 1 1
101/05/2013S57_4 Nail Re-Dressing 2 2
116/05/2013S57_4 Nail Re-Dressing 2 3
123/05/2013NS Nail Surgery 1 4
114/06/2013S57_4 Nail Re-Dressing 2 5
119/08/2013NS Nail Surgery 1 6
121/08/2013S57_4 Nail Re-Dressing 2 7
104/09/2013RED Redressing 2 8
125/09/2013S57_4 Nail Re-Dressing 2 9
229/04/2013NS Nail Surgery 1 1
201/05/2013S57_4 Nail Re-Dressing 2 2
216/05/2013S57_4 Nail Re-Dressing 2 3
223/05/2013NS Nail Surgery 1 4
214/06/2013S57_4 Nail Re-Dressing 2 5
The important col is the last one which outputs numbers in sequential order based on ROW_NUMBER() OVER(PARTITION BY. However, what I want it to do is reset the sequence to 1 each time the GenActivityCode col value changes to NS. I've replaced the last col with another col called RequiredFollowup_No here to show what I mean:
ClientIDAppointmentDateGenActivityCodeActivitypartitioncodeRequired FollowUp_No
129/04/2013NSNail Surgery1 1
101/05/2013S57_4Nail Re-Dressing2 2
116/05/2013S57_4Nail Re-Dressing2 3
123/05/2013NSNail Surgery1 1
114/06/2013S57_4Nail Re-Dressing2 2
119/08/2013NSNail Surgery1 1
121/08/2013S57_4Nail Re-Dressing2 2
104/09/2013REDRedressing2 3
125/09/2013S57_4Nail Re-Dressing2 4
229/04/2013NSNail Surgery1 1
201/05/2013S57_4Nail Re-Dressing2 2
216/05/2013S57_4Nail Re-Dressing2 3
223/05/2013NSNail Surgery1 1
214/06/2013S57_4Nail Re-Dressing2 2
I have tried every variation of row, rank, partition by etc. that I can think of, but so far have got nowhere. I can’t believe there isn’t a simple solution out there. Any takers?
Cheers
Tim
February 27, 2014 at 7:17 am
Please ignore - I've reposted this in the general forum as I posted it here it error.
Tim
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply