jeff jeff - 9 days ago 5
SQL Question

Add a series column that will increment if reaches the maximum number

I would like to add a column that will count according to the max number of series.

I have generated the SERIES_NO column using below:

MOD(ROW_NUMBER() OVER (ORDER BY item_code, loc_code, cargo_sts) - 1, 3) + 1


In this case the Max series no is
3
. Below is the sample result set. Now, I want to generate the
SHEET_NO
column. Any suggestion? Thanks.

CARGO_STS LOC_CODE ITEM_CODE AVAIL_QTY SERIES_NO SHEET_NO
NORMAL D1867BD1 0000044500 6 1 1
NORMAL D1947GD1 0000055401 2 2 1
NORMAL D3351AA1 0000058000 2 3 1
NORMAL D1945DC2 0000058201 1 1 2
STO-DAMAGE 205-12BB 0000058300 1 2 2
NORMAL D3446FB1 0000058300 1 3 2
NORMAL Q00-37CA 0000060401 128 1 3
NORMAL D1158FA1 0000079901 36 2 3

Answer

something like following will do:

ceil (ROW_NUMBER() OVER (ORDER BY item_code, loc_code, cargo_sts) /3 )
Comments