Am in need of a second opinion on a SQL query please.
I am trying to make my sales transfer script triggered rather than all at end of day.
I need a few constraints/conditions of the query.
- Tickets must be closed/paid in full to avoid duplicate ticket transfers.
- Tickets must not be a room post payment type as these are transferred per ticket on demand.
- Tickets must only be transferred once, Im pretty sure I have achieved this with the LastPaymentDate and IsClosed
So im pretty sure this query will return list of ticket ids for tickets which have been fully paid within the date range excluding room post tickets.
The LastPaymentDate im confident should include older tickets which were not fully paid at last query.
I have run on some sample transaction data and seems to work just hoping for a second opinion from others with more experience with the database structure.
The query is;
(For testing purposes I used Credit Card rather than Room Post as not configured on this setup version.
SELECT DISTINCT t.[Id]
FROM [Tickets] t LEFT JOIN [Payments] p ON t.[Id] = p.[TicketId]
WHERE t.[LastPaymentDate] > '2016-01-10 01:00:00'
AND t.[LastPaymentDate] < '2016-01-10 02:20:00'
AND t.[IsClosed] = 'True'
AND p.[TicketId] NOT IN (
SELECT [TicketId]
FROM [Payments]
WHERE [Name] LIKE 'Credit Card'
)