Row_number over partiton

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  November 23, 2018 at 8:57 am

  #378708

  Hi, I am using the below script to calculate a set of sequential ints on records as they are committed to a table.
  The requirements are that the partition is by company and trip and that trips will be partially committed and can have their booking lines in any order.

  When a record already exists, I am using that number
  When a record doesn't exist, I am adding the result of the row number to the max(int) for that same trip/company that may  already exist in the table. I have wrapped an isnull around the last part as some records will be brand new and not have any previous entries.

  My issue is that the calculation works for some records and not for others.
  Here is the code:
  

  [Booking_New] =

  ISNULL( (select distinct(booking_new)

  from dbo.MAX_Booking mb

  where mb.record_ID collate database_default = cli.RECORD_ID),

  row_number() over (partition by cli.Company, cli.Trip order by cli.Booking asc) +

  isnull((select MAX(mb.Booking_new)

  from dbo.MAX_Booking mb

  where rtrim(mb.Company) collate database_default = rtrim(cli.company) and

  rtrim(mb.Trip) collate database_default = rtrim(cli.trip) and

  rtrim( mb.branch_code) collate database_default = rtrim(cli.Branch_Key)),0)

  Here is the output for a brand new record:
  

  I have added row_number and max_b_n to prove that each part of the sum is correct
  what I should see in this case  in booking_new  is
  1
  2
  3

  what I actually see is
  1
  2
  2

  Can anyone assist in what is causing this miscalculation?

  Cheers

  Dave
  

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 23, 2018 at 9:00 am

  #2014401

  Can you post some sampel data which we can consume please (with DDL and DML statements) please? I doubt you need to nested sub queries here, so if we can have some data we can work with, along with expected results, we can more easily help you. (Also, what's wrong with the value of Row_Number in your picture, which appears to have the results you're after.)

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  November 23, 2018 at 9:32 am

  #2014402

  Hi Thom,
  Thanks for the reply.
  Max(booking_new) is  the row number calculated for previously entered records in the same trip/company.
  I am not sure what you mean by DDL and DLM statements sorry.
  I have managed to solve it incidentally.

  It turns out that despite my best efforts,
  where mb.record_ID collate database_default = cli.RECORD_ID), didn't work as record_id was not unique. I have a unique field to use (a computed identity field) and that has resolved my issues.

  Best Regards

  Dave

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 23, 2018 at 10:00 am

  #2014405

  david_h_edmonds - Friday, November 23, 2018 9:32 AM

  Hi Thom,
  Thanks for the reply.
  Max(booking_new) is  the row number calculated for previously entered records in the same trip/company.
  I am not sure what you mean by DDL and DLM statements sorry.
  I have managed to solve it incidentally.

  It turns out that despite my best efforts,
  where mb.record_ID collate database_default = cli.RECORD_ID), didn't work as record_id was not unique. I have a unique field to use (a computed identity field) and that has resolved my issues.

  Best Regards

  Dave

  Data Definition Language and Data Manipulation Language; CREATE and INSERT statements.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  November 23, 2018 at 10:48 am

  #2014407

  Perfect - thanks for clarifying.

  Dave