i want to get sql script for this please :
get report for assign , claim open , closed ticket based on the staff between 30 days
the most important is assign and claim tickhet count
get report for total of assign , claim, open , closed ticket based on the staff
- Best Answerset by KevinTheJedi
You can take a look at the ERDs to help make your own SQL queries:
We cannot assist with custom scripts/sql/etc.
Cheers.
thanks a lot
- Edited
i created this but it does not get the correct data
SELECT
s.firstname AS staff_member,
SUM(CASE WHEN t.status_id IN (1, 2) THEN 1 ELSE 0 END) AS open_tickets,
SUM(CASE WHEN t.status_id = 3 THEN 1 ELSE 0 END) AS closed_tickets,
COUNT(t.ticket_id) AS thread_count,
SUM(CASE WHEN t.staff_id IS NOT NULL THEN 1 ELSE 0 END) AS assigned_tickets
FROM
ost_ticket t
left JOIN
ost_thread th ON t.ticket_id = th.object_id
JOIN
ost_staff s ON t.staff_id = s.staff_id
WHERE
t.created >= DATE_SUB(NOW(), INTERVAL 30 DAY) && th.object_type='T'
GROUP BY
s.username
ORDER BY
s.username;ok got it
but In which table and column the claim stored ?
help me with this only please
20 days later
I believe you just need to play around with this part: && th.object_type='T'
'&&' is deprecated and will be removed in a future . Please use AND instead.
3 months later
- Edited
Based off your code (thanks by the way) try this:
[modified code removed by admin]