I have come up with the following query which gives me a summary of my help desk for the previous month. The only issue that I run into is when January rolls around the it will be looking for the month which will end up as 0 and the year will be 2013. It should be looking for 12/2012. This is my first attempt at SQL scripts and I'm not sure if conditional statements are possible in SQL. I don't want to have to worry about changing the code every January. Any help would be appreciated.
Select "* Tickets in the system (Since " || min(created_at) || ") *" as "Category",count(id) as "Total"
from tickets
union
Select "1 - Tickets opened this month" as "Category",count(id) as "Total"
from tickets t
where strftime('%m',t.created_at) = strftime('%m','now','-1 month')
and strftime('%Y',t.created_at) = strftime('%Y','now')
union
Select "2 - Tickets closed this month" as "Category",count(id) as "Total"
from tickets t
where strftime('%m',t.closed_at) = strftime('%m','now','-1 month')
and strftime('%Y',t.closed_at) = strftime('%Y','now')
union
Select "3 - Tickets currently open" as "Category",count(id) as "Total"
from tickets t
where (closed_at is null)
union
Select "4 - Tickets open more than 5 days" as "Category",count(id) as "Total"
from tickets t
where (closed_at is null) and (created_at<=date('now','-5 days'))
union
Select "5 - Tickets average days open" as "Category", round(avg((julianday(closed_at) - julianday(created_at))),2) as "Total"
from tickets t
where (status='closed')
union
Select "6 - Incident Counts This Month" as "Category",count(id) as "Total"
from tickets t
where (c_type='Incident') and strftime('%m',t.created_at) = strftime('%m','now','-1 month')
and strftime('%Y',t.created_at) = strftime('%Y','now')
union
Select "7 - Request Counts This Month" as "Category",count(id) as "Total"
from tickets t
where (c_type='Request') and strftime('%m',t.created_at) = strftime('%m','now','-1 month')
and strftime('%Y',t.created_at) = strftime('%Y','now')
union
Select "8 - Change Counts This Month" as "Category",count(id) as "Total"
from tickets t
where (c_type='Change') and strftime('%m',t.created_at) = strftime('%m','now','-1 month')
and strftime('%Y',t.created_at) = strftime('%Y','now')
