I'm trying to get a row number/rank for my data based on the change in Item and Bin with a reset every time the Item Number changes.
Here is my Script:
SELECT *
, ROW_NUMBER() over (partition by ITEMNMBR, DestBin, OrigQty Order by ITEMNMBR) as [Row Number]
--, DENSE_RANK() OVER (ORDER BY ITEMNMBR, LOCNCODE, DestBin) AS [Row Number]
FROM _DSI_NegativeInvetoryBins
Below is what the results are:
ITEMNMBR
LOCNCODE
DestBin
Row Number
AA AS18ELF05SR
WHSE5000
RCV1
1
AA AS18HP19RG
WHSE5000
CAB Y1
1
AA AS18HP19RG
WHSE5000
RCV1
1
AA AS18HP19RS
WHSE5000
CAB Y1
1
AA AS18HP19RS
WHSE5000
RCV1
1
BM 86027
WHSE5000
RCV1
1
BM 92233 OA
WHSE5000
RCV1
1
BM BE93738
WHSE5000
1
1
BO E0KK-03 COBALT
WHSE5000
1B08F1
1
BO E0KK-03 COBALT
WHSE5000
CAB E9
1
What I really want is:
ITEMNMBR
LOCNCODE
DestBin
Row Number
AA AS18ELF05SR
WHSE5000
RCV1
1
AA AS18HP19RG
WHSE5000
CAB Y1
1
AA AS18HP19RG
WHSE5000
RCV1
2
AA AS18HP19RS
WHSE5000
CAB Y1
1
AA AS18HP19RS
WHSE5000
RCV1
2
I apologize for the formatting not sure how to copy/paste the results with formatting. Hopefully this is clear as to what I'm looking to accomplish.
not able to understand .. what you are looking for
Drop Create Data
use tempdb
go
drop table data
go
create table data
(
ITEMNMBR varchar(100) ,
LOCNCODE varchar(100) ,
DestBin varchar(100) ,
RowNumber int
)
go
insert into data select 'AA AS18ELF05SR','WHSE5000','RCV1', 1
insert into data select 'AA AS18HP19RG','WHSE5000','CAB Y1', 1
insert into data select 'AA AS18HP19RG','WHSE5000','RCV1', 1
insert into data select 'AA AS18HP19RS','WHSE5000','CAB Y1', 1
insert into data select 'AA AS18HP19RS','WHSE5000','RCV1', 1
insert into data select 'BM 86027','WHSE5000','RCV1', 1
insert into data select 'BM 92233 OA','WHSE5000','RCV1', 1
insert into data select 'BM BE93738','WHSE5000', 1,1
insert into data select 'BO E0KK-03 COBALT','WHSE5000','1B08F1',1
insert into data select 'BO E0KK-03 COBALT','WHSE5000','CAB E9',1
go
select * from data
go
SQL I tried
SELECT itemnmbr,
locncode,
Count(*)
FROM data
GROUP BY itemnmbr,
locncode
go