1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| SELECT g.name as groupname,
CONCAT_WS(' ', u.`firstname`, u.`name`) AS username, t.id,
sum(case when TO_DAYS(NOW()) - TO_DAYS(t.date)<3 then 1 else 0 end) AS `3days`,
sum(case when TO_DAYS(NOW()) - TO_DAYS(t.date)>=3 and TO_DAYS(NOW()) - TO_DAYS(t.date)<7 then 1 else 0 end) AS `7days`,
sum(case when TO_DAYS(NOW()) - TO_DAYS(t.date)>=7 and TO_DAYS(NOW()) - TO_DAYS(t.date)<14 then 1 else 0 end) AS `14days`,
sum(case when TO_DAYS(NOW()) - TO_DAYS(t.date)>=14 and TO_DAYS(NOW()) - TO_DAYS(t.date)<30 then 1 else 0 end) AS `30days`,
sum(case when TO_DAYS(NOW()) - TO_DAYS(t.date)>=30 then 1 else 0 end) AS `more`
FROM users u
INNER JOIN profiles_users up on ( u.id=up.users_id)
INNER JOIN profiles p on (p.id=up.profiles_id)
INNER JOIN tickets_users tu on (tu.users_id=u.id and tu.type='2')
INNER JOIN tickets t on (t.id=tu.tickets_id)
LEFT JOIN groups_users gu on (gu.users_id=u.id)
LEFT JOIN groups g on (gu.groups_id=g.id)
WHERE t.status='4' and u.name="...."
Group by t.id |
