Russ Suter
asked on
Need a line number based on invoice number
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
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...
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.
If its not equal then increment the invoice count.
perhaps a sql function that takes in the invoice number and outputs the invoice count
RANK or DENSE_RANK
Back in a minute....
Back in a minute....
although maybe not....
discussion on partition query on so
https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful
discussion on partition query on so
https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, that's it. I'd forgotten about DENSE_RANK(). Thanks very much!
My pleasure. Happy to have been able to help, even if just a memory jogger :)
ASKER
The 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.