1

I have this table:

ID      ORDER   SCNID   SCANNER
-------------------------------
170842  85986   20      APL-1   
170843  85986   20      APL-1   
170844  85986   20      APL-1   
170845  85986   20      APL-1   
170886  86004   200     GPL-2   
170897  86011   1600    MAP-1   
170900  86011   1600    MAP-1   
170903  86011   1600    MAP-1   
170904  86011   1600    MAP-1   
170906  86011   1600    MAP-1   
170908  86011   1600    MAP-1   
170909  86011   1600    MAP-1   
170918  86024   520     NIX-3   
170922  86028   1050    OPL-3   
170923  86029   1050    OPL-3

I need to make a customorderID column for which it will look like this :

ID      ORDER   SCNID   SCANNER  CUSORDERID
--------------------------------------------
170842  85986   20      APL-1    85986-1
170843  85986   20      APL-1    85986-2
170844  85986   20      APL-1    85986-3
170845  85986   20      APL-1    85986-4
170886  86004   200     GPL-2    86004-1
170897  86011   1600    MAP-1    86011-1
170900  85986   1600    MAP-1    85986-5
170903  86011   1600    MAP-1    86011-2
170904  86011   1600    MAP-1    86011-3
170906  86011   1600    MAP-1    86011-4
170908  86011   1600    MAP-1    86011-5
170909  86011   1600    MAP-1    86011-6
170918  86024   520     NIX-3    86024-1
170922  86028   1050    OPL-3    86028-1
170923  86029   1050    OPL-3    86029-1

The rows are sorted by ID & according to the ID the 1st record's for a particualar ORDER (eg. 86011) custom ORDERID will be 86011-1, if any other records are there for that ORDER then it will be 86011-2 and so on.

Can anyone help me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sourav Mukherjee
  • 61
  • 2
  • 12
  • 1
    You already have an order field, any reason you can't use a sequence when querying to come up with the addendum on the fly instead of storing it? This is usually preferred if you have some sort of date/time to sort it on. Ref here: http://stackoverflow.com/questions/14359749/how-to-return-a-incremental-group-number-per-group-in-sql – jleach Mar 21 '16 at 10:16
  • I need the CUSTOMODER CULUMN which will looks like this.as stated. – Sourav Mukherjee Mar 21 '16 at 10:18
  • What should happen if you have `86011-1`, `86011-2` and `86011-3` and someone deletes the row `86011-2`? Should `86011-3` renumber? If not, should the next row become `86011-2` or `86011-4`? – Joachim Isaksson Mar 21 '16 at 10:22
  • @Joachim Isaksson yes ... rows will be deleted when the order is completed. so if 86011-2 gets deleted then 86011-3 will become 86011-2. thats why i said it is custom ID not Actual ID. I just need to show the CUSTOM ID in front end. – Sourav Mukherjee Mar 21 '16 at 10:24
  • How often to rows actually get deleted, as opposed to having an Archived/Deleted bit or status field? Otherwise this requires some "lookup logic" or heavy subquerying on inserts (more complicated for batch inserts) and usually isn't performant. If it must be stored, store it in a ORDERSEQUENCE field and concatenate the two for display... it'll make it easier to perform the "Get max and increment" logic. – jleach Mar 21 '16 at 10:31
  • And by the way, having to re-fill XXXX-2 if it gets deleted is a major pita... now you have to do gap/missing number analysis along with max/increment analysis. Who's in charge of this numbering scheme? Is it something you have pushback on? – jleach Mar 21 '16 at 10:41
  • @jdl134679 what i was looking for. check esiprogrammer answer :) – Sourav Mukherjee Mar 21 '16 at 11:04

1 Answers1

1

Try this:

with tempOrder as
(
   select ID,[ORDER],SCNID,Scanner, ROW_NUMBER() over (partition by [ORDER] order by [ORDER], ID) as OrderNum
   from OrderTable   
)
Select Id,[Order],SCNID,Scanner, Str([ORDER]) +'-'+ Str(OrderNum) as CUSORDERID from tempOrder
order by [ORDER]
esiprogrammer
  • 1,438
  • 1
  • 17
  • 22
  • Thanks ! I had just modified the select statement from CTE For the exact output.. Select [Order],CONVERT(VARCHAR,([ORDER])) +'-'+ CONVERT(VARCHAR,(CUSTOrderNum)) as CUSORDERID from tempOrder order by [ORDER] – Sourav Mukherjee Mar 21 '16 at 11:01
  • @SouravMukherjee This won't handle deleted items and gap filling per your requirements in the question comments. – jleach Mar 21 '16 at 11:06