1. We have loaded all the SQ canceled flights
2. Then ran a report of all international departures from NZ from 25 March to 30 June
showing Carrier, Flight No and Date then imported this into the Canceled flights table
3. Then I ran a report of all ticket numbers that had canceled flights not refunded or exchanged
flown Jan 2020 - Mar 2021
These we assume will be suspended
4. I imported that list of tickets and set them to suspended (so Held in Credit if not refunded or exchanged)
5. A simple Pivot report shows
- Flown revenue by airline
- allows you to include or exclude tickets in credit
- Value of tickets in credit expiring by date.
This is of course only an approximation of your real situation but is better than nothing.