Need a line number based on invoice number

Russ Suter
Russ Suter used Ask the Experts™
on
I seem to be a little rusty on some of my SQL. I can easily get a row number grouped by an invoice number as follows:
SELECT
    ROW_NUMBER() OVER (PARTITION BY INVOICENUMBER ORDER BY LINEITEMNUMBER) AS INVOICELINENUMBER,
    *
FROM
    INVOICES
WHERE
    DATECLOSED IS NULL

Open in new window

This handily gives me a unique line number starting with 1 for each invoice line item. What I can't seem to remember is how do I get another number that increments for each invoice number. So, say the first 2 rows in my data set have the same invoice number, they would both need to be assigned number 1. The next 3 rows are a different invoice number. They need to be number 2. Etc...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer

Author

Commented:
Perhaps to try and clarify my question. Here's a data example...
Sample DataThe first column was easy to compute using the ROW_NUMBER() function as I stated above. What I don't know is how to get the right values into the last column labeled INVOICECOUNT and highlighted in yellow in the above image.
I would think you will need to store the last invoice number and compare it with the current inv number.
If its not equal then increment the invoice count.
perhaps a sql function that takes in the invoice number and outputs the invoice count
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
RANK or DENSE_RANK

Back in a minute....
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Sorry about that... Use DENSE_RANK() function
 SELECT
    ROW_NUMBER() OVER (PARTITION BY INVOICENUMBER ORDER BY LINEITEMNUMBER) AS INVOICELINENUMBER,
    dense_rank() OVER (order BY INVOICENUMBER) AS INVOICECOUNT,

    *
FROM
    INVOICES
WHERE
    DATECLOSED IS NULL

Open in new window

See : https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017
Russ SuterSenior Software Developer

Author

Commented:
Yep, that's it. I'd forgotten about DENSE_RANK(). Thanks very much!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
My pleasure. Happy to have been able to help, even if just a memory jogger :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial