February 27, 2014 at 7:15 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:27 am
Did you try this?
ROW_NUMBER() OVER(PARTITION BY r.clientid, GenActivityCode ORDER BY appointmentdate asC) AS FollowUp_No
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2014 at 7:59 am
Hi
No - that doesn't work either. I end up with the sequence:
FollowUp_No
1
1
2
2
3
3
4
1
5
Basically, I want the sequence to recommence at 1 at each ocuurrence of the value 'NS' under GenActivityCode, then increment from there until the value changes back to 'NS' again and again starts to increment from 1.
I thought it would be easy, but I'm getting a headache. Maybe I'm having a bad day.
Cheers.
February 27, 2014 at 9:18 am
Have you tried the DENSE_RANK() function? It's called exactly the same way as ROW_NUMBER() and should sort your problem.
March 11, 2014 at 7:19 am
Hi
Sorry for the late reply, I've been pulled away to work on another project.
I've tried Dense_Rank, but that doesn't work either.
I did however find this post where the guy is having similar problems to me and there is no current solution mentioned here either apart from Dense_Rank, which as I've mentioned I’ve tried and doesn’t work:
http://stackoverflow.com/questions/13405264/t-sql-reset-row-number-on-field-change">
http://stackoverflow.com/questions/13405264/t-sql-reset-row-number-on-field-change
What I think I'll do is wait for SS 2012, which I'm hoping to get May. Then I can use the LAG function to re-start numbering at 1 for each occurrence of NS under GenActivityCode.
Thanks for your help.
Cheers Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply