I have a set of data containing invoice information. The goal is to find the credit and rebill invoice number ('InvcNbrKey') associated with a particular 'Standard Order' line. Think of it as a person orders an item which generate a line with 'OrdTypeName' = 'Standard Order', but later it was credited ('Rebill CM Req')and rebilled ('Rebill DM Req').

I attempted solving this problem by using Row_Number over Partition to isolate all transactions that share the same NDC, CustPO# and Location to put these transactions into the order which is described above: "Standard Order" followed by 'Rebill CM Req' then 'Rebill DM Req' so that I can eventually use Lead Function to return the correct Invoice Number for the credit

Select ROW_NUMBER() over (Partition by NDC,[CustPO#],[Location] Order by NDC,CustPO#,OrdNbrKey) as RowNumber
,*
From Invoices

Then this two lead functions to find the invoice and the date associated:

Case when (Lead([NetSales$],1) over (Partition by  NDC,[CustPO#],[Location] Order by NDC asc, CustPO#) = [NetSales$]*(-1))                
        
        Then concat('Credited with Invoice #',Lead([InvcNbrKey],1)  over (Partition by  NDC,[CustPO#],[Location] Order by [InvcDateKey]),' on '
            ,Lead([InvcDateKey],1)  over (Partition by  NDC,[CustPO#],[Location] Order by [InvcDateKey]) )  
        
        Else '' END as [HasThisBeenCredited]
    ,Case when (Lead([OrdTypeName],2) over (Partition by  NDC,[CustPO#],[Location] Order by NDC asc, CustPO#) = 'Rebill DM Req')

        Then concat('Rebilled with Invoice #',Lead([InvcNbrKey],2)  over (Partition by  NDC,[CustPO#],[Location] Order by [InvcDateKey]),' on '
            ,Lead([InvcDateKey],2)  over (Partition by  NDC,[CustPO#],[Location] Order by [InvcDateKey]) )  
        
        Else '' END as [HasThisBeenRebilled]

'''

It looks like this

But my desired return is this:

If my desired return is achieved, then I use lead function to return this information:

The problem is I cannot produce the desired return. I had great success using this approach if there are only three lines after the isolation using Partition by. However, when there are 6 lines. The problem is with putting the rows in the particular sequence described. Maybe this problem can be solved using an entire different approach rather than using row_number over partition by and lead function.

Any suggestion is appreciated.

Here is the sql for creating this table

INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('1/25/2023','4608548197','Standard Order','MH01252023','50419039501','3121043683','7881.5','7881.5');
INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('1/26/2023','4608548218','Standard Order','MH01252023','50419039501','3121044236','7881.5','7881.5');
INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('4/13/2023','5148834452','Rebill CM Req','MH01252023','50419039501','352040045','7881.5','-7881.5');
INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('4/13/2023','5148834454','Rebill DM Req','MH01252023','50419039501','352040046','8428.26','8428.26');
INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('4/13/2023','5148837317','Rebill CM Req','MH01252023','50419039501','352043059','7881.5','-7881.5');
INSERT INTO Invoices (InvoiceDate,OrdNbrKey,OrdTypeName,CustPO#,NDC,InvcNbrKey,InvoicePrice,HistoricWAC) VALUES ('4/13/2023','5148837318','Rebill DM Req','MH01252023','50419039501','352043060','12866.4','12866.4');