Search code examples
mysqlsqlnavicat

Find Earliest Transaction Date when using Transaction Date in "WHERE" clause

I'm creating a daily report for transactions that happened the day before on purchase (both new and previous). For simplicity, it looks like: 

SELECT 
Table.Identifier as 'Number',
Table.Name as 'Customer Name',
Table.PurchaseDate as 'Date',
sum(Table.Amount) as 'Amount',
Case 
when 'SOME CODE' then 'NEW'
when sum(Table.Amount) > 0 then 'INC'
when sum(Table.Amount) < 0 then 'DEC'
end case as 'Transaction Type'
Where
Table.TransactionEntryDate = DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()))

The transaction date is the last business day, (if today's Monday, the last day is Friday) I only want entries with the transaction date as the last business date, but in order to classify it as a NEW Transaction Type, I need to see if today's transaction date is the minimum transaction date for the Identifier / Number. but as the WHERE clause already filters out all the non-required dates, any clause I put in the 'SOME CODE' spot of the CASE clause will return NEW

ie if we consider the two cases

Transaction Identifier #1 occurs some days ago, but a return transaction and an another purchase was made the prior business day. (We care about net transaction) There are therefore 2 entries in the Table. I want the CASE function to see that the minimum (ie first) Entry Date is NOT the prior business day and continue on to "DEC" case.

Transaction Identifier #5 occurs the prior business day. This is the first entry in the Table. I want the case function to recognize that Identifier #5's earliest Entry Date is the prior business date so the CASE function assigns Transaction Type as NEW.

sample table described above

However, I can't use a count function as sometimes there are multiple transactions for the same identifier on the same day and I want them summed. Sometimes the "Purchase Date" is different than the transaction date, as orders are pre-ordered prior to the actual transaction, so I can't just do when PurchaseDate = TransactionEntryDate

Here is what I'd like the table to look like. However as the Identifier #1 has the Feb-25 Transaction filtered out, a when when min(Table.TransactionEntryDate) = Table.TransactionEntryDate then 'NEW' will also return 'NEW'

desired outcome

Something like this, (but it doesn't work in my query) 

when min(All Table.TransactionEntryDate where Table.identifier= Table.Identifier AND Table.PurchaseDate= Table.PurchaseDate) = DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE())) then 'NEW'
Solution

  • I think this'll identify for each row if an earlier row exists:

    OUTER APPLY (SELECT TOP(1) Identifier
             FROM Table T1
             WHERE T1.Identifier = Table.Identifier
             AND T1.TransactionEntryDate < Table.TransactionEntryDate) AS PriorTransactionExists

    And then in your case:

    CASE WHEN PriorTransactionExists.Identifier IS NULL THEN 'NEW'

    You could probably just do it as an inline exists instead of the outer apply? Hopefully I've understood but apologies if not.

    EDIT AFTER REALISING THIS IS MYSQL:

    Case when not exists (SELECT 1 FROM Table T1
             WHERE T1.Identifier = Table.Identifier
             AND T1.TransactionEntryDate < Table.TransactionEntryDate) THEN 'New'