1

Hi,

Im wandering if anyone can help me tweak an existing report i have created, or help create a new one if the old one cannot be made suitable.

Basically the report looks for opened and closed calls per day, and works out the difference. The open tickets part of the report works fine, its the closed part im having a couple of issues with.

It currently only shows tickets that have been open AND closed that day. I want it to show tickets that have been opened and closed that day, and tickets which have been opened previously but closed on that day.

Hopefully the difference should then go into minus numbers.

Any help appreciated.

Current code :

SELECT strftime(‘%d’,created_at)||‘/’||strftime(‘%m’,created_at)||‘/’||strftime(‘%Y’,created_at) as Date, count(strftime(‘%m’,[created_at])) AS Opened, count(strftime(‘%m’,[closed_at])) AS Closed ,count(strftime(‘%m’,[created_at])) - count(strftime(‘%m’,[closed_at])) as Difference
FROM tickets
group by Date
having (date([created_at]))>=date(‘now’,‘-5 day’);

stats.JPG
stats.JPG812×148 13.3 KB
2 Spice ups
2

Just change the where statement to include ticket that were closed today

Where (tickets.closed_at>=date(‘now’,‘-0 days’))

3

Actually, the report is just looking at tickets created in the last 5 days. That they happen to be closed within that time period as well is coincidental - not determined by the SQL.

I looked at your code and combined the where clause, but that makes the report show only tickets closed today. I understood that the ticket could be closed any day within the last 5 days. Which I see is why you opted to try the group / having clause.

I like SQL challenges, taking a look…

4

Thanks Yasaf for your reply, but i cant quite get your suggestion to work.

Ive attached an example of what im trying to get it to look like.

stats2.JPG

5

The trick is that for the Closed tickets you actually need to use the closed_at date instead of the created_at date. Otherwise the Closed ticket can have a created_at date that is prior to the last 5 days, and does not appear on the report.

If you remove the Having clause, those older ones will appear, but on different created dates.

I am thinking this is going to need some kind of union, or some more smarts to force it to use the closed_at in place of created_at.

Time for lunch now, will continue later…

6

Mike5623 wrote:

Thanks Yasaf for your reply, but i cant quite get your suggestion to work.

Ive attached an example of what im trying to get it to look like.

I think I didn’t explain myself very well,

I meant to use my code only for the part referring to tickets that where closed today.

As Michael1971 you’ll need to divide the report into two sections

  • Tickets opened and closed today
  • Tickets opened earlier and closed today
7

Would you be able to test this in your environment?

SELECT
CASE WHEN closed_at >= date(‘now’,‘-5 day’)
THEN strftime(‘%d’,closed_at)||‘/’||strftime(‘%m’,closed_at)||‘/’||strftime(‘%Y’,closed_at)
WHEN created_at >= date(‘now’,‘-5 day’)
THEN strftime(‘%d’,created_at)||‘/’||strftime(‘%m’,created_at)||‘/’||strftime(‘%Y’,created_at)
END As “Ticket Date”
, count(strftime(‘%m’,[created_at])) as Opened
, count(strftime(‘%m’,[closed_at])) as Closed
, count(strftime(‘%m’,[created_at])) - count(strftime(‘%m’,[closed_at])) as Difference
FROM tickets
Where ( tickets.closed_at >= date(‘now’,‘-5 days’) )
or ( tickets.created_at >= date(‘now’,‘-5 days’) )
GROUP BY “Ticket Date”

8

Actually I can already see that it is double counting the tickets that are closed on a particular day, but opened in an earlier timeframe.

For example, I have 2 tickets created today in my environment. We closed 6 tickets today. The report correctly tallies the Closed tickets at 6, but it makes the Opened tickets equal to 7. That is: 6 Closed today plus 1 that is still open = 7.

Hm…

9

@Yasaf & Michael1971 - thank you for taking a look :slight_smile:

as you can probably tell my SQL skills are limited.

Im happy to have two separate reports for open + closed if its easier ?

Im basically trying to replicate the stats we could pull from our old help desk system, before i push spiceworks into production.

10

Mike, pls. try this:

select strftime(‘%d’,tkt_Date)||‘/’||strftime(‘%m’,tkt_Date)||‘/’||strftime(‘%Y’,tkt_date) as Date,
(select count(id) from tickets where date(created_at)=tkt_date) as opened,
(select count(id) from tickets where date(closed_at)=tkt_date) as closed,
(select count(id) from tickets where date(created_at)=tkt_date)-(select count(id) from tickets where date(closed_at)=tkt_date)
as Difference
from
(select distinct date(created_at) as tkt_Date from tickets
where date(created_at) between date(‘now’,‘-1 month’) and date(‘now’)
union
select distinct date(closed_at) as tkt_Date from tickets
where date(closed_at) between date(‘now’,‘-1 month’) and date(‘now’))
order by tkt_Date desc

The statement: … between date(‘now’,‘-1 month’) and date(‘now’) controls the interval you are looking into, so if needed you have to can change it accordingly on both closed_at and created_at ranges.

I hope it helps,

Regards,

11

@Dragos1970 -

Amazing ! that looks like its exactly what i needed.

Thank you. Ill monitor it for the next couple of days and report back.

12

Nice! Yes, nice and clean. Awesome.

Are you going to publish this as a report in the community or shall I?

13

Michael,

Pls. be my guest. This report shows only part of info that can be extracted from tickets, and is shown in the reports “package” that I’ve posted some time ago at http://community.spiceworks.com/reports/2125, http://community.spiceworks.com/reports/2126 and http://community.spiceworks.com/reports/2127 for current week.

There are also “packages” for current month/year and previous week/month/year.

Perhaps I didn’t made a very clear description of them, but are very useful for me.

I’ve attached some screenshots of those reports - just to not download them if you not need them.

reports_picture.zip (261 KB)

14

I tried adding a Totals line so we can see easily whether the performance is positive or negative over the total timeframe. It messes with the sorting though as the column becomes text rather than date. Not sure if there is a way around that - perhaps sorting inside the FROM clause in the first part of the query.

I will also take a look at what you have out there. Thanks!

SELECT strftime(‘%d’,tkt_Date)||‘/’||strftime(‘%m’,tkt_Date)||‘/’||strftime(‘%Y’,tkt_date) as “Ticket Date”,
(select count(id) from tickets where date(created_at)=tkt_date) as “Opened”,
(select count(id) from tickets where date(closed_at)=tkt_date) as “Closed”,
((select count(id) from tickets where date(created_at)=tkt_date)-(select count(id) from tickets where date(closed_at)=tkt_date)) as “Difference”
FROM
(select distinct date(created_at) as tkt_Date from tickets
where date(created_at) between date(‘now’,‘-7 day’) and date(‘now’)
union
select distinct date(closed_at) as tkt_Date from tickets
where date(closed_at) between date(‘now’,‘-7 day’) and date(‘now’))
UNION ALL
SELECT ‘Totals’,
(SELECT count(id) from tickets where date(created_at) between date(‘now’,‘-7 day’) and date(‘now’)),
(SELECT count(id) from tickets where date(closed_at) between date(‘now’,‘-7 day’) and date(‘now’)),
((SELECT count(id) from tickets where date(created_at) between date(‘now’,‘-7 day’) and date(‘now’))
-(SELECT count(id) from tickets where date(closed_at) between date(‘now’,‘-7 day’) and date(‘now’)))
order by “Ticket Date”

15

I ended up just moving the Year to the front so Y/m/d - that puts the days into order.

16

The finished product:

http://community.spiceworks.com/reports/2179

17

The report Dragos1970 created worked perfectly.

Thanks to all that helped.