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
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]