0

I have a CustomerSales table that looks like below:

[RowID], [Cust], [Time], [Article], [Qty], [Amount], [Receipt]
1, C1, 10:10:00, A1, 1, 100, 1
2, C1, 10:10:00, A2, 2, 101, 1
3, C1, 10:10:00, A3, 3, 102, 1 
4, C1, 10:10:00, A4, 4, 103, 1
5, C2, 10:10:11, A3, 5, 104, 1
6, C3, 10:20:01, A2, 6, 105, 1
7, C4, 10:20:01, A2, 7, 106, 1
8, C4, 10:20:01, A1, 8, 107, 1
9, C1, 11:11:01, A2, 3, 100, 1
10, C1, 11:11:01, A3, 2, 101, 1

CustomerSales table

My data does not have receipt number. Hence the requirement is to generate a linear number for receipt number. e.g. start from 1 and increments by 1. To determine which line items belong to a given receipt number the records can be grouped by [Cust] and [Time]. All lines belonging to the group of [Cust] and [Time] will be updated with the MAX(Receipt]+1 (next available receipt number).

Expected results as below:

[RowID], [Cust], [Time], [Article], [Qty], [Amount], [Receipt]
1, C1, 10:10:00, A1, 1, 100, 1, 1
2, C1, 10:10:00, A2, 2, 101, 1, 1
3, C1, 10:10:00, A3, 3, 102, 1, 1 
4, C1, 10:10:00, A4, 4, 103, 1, 1
5, C2, 10:10:11, A3, 5, 104, 1, 2
6, C3, 10:20:01, A2, 6, 105, 1, 3
7, C4, 10:20:01, A2, 7, 106, 1, 4
8, C4, 10:20:01, A1, 8, 107, 1, 4
9, C1, 11:11:01, A2, 3, 100, 1, 5
10, C1, 11:11:01, A3, 2, 101, 1, 5

Expected Results

Dale K
  • 25,246
  • 15
  • 42
  • 71
anandav
  • 3
  • 1
  • Please update your question with your best attempt at resolving this yourself. Also, you don't need to "loop". Just `UPDATE`... – devlin carnate Feb 12 '20 at 23:50
  • Does this answer your question? [Update Statement using Join and Group By](https://stackoverflow.com/questions/12225715/update-statement-using-join-and-group-by) – devlin carnate Feb 12 '20 at 23:53

1 Answers1

3

You can use dense_rank():

select t.*,
       dense_rank() over (order by time, cust) as receipt
from t;

If you want to update the data, use an updatable CTE:

with toupdate as (
      select t.*,
             dense_rank() over (order by time, cust) as new_receipt
      from t
     )
update toupdate
    set receipt = new_receipt;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786