I'm trying to find a way to create a query that returns transactions from individuals when they submit transactions that are traditionally accompanied by other transactions.
Example (with data below):
We see employees had a flight purchased (with the date the flight was purchased). Then they submitted baggage fees with transaction dates that match either the departure date and the return date. We then see they submitted a third one that doesn't match any of the airline dates.
Is there a way to identify for each person that has a baggage fee if they have an airline departure/return date that matches the transaction date? In the example below, the last baggage fees for each employee are the rows I'm hoping to return since they don't match the depart/return date for their respective flights.
DB NAME = ExpenseTable
Employee | Type | TransactionDate | Depart | Return |
---|---|---|---|---|
123 | Airline | 12052019 | 12082019 | 12132019 |
123 | Baggage | 12082019 | NULL | NULL |
123 | Baggage | 12132019 | NULL | NULL |
123 | Baggage | 12252019 | NULL | NULL |
567 | Airline | 11052020 | 11082020 | 11132020 |
567 | Baggage | 11082020 | NULL | NULL |
567 | Baggage | 11132020 | NULL | NULL |
567 | Baggage | 11252020 | NULL | NULL |
I think you could use "WHERE NOT EXISTS" to do what you're looking for. Something like this:
SELECT *
FROM Transactions T1
WHERE T1.Type = 'Baggage'
AND NOT EXISTS (
SELECT *
FROM Transactions T2
WHERE T2.Type = 'Airline'
AND (T1.TransactionDate = T2.Depart OR T1.TransationDate = T2.Arrive)
)
The NOT EXISTS excludes any baggage records that have a matching airline record, based on either the depart date or arrive date. (Putting EXISTS by itself would do the opposite...it would only show baggage transactions with matching airline transactions.) You could probably do this with a join, but EXISTS/NOT EXISTS is nice because it doesn't give you duplicate rows in your result set.