Quarterly Report

[Marclem]

Hi Everyone,

Below I have the following code and highlighted upon researching and tweaking the quarterly data I would like to retrieve but the results give me data from Oct 1st- Dec 28th 2016. I would like to be able if possible to change the year and quarter of the year so I may choose accordingly. i.e. Year 2015 get 1st quarter data only (Jan-March)

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       DATE_FORMAT(HD_TICKET.DUE_DATE, '%m/%d/%Y %H:%I:%s') as DUE_DATE,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       CASE WHEN HD_TICKET.TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as 'SLA_Met?', 
     #TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,#
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
[b]where HD_STATUS.STATE = 'closed'
and ((date(HD_TICKET.TIME_CLOSED)  >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month) 
and date(HD_TICKET.TIME_CLOSED)  <  date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))[/b]
order by HD_PRIORITY.ORDINAL, OWNER_NAME, HD_TICKET.TIME_CLOSED, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Thank you for your support!

 

[ChrisHirst]

I'm not even going to try to figure out that query, it will probably give me nightmares because I have no way of actually testing it.

What I would suggest is you break it down somewhat and put the process(es) into "Stored Procedures" that you can trigger and/or send parameters to. It will be far more efficient than a simple (using the term rather loosely) query would be.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[Marclem]

Hi Chris,

I am not a SQL specialist which I am learning with researching and support from you guys. when you say "break it down somewhat and put the process(es) into "Stored Procedures" that you can trigger and/or send parameters to. It will be far more efficient than a simple (using the term rather loosely) query would be" can you put in more detail how to put the process into Stored Procedures"?

As I stated before, in the code below which I gathered thru research could you help me understand (2nd and 3rd lines) what each query is actually doing? I believe I already have the correct code query but just need to understand it so I may make changes accordingly:

where HD_STATUS.STATE = 'closed'
and ((date(HD_TICKET.TIME_CLOSED)  >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month) 
and date(HD_TICKET.TIME_CLOSED)  <  date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))

Thank you

 

[Marclem]

Hi

Can anyone help me please.


Thank you