0

I'm trying to write a SELECT query that calculates a BatchNumber column that increments by 1 every 150 rows, starting at 1001.

The expected BatchNumber should be 1001 for the first 150 rows, then 1002 for rows 151-300, and so on...

I've tried using a division on a dynamic ROW_NUMBER(), but can't get it.

SELECT ID
    ,ROW_NUMBER() OVER (ORDER BY (SELECT ID)) AS RowNumber
    ,CASE
        WHEN CONVERT(INT, (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) % 150))=0
        THEN 0 ELSE 1001 END
    + CONVERT(INT,(ROW_NUMBER() OVER (ORDER BY (SELECT ID)) / 150))
    AS BatchNumber  
FROM Table1 WITH (NOLOCK);
  • 1
    Your question is really unclear. Can you provide sample data and expected results? – Erik Darling Nov 27 '17 at 17:02
4

The simplest way I can get it is:

SELECT ID
    ,1001
    + (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) - 1) / 150
    AS BatchNumber
FROM Table1;
  • If you are happy with an answer, click the tickmark just under the Downvote button to mark the answer as Accepted :-) – Oreo Nov 27 '17 at 18:13

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.