I have a table which contains Issues and SubIssues in a customer service system. The data might look like this:
| id | ticket_id | sub_ticket_id | response_time_in_mins | comment | cond1 | cond2 | cond3 |
|---|---|---|---|---|---|---|---|
| 1 | 1000 | NULL | NULL | "the overall ticket about how quickly you closed down my account" | 1 | 0 | 1 |
| 2 | 1000 | 1 | 12 | "send ack email" | 1 | 0 | 1 |
| 3 | 1000 | 2 | 30 | "look up user in all DBs and remove" | 1 | 1 | 1 |
| 4 | 1000 | 3 | 5 | "send finished email" | 1 | 0 | 0 |
| 5 | 1001 | NULL | NULL | "the overall ticket about do you have my email in your sys?" | 1 | 0 | 1 |
| 6 | 1001 | 1 | 2 | "send ack email" | 0 | 0 | 1 |
| 7 | 1001 | 2 | 10 | "look up and notify" | 1 | 1 | 0 |
| ... |
I'd like to aggregate total response times for each ticket_id satisfying each of the conditions at a time. For example the resulting table may look like this (but assume N conditions)
| ticket_id | total_resp_time_cond_1 | total_resp_time_cond_2 | total_resp_time_cond_3 |
|---|---|---|---|
| 1000 | 47 | 30 | 42 |
| 1001 | 10 | 10 | 2 |
| ... |
Here it is in pseudo code if I could do Python+SQL:
foreach ticket_number in issues_and_subissues:
foreach condition in cond1, cond2, cond3, ... condN:
SELECT sum(response_time) FROM issues_and_subissues isst WHERE isst.ticket_id=ticket_number AND condition=1
Is there a T-SQL-only way to achieve this via JOINs? or would I have to use CURSERS? any other options?
I searched on Stackoverflow and beyond and all I could find was the CURSER way of doing it. Thank you!
It's called conditional aggregation. You do it by putting a CASE expression inside an aggregate function:
SELECT ticket_id,
SUM(CASE WHEN cond1 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_1,
SUM(CASE WHEN cond2 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_2,
SUM(CASE WHEN cond3 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_3
FROM [table]
GROUP BY ticket_id
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments