Hi Tony, This should do it for you, just make sure to chose the dates to suit, ( weekly, daily, monthly etc.. ) select workerstaffname, (sum(timespent)/60/60) as 'Time Worked (hours)' from swtickettimetracks where ((date(FROM_UNIXTIME(dateline)) between '2011/01/01' AND '2011/10/31')) Group by workerstaffname Gary
Hi Jlc, Thats not possible inside kayako, but you can use an external script to do it, which you could schedule to run every week on a server or something Gary
Well thanks Gary for all your advice you have given, it has helped so much. What we are missing though is a way to find a way to see how many tickets where open at the beginning of the day (say 00:01) and how many where open at the end of day (say 23:59) Derren
Hi Derren, This here will do it 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 and ((date(FROM_UNIXTIME(swtickets.dateline)) between '2011/10/06' AND '2011/10/06')) Group by swdepartments.title, swticketstatus.title Pretty much if you use the day before, it will list how many open tickets upto midnight, if you use the next day, it will show how many of those you closed Gary
Thanks Gary. You make it to easy So I shall bug you just once more if you have the time. What would also like is something like the following DATE - TICKETS OPENED - TICKETS CLOSED 3/10/11 34 33 4/10/11 14 15 thanks Derren
Hi Derren, Ok, here is the code, please please please, do not remove the where clause, or you will turn your SQL server into a doorstop while it tries to work it out for every date in your entire database. This is actually quite an intensive query, so if you dont limit the search range to something sensible, it will take a long time. SELECT date(FROM_UNIXTIME(swtickets.lastactivity)) as 'CDate' , ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.dateline)) = CDate and isresolved=0 ) as 'Open', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = CDate and isresolved=1 ) as 'Closed' from swtickets where ((date(FROM_UNIXTIME(swtickets.lastactivity)) between '2011/10/01' AND '2011/10/31')) Group by date(FROM_UNIXTIME(swtickets.lastactivity)) Gary
Thanks once again Gary with regards to my first query; am I right in thinking that this will list tickets that are still open THAT WHERE CREATED within the date range? what I am looking at now is, can I chose a date, and then be shown how many tickets where still open at the end of that day, regardless of department or when the ticket was created. ie last thurday there where 5 tickets still open at close of business, 3 of them where over 5days old. etc thanks Derren
Hi Darren, This is quite tricky, as the only two fields you can check dates against are "date created" and "date last activity", neither of these technically give you what your looking for. ( if tickets are open for an extended period it gets blurry What your looking for is changing this: where swtickets.isresolved = 0 and ((date(FROM_UNIXTIME(swtickets.dateline)) between '2011/10/06' AND '2011/10/06')) to this: where swtickets.isresolved = 0 That will show "all open tickets right now", and you would have to ensure you ran that every day, you could not go "back in time to check" Gary
thanks gary. thats what i feared. i was even looking into audit trails to find and exclude everything that was switched to close.
I would like to have a query that : gets the tickets that were created "today" (as a variable) and that were closed "today". So each day today auto changes. if possible also query that shows how many tickets were closed by a technician/staff on a specific date
Hi, does anyone have any idea why this SQL doesn't work? (Doesn't produce any results) Code: SELECT 'Tickets.Ticket Mask ID', 'Ticket Billing.Creation Date', 'Tickets.Subject', 'Tickets.Priority', 'Ticket Billing.Time Billable' FROM 'Ticket Billing' WHERE 'Ticket Billing.Creation Date' <= LastMonth();
Hi bob, I think you would just need to add in the tickets table, e.g. from 'Tickets', 'Ticket Billing' Gary
hi, I need to generate a report to retrieve info with the count of tickets by user in descending order , like top 10 of ticket post users..any guideline will be appreciated. thanks
Guys, I've been fiddling around with kayako and cant get this one working right. We are trying to do a report based on the amount of replies a particular staff member has made both daily (shows each day and the number of replies by each staff member) and weekly (shows 4 weeks and the number of replies by each staff member). Any ideas?
Gary, Any idea how I would be able to pull Custom Fields? I need to generate a report based on our defined categories.
Hi jlc, I came here to ask exactly the same question. I need to pull a table that contains the tickets and the rows are ticketid, full name, customfield 8 and customfield 9. So far I cant find the relationship between swtickets and swcustomfieldvalues. Anyone any ideas?
