CODEWITHSUNDEEP
c#sql-serverlinq
Randy
Randy

Reputation: 1287

How do I grab only the latest Invoice Number

I have Invoice Numbers that are stored as nvarchar(25).

Their Format is ‘####AA’ Where #### is the Invoice Number and AA is the Version Number (partial Order Shipping) I cannot change the format.

I created two Scalar Functions:

CREATE FUNCTION [dbo].[fnNumbersFromStr](@str varchar(8000))
RETURNS int
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@str)> 0
  SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1), '')
  RETURN CAST(@str AS INT)
END

And it’s brother:

CREATE FUNCTION [dbo].[fnStringFromNum](@str varchar(25))
RETURNS varchar(25)
AS
BEGIN
  WHILE PATINDEX('%[^a-z]%',@str)> 0
  SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^a-z]%', @str), 1), '')
  RETURN @str
END

I am stalled with this script:

SELECT
strInvoiceNo,
    dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
    dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
FROM @TempTable

Which when runs returns:

strInvoiceNo    InvoiceNumber    InvoiceString

1000A           1000             A

1000B           1000             B

1000C           1000             C

1001A           1001             A

1001B           1001             B

1002AA          1002             AA

1002AB          1002             AB

1003A           1003             A

1004A           1004             A

I just can’t figure out the next step from here. I am stuck.

I would like the select to only return the latest Invoice Versions:

1000C
1001B
1002AB
1003A
1004A

Sql, Lamda or Linq will work fine for me.

Thanks in advance,

Upvotes: 1

Views: 367

Answers (4)

Robert McKee
Robert McKee

Reputation: 21477

Here is it in LINQ (Assuming fnStringFromNum returns a string padded on the left with spaces):

dbContext.YOURTABLE
  .GroupBy(x=>UDFFunctions.fnNumbersFromStr(x.AccountNumber))
  .Select(x=>x.OrderByDescending(y=>UDFFunctions.fnStringFromNum(y.AccountNumber).FirstOrDefault())

SQL (using current fnStringFromNum):

SELECT
  InvoiceNumber + LTRIM(MAX(RIGHT(SPACE(20)+InvoiceString,20))) As strInvoiceNo    
FROM
(
   SELECT
       dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
       dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
   FROM @TempTable
) As tbl
GROUP BY InvoiceNumber

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

I dont think you need any UDF for this, a simple windowing function query should return what you looking for. 

WITH x AS
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)
SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM X 
WHERE rn = 1

OR 

SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM 
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)x
WHERE rn = 1

Upvotes: 1

nycdan
nycdan

Reputation: 2849

Not necessarily the most efficient, but this will work:

select strInvoiceNo
from @TempTable T
where InvoiceString = (select max(invoicestring) from temp1 where invoicenumber = T.invoicenumber)
order by 1

Edit: Sorry....disregard. This will work off of your full result table but may not be what you actually need. Apologies.

Upvotes: 0

Magnus
Magnus

Reputation: 46947

Try this:

SELECT
  InvoiceNumber + MAX(InvoiceString) As strInvoiceNo    
FROM
(
   SELECT
       dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
       dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
   FROM @TempTable
) As tbl
GROUP BY InvoiceNumber

Upvotes: 1

Related Questions