1

I have a table with 200,000 records. One of the columns is a location ID. I need to break down the entire table in batches of 2000 but group them by the LocationID column.

I have about 200 locations with 600 records to 4100 records in each location.

I need to show batch numbers within each location. So if location 001 has 600 records, then there is a batchID of 001-1 which contains 600 records. If location 002 has 2800 records then there are batch IDs 002-1 (2000 records) and 002-2 (800 records). And so on. So a location, 010 with 4100 records would have batchIDs of 010-1,010-2,010-3,010-4,010-5.

I have the following code that works to create batches of 2000, but I can't figure out how to group it by the location #.

SELECT 
 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber, 
(CASE WHEN CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) % 2000)) = 0 
 THEN 0 
 ELSE 1 END) + 
 CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 2000)) AS BatchNumber, 
 * 
FROM [dbo].[LocationRecords]
3
  • 2
    Have you tried PARTITION BY LocationID?
    – mustaccio
    Dec 13 '19 at 15:04
  • @mustaccio How would I apply Partition By?
    – Capt_Ron
    Dec 13 '19 at 15:10
  • By including it in your window specification may be?
    – mustaccio
    Dec 13 '19 at 15:12
0

As mustaccio pointed out you must set a partition in the ROW_NUMBER() function, just to start the counter again for each LocationId.

I've set up the next example:

create table locations (id int identity primary key, location_id varchar(10));

insert into locations values
('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),
('001'),
('002'),('002'),('002'),('002'),('002'),('002'),('002'),('002'),
('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),
('003'),('003'),('003'),('003'),('003'),('003');

I've used batches of 10 instead of 2000 for the sake of the answer:

SELECT
    id,
    location_id,
    CONCAT (location_id, '-',
            CAST(ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY id) / 10 as VARCHAR(10))
           ) as partition
FROM
    locations;

This is the result:

id | location_id | partition
-: | :---------- | :--------
 1 | 001         | 001-0    
 2 | 001         | 001-0    
 3 | 001         | 001-0    
 4 | 001         | 001-0    
 5 | 001         | 001-0    
 6 | 001         | 001-0    
 7 | 001         | 001-0    
 8 | 001         | 001-0    
 9 | 001         | 001-0    
10 | 001         | 001-1    
11 | 001         | 001-1    
12 | 002         | 002-0    
13 | 002         | 002-0    
14 | 002         | 002-0    
15 | 002         | 002-0    
16 | 002         | 002-0    
17 | 002         | 002-0    
18 | 002         | 002-0    
19 | 002         | 002-0    
20 | 003         | 003-0    
21 | 003         | 003-0    
22 | 003         | 003-0    
23 | 003         | 003-0    
24 | 003         | 003-0    
25 | 003         | 003-0    
26 | 003         | 003-0    
27 | 003         | 003-0    
28 | 003         | 003-0    
29 | 003         | 003-1    
30 | 003         | 003-1    
31 | 003         | 003-1    
32 | 003         | 003-1    
33 | 003         | 003-1    
34 | 003         | 003-1    
35 | 003         | 003-1    

db<>fiddle here

1
  • @mustaccio Perfect. I can work with this to apply it to my situation. I really appreciate both your help.
    – Capt_Ron
    Dec 13 '19 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.