Incorrect sequencing of row nos.

  • 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

  • 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/

  • 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.

  • Have you tried the DENSE_RANK() function? It's called exactly the same way as ROW_NUMBER() and should sort your problem.

  • 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