I have a SQL that aggregates the monthly ticketing of different organisations to give an output like this -
| Organization | Course | Month | Last Updated | Status | Tickets This Month | Total Tickets | Deleted Tickets |
|---|---|---|---|---|---|---|---|
| OrgABC | C1 | 03/23 | 2023-07-21 | ACTIVE | 12 | 12 | |
| OrgABC | C2 | 03/23 | 2023-04-19 | ACTIVE | 4 | 4 | |
| OrgABC | C1 | 04/23 | 2023-07-21 | ACTIVE | 3 | 15 | |
| OrgABC | C2 | 04/23 | 2023-04-19 | DELETED | 2 | 6 | |
| OrgABC | C2 | 05/23 | 2023-04-19 | DELETED | 0 | 0 | 6 |
| OrgABC | C1 | 05/23 | 2023-07-21 | ACTIVE | 2 | 17 | |
| OrgABC | C1 | 06/23 | 2023-07-21 | ACTIVE | 3 | 20 | |
| OrgXYZ | C2 | 06/23 | 2023-08-15 | ACTIVE | 5 | 5 | |
| OrgXYZ | C2 | 07/23 | 2023-08-15 | ACTIVE | 3 | 8 | |
| OrgABC | C1 | 07/23 | 2023-07-21 | DELETED | 0 | 20 | |
| OrgABC | C1 | 08/23 | 2023-07-21 | DELETED | 0 | 0 | 20 |
| OrgXYZ | C2 | 08/23 | 2023-08-15 | DELETED | 2 | 7 | |
| OrgXYZ | C2 | 09/23 | 2023-08-15 | DELETED | 0 | 0 | 7 |
At this stage, I only have up till the Tickets This Month column in my query. The Total Tickets column aggregates the running sum of all tickets for an organization and course, whereas the running sum gets converted to Deleted Tickets if the status becomes 'DELETED'. What can I do to add two columns like Total Tickets and Deleted Tickets above where the logic is -
IF status = 'DELETED' and last_updated < billing_month
THEN convert total_tickets TO deleted_tickets AND make total_tickets = 0
ELSE total_tickets = total (prev_month) + tickets (current_month) AND deleted_tickets = 0
so that my table looks like the above table. Can you help me make these two columns please?
