Post your helpful reports here!

Hi there,

Ok, this is going to be a wall of text, so bear with me, I am going to post various SQL searches for things which people might like ( sorry! )

This is the SQL you can use to pull out chat information into a table that you can then use to build a pivot table and view various stats

select chatobjectmaskid, (FROM_UNIXTIME(dateline)) as "Date of Chat", staffname, waittime, TIMEDIFF(FROM_UNIXTIME(lastpostactivity),FROM_UNIXTIME(dateline) ) as "Length of Chat"
from swchatobjects
where chatstatus = 3 and ((date(FROM_UNIXTIME(dateline)) between '2011/05/06' AND '2011/06/15'))

But for your more specific number of chats per staff, you can use the below:

N.B. If gives you staff name, number of chatsm average wait time to answer a chat request, length of all chats added together, per date range.

select staffname, chatobjectmaskid,count(*) as "No. Chats", avg(waittime), Sec_to_Time(Sum(Time_to_Sec( TIMEDIFF(FROM_UNIXTIME(lastpostactivity),FROM_UNIXTIME(dateline) ) )))as "Length of Chat"
from swchatobjects
where chatstatus = 3 and ((date(FROM_UNIXTIME(dateline)) between '2011/05/06' AND '2011/06/15'))
group by staffname

Then onto ticket SQL!

List number of open tickets per department

SELECT swdepartments.title as Department,swticketstatus.title as Status, count(swtickets.ticketmaskid) as 'Number of Tickets'
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swdepartments
On swdepartments.departmentid = swtickets.departmentid
where swtickets.isresolved = 0
Group by swdepartments.title, swticketstatus.title

( I made it ignore "resolved tickets" and display everything else )

This here is the SQL you will need for number of tickets per week per staff member.

SELECT swticketstatus.title as Status, count(*) as "No of Tickets", swstaff.fullname
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swstaff
on swstaff.staffid = swtickets.ownerstaffid
where ((date(FROM_UNIXTIME(swtickets.lastactivity)) between '2011/06/06' AND '2011/06/15'))
Group by swticketstatus.title, swstaff.fullname
Order by swstaff.fullname

If you want to create a pivot table to give you all sorts of views, this code here can create the raw table:

N.B. The ratings for feedback are going to be unique to the ones you created, if you run this SQL here:

SELECT benchmarkid, benchmarktitle from swbenchmarks

It will list your benchmark name and ID, you then just need to amend the SQL below to put the correct benchmark and name in. ( you can also delete all the benchmark stuff if you dont need them )

SELECT swtickets.ticketmaskid, swtickets.isescalated as Escalated, swticketstatus.title as Status, swticketpriorities.title as Priority, swdepartments.title as Department, swstaff.fullname as "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) as Created,
(select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) as "Overall Satisfaction",
(select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=2 ) as "Communication",
(select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=3 ) as "Response Time",
(select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=4 ) as "friendliness"
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swticketpriorities
On swticketpriorities.priorityid = swtickets.priorityid
Right Join swdepartments
On swdepartments.departmentid = swtickets.departmentid
Right Join swstaff
on swstaff.staffid = swtickets.ownerstaffid

I hope all that made sense for you, holla back if its gibberish lol

Gary

Glad to help

I know what its like with a boss on your back about stats and not being able to get them.

Gary

Hi Rafael,

We were told this month at some point, but honestly I would not expect it until Aug

Gary

Hi Teejay,

Do you mean you want a count of tickets that have "not been" escalated? ( i.e. closed off / replied before time ran out )

Gary

Hi Tony,

This SQL should do the trick for you:

SELECT swdepartments.title as Department,prioritytitle as Priority, count(swtickets.ticketmaskid) as 'Number of Tickets'
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swdepartments
On swdepartments.departmentid = swtickets.departmentid
where swtickets.isresolved = 1 and isescalated = 0 and escalationruleid != 0
Group by swdepartments.title, prioritytitle

Gary

Hi Evilution,

Not a problem

Current "Open" tickets per department:

SELECT swdepartments.title as Department, count(swtickets.ticketmaskid) as 'Number of Tickets'
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swdepartments
On swdepartments.departmentid = swtickets.departmentid
where swticketstatus.title = "Open"
Group by swdepartments.title

Closed tickets per department ( Past month )

SELECT swdepartments.title as Department, count(swtickets.ticketmaskid) as 'Number of Tickets'
from swtickets
Right Join swticketstatus
On swtickets.ticketstatusid = swticketstatus.ticketstatusid
Right Join swdepartments
On swdepartments.departmentid = swtickets.departmentid
where swtickets.isresolved = 1 and ((date(FROM_UNIXTIME(swtickets.resolutiondateline)) between '2011/05/01' AND '2011/05/31'))
Group by swdepartments.title

( Note on that one, you just need to adjust the dates to suit, so you can make it weekly, two weekly, monthly, yearly etc.. )

Gary

Hi Sunny,

You can track this, but as there is no "logging" of macro use, the only way you can count it up would be to run a series of sql searches against your actual ticket replies.

This SQL below shows how many staff have used a specific macro ( per staff member )

SELECT swstaff.fullname, count(*) FROM swticketposts
Right Join swstaff
on swstaff.staffid = swticketposts.staffid
WHERE contents like "%There are a couple of things you can check which will help ensure your able to log in.%"
group by swticketposts.staffid

But you would need to change the "%There are a couple of things you can check which will help ensure your able to log in.%" to match the text you have in your macro. Please note that the % sign is actually an SQL symbol for "anything", so you need to keep them on both ends of the string, so that it will find the macro text "anywhere" within the reply.

Also note that on a large database, this will be quite an intensive search and make take a little while.

Gary

Hi Tony,

This here should do the trick for you:

Code:

SELECT swtickets.ticketmaskid, swticketstatus.title AS Status, swticketpriorities.title AS Priority, swdepartments.title AS Department, swstaff.fullname AS "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) AS Created,
(SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) AS "Overall Satisfaction",
(SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=5 ) AS "Communication",
(SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=6 ) AS "Response Time",
(SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=7 ) AS "friendliness",
(SELECT contents from swticketposts where swticketposts.ticketid = swtickets.ticketid and issurveycomment=1 ) AS "FeedBack Comment"
FROM swtickets
RIGHT JOIN swticketstatus
ON swtickets.ticketstatusid = swticketstatus.ticketstatusid
RIGHT JOIN swticketpriorities
ON swticketpriorities.priorityid = swtickets.priorityid
RIGHT JOIN swdepartments
ON swdepartments.departmentid = swtickets.departmentid
RIGHT JOIN swstaff
ON swstaff.staffid = swtickets.ownerstaffid
WHERE ((date(FROM_UNIXTIME(dateline)) between '2011/08/01' AND '2011/08/16')) AND (swticketstatus.title = 'Closed')
ORDER BY swtickets.dateline ASC

Gary

Your welcome

Gary