Is there a way to organize by last invoice date by job?

Question

I am fairly new to SQL and am looking for a solution. I am looking to find the best way to get the newest date from transaction date while being grouped by Job reference. Since these are different tables I am struggling someone please help.

SELECT
    C.CustomerCode,
    C.name AS Customer,
    B1.Name AS 'Customer Home Branch',
    B.Name AS 'Trx Branch',
    At.DocumentNumber AS 'Document        
    Number',
    At.TransactionDate AS 'Document    
    Date',
    At.PaymentDueDate AS 'Due Date',
    CASE 
        WHEN At.TransactionType = 1 THEN 'Invoice' 
        WHEN at.transactiontype = 2 THEN 'CreditNote' 
        WHEN at.transactiontype = 3 THEN 'FC' 
        WHEN AT.transactiontype = 4 THEN 'Payment' 
        WHEN At.TransactionType = 8 THEN 'Adj' 
        ELSE '' 
    END AS Type, 
    CA.AddressCode,
    J.JobReference,
    At.OriginalAmount AS 'Original     
    Amount',
    At.AmountOutstanding AS 'Outstanding Amount',
    DATEDIFF(DAY, AT.PaymentDueDate, GETDATE()) AS 'Days Past Due',
    DATEDIFF(DAY, At.TransactionDate, GETDATE()) AS 'Days From Shipment',
    C.customerID,
    AT.AccountsTransactionID
FROM
    AccountsTransaction AS AT WITH (NOLOCK)
LEFT JOIN
    Customer AS C WITH(NOLOCK) ON C.customerid = AT.CustomerID
LEFT JOIN
    Branch AS B WITH(NOLOCK) ON B.branchid = AT.BranchID
LEFT JOIN
    Branch AS B1 WITH(NOLOCK) ON B1.branchid = C.HomeBranchID
LEFT JOIN
    CustomerAddress AS CA WITH(NOLOCK) ON CA.customerAddressID = AT.DeliveryAddressID
LEFT JOIN
    Job AS J WITH(NOLOCK) ON j.jobID = CA.JobID

Please help!! I am having a very hard time using a max and group by with so many tables being joined above. I would like to group by job reference and show the newest transaction date in this manner.

INNER JOIN
    (SELECT
         AT.DeliveryAddressID,
         MAX('TransactionDate') AS ND
     FROM
         AccountsTransaction AS AT
     GROUP BY
         AT.DeliveryAddressID) AS New ON AT.DeliveryAddressID = J.JobID

if you want to group, as stated in your question, try the `GROUP BY` clause with selecting a `max(transactionDate)` — Psi, Sep 05 '22 at 00:59
I tried this but it does not give me the newest transaction date by job refrence — Nick1893, Sep 05 '22 at 01:48
See [Bad Habits to kick - putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) - it is *not recommended* to use this everywhere - quite the contrary! — marc_s, Apr 27 '23 at 04:17

Paul Maxwell · Answer 1 · 2023-04-27T05:02:52.237

Without looking too deeply at your query, and in the absence of any sample data, I suggest you use row_number() over() to locate the most recent transaction, and then filter by (or in a join condition) the generated row numbers (column rn below):

SELECT
*
FROM (
    SELECT
          CustomerID
        , DocumentNumber
        , TransactionDate
        , PaymentDueDate
        , CASE 
            WHEN TransactionType = 1 THEN 'Invoice' 
            WHEN transactiontype = 2 THEN 'CreditNote' 
            WHEN transactiontype = 3 THEN 'FC' 
            WHEN transactiontype = 4 THEN 'Payment' 
            WHEN TransactionType = 8 THEN 'Adj' 
            ELSE '' 
          END AS Type
        , OriginalAmount
        , AmountOutstanding
        , DATEDIFF(DAY, PaymentDueDate, GETDATE()) AS DayePastDue
        , DATEDIFF(DAY, TransactionDate, GETDATE()) AS DaysFromShipment
        , AccountsTransactionID
        , ROW_NUMBER() OVER(PARTITION BY CustomerID 
                            ORDER BY TransactionDate DESC) AS rn
    FROM AccountsTransaction
    -- nb: you cannot filter by ROW_NUMBER here, it has to be later as seen below
    ) AS atran
WHERE atran.rn = 1

The OVER clause partitions the data by customerid (PARTITION BY is similar to a GROUP BY clause). Within each partition, the data is ordered by TransactionDate in descending order. Then the ROW_NUMBER() function assigns a number to each row, starting with 1 which will be the most recent date and increments by 1 for each subsequent row. When a new customerid is encountered, the row numbering restarts at 1.

You can apply this in your existing select clause but using J.JobReference as the partiioning column, but note you must add another "layer" to the query to do the "where rn = 1" so you may want to avoid including all the column aliases until you reach the outermost query layer.

SELECT
    *
FROM (
    SELECT
          J.JobReference,
        , AT.AccountsTransactionID
        , ROW_NUMBER() OVER(PARTITION BY J.JobReference 
                            ORDER BY at.TransactionDate DESC) AS rn
        , B1.Name AS b1Name
        ...    
    FROM AccountsTransaction AS AT WITH (NOLOCK)
    LEFT JOIN Customer AS C ON C.customerid = AT.CustomerID
    LEFT JOIN Branch AS B ON B.branchid = AT.BranchID
    LEFT JOIN Branch AS B1 ON B1.branchid = C.HomeBranchID
    LEFT JOIN CustomerAddress AS CA ON CA.customerAddressID = AT.DeliveryAddressID
    INNER JOIN Job AS J ON j.jobID = CA.JobID 
    ) AS d
WHERE rn = 1

nb: I suggest you would not use a left join if job numbers are vital to the query...

Is there a way to organize by last invoice date by job?

1 Answers1