INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Quarterly Report

Quarterly Report

(OP)
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)

CODE -->

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

RE: Quarterly Report

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.

RE: Quarterly Report

(OP)
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:

CODE -->

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

RE: Quarterly Report

(OP)
Hi

Can anyone help me please.


Thank you

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close