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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Perhaps to try and clarify my question. Here's a data example...
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.
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.
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial