I'm wondering if anybody can help me out with any or all of this code below. I've made it work, but it seems inefficient to me and is probably quite a bit slower than optimal. 

Some basic background on the necessity of this code in the first place:

I have a table of shipping records that does not include the corresponding invoice number. I've looked all through the tables and I continue to do so. In fact, only this morning I discovered that if a packing slip has been generated that I can link the shipping table to the packing slip table via that packing slip ID and grab the invoice number from there. Absent that link, however, I'm forced to guess. In most instances, that's not terribly difficult, because the invoice table has number, line and release that can match up. But when there are multiple shipments for number, line and release (for instance, when a line is partially shipped) then there can be multiple answers, only one of which is correct. I am partially helped by the presence of a a column in the shipping table that states what the date sequence is for that number, line and release, but there are still circumstances where the process I use for "guessing" can be somewhat ambiguous.

What my procedure does is this. First, it creates a table of data that includes the invoice number if there was a pack slip to link it through.

Next, it dumps all of that data into a second table, this time using--only if the invoice was NULL in the first table--a "guess" about the invoice number based on partitioning all the shipping records by number, line, release, date sequence and date, and then comparing that to the same type of thing for the invoice table, and trying to line everything up by date.

Finally, it parses through that table and finds any last nulls and essentially matches them up with the first record of any invoice for that number, line and release.

Both guesses have added characters to show that they are, in fact, guesses.

```
IF OBJECT_ID('tempdb..#cosTAble') IS NOT NULL
DROP TABLE #cosTable

DECLARE @cosTable2  TABLE (
     ID             INT IDENTITY
    ,co_num         CoNumType
    ,co_line        CoLineType
    ,co_release     CoReleaseType
    ,date_seq       DateSeqType
    ,ship_date      DateType
    ,inv_num        NVARCHAR(14)
    )

DECLARE
     @co_num_ck     CoNumType
    ,@co_line_ck    CoLineType
    ,@co_release_ck CoReleaseType

DECLARE @Counter1   INT = 0

SELECT cos.co_num, cos.co_line, cos.co_release, cos.date_seq, cos.ship_date, cos.qty_invoiced, pck.inv_num
    INTO #cosTable
    FROM co_ship cos
        LEFT JOIN pckitem pck
            ON cos.pack_num = pck.pack_num
            AND cos.co_num = pck.co_num
            AND cos.co_line = pck.co_line
            AND cos.co_release = pck.co_release

;WITH cos_Order
    AS(
        SELECT co_num, co_line, co_release, qty_invoiced, date_seq, ship_date, ROW_NUMBER () OVER (PARTITION BY co_num, co_line, co_release ORDER BY ship_date) AS cosrow
        FROM co_ship
        WHERE qty_invoiced > 0
    ),

invi_Order
    AS(
        SELECT inv_num, co_num, co_line, co_release, ROW_NUMBER () OVER (PARTITION BY co_num, co_line, co_release ORDER BY RecordDate) AS invirow
        FROM inv_item
        WHERE qty_invoiced > 0
    ),

cos_invi
    AS(
        SELECT cosO.*, inviO.inv_num
        FROM cos_Order cosO
            LEFT JOIN invi_Order inviO
                ON cosO.co_num = inviO.co_num AND cosO.co_line = inviO.co_line AND cosO.cosrow = inviO.invirow)

INSERT INTO @cosTable2
SELECT cosT.co_num, cosT.co_line, cosT.co_release, cosT.date_seq, cosT.ship_date, COALESCE(cosT.inv_num,'*'+cosi.inv_num) AS inv_num
    FROM #cosTable cosT
    LEFT JOIN cos_invi cosi
        ON  cosT.co_num = cosi.co_num
        AND cosT.co_line = cosi.co_line
        AND cosT.co_release = cosi.co_release
        AND cosT.date_seq = cosi.date_seq
        AND cosT.ship_date = cosi.ship_date

WHILE @Counter1 < (SELECT MAX(ID) FROM @cosTable2) BEGIN
    SET @Counter1 += 1
    SET @co_num_ck = (SELECT co_num FROM @cosTable2 WHERE ID = @Counter1)
    SET @co_line_ck = (SELECT co_line FROM @cosTable2 WHERE ID = @Counter1)
    SET @co_release_ck = (SELECT co_release FROM @cosTable2 WHERE ID = @Counter1)
    IF EXISTS (SELECT * FROM @cosTable2 WHERE ID = @Counter1 AND inv_num IS NULL)
        UPDATE @cosTable2
            SET inv_num = '^' + (SELECT TOP 1 inv_num FROM @cosTable2 WHERE 
                            @co_num_ck = co_num AND
                            @co_line_ck = co_line AND
                            @co_release_ck = co_release)
            WHERE ID = @Counter1 AND inv_num IS NULL
    END

SELECT * FROM @cosTable2
ORDER BY co_num, co_line, co_release, date_seq, ship_date

```