Computer Hope

Software => Computer programming => Topic started by: Dan O on July 23, 2012, 02:17:57 PM

Title: SQL Query for Previous Month
Post by: Dan O on July 23, 2012, 02:17:57 PM

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.

Code: [Select]


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')


Title: Re: SQL Query for Previous Month
Post by: Sally Smith on July 27, 2012, 09:58:36 PM

They do Batch files?   SQL?

Good Luck

Title: Re: SQL Query for Previous Month
Post by: Dan O on July 30, 2012, 08:45:46 AM

I'm not quite sure what you are asking me?

Title: Re: SQL Query for Previous Month
Post by: Rob Pomeroy on September 11, 2012, 07:30:30 AM

Um - why not use SQL's date diff functions?  Which flavour of SQL is this?