July 27, 2009 at 2:26 am
I am generating a report in MS access using this TSQL statement. My requirement is to generate all the tickets which has breached the respose time of I hour. Response time is taken from an external note to the customer which is in the ON ticket_history table. This note is refered as an “ADDNOTE” and maintained in the ticket_history_type table. By linking ticket , ticket_history table & ticket_history_type table I am able to get the external note the customer. As there are several ADDNOTE to the customer I need to select the MIN(ticket_history].[create_time]) for this specific history_type.
🙂 My requirement is to generate the report using both the query as a single query.
Query to get Minimum Addnote history time:
=====================================
1. SELECT Min(ticket_history.create_time) AS Expr1
FROM (ticket_history INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id) INNER JOIN ticket ON ticket_history.ticket_id = ticket.id
WHERE (((ticket_history_type.name)='AddNote'))
GROUP BY ticket.tn;
2. Query to get the Response breach time
====================================
SELECT ticket.tn, ticket.create_time, users.first_name, ticket_history_type.name, ticket_history.create_time, queue.name, ticket_priority.name, ticket_state.name, ticket.ticket_priority_id, users.last_name
FROM ((((ticket_history INNER JOIN (ticket INNER JOIN users ON ticket.user_id = users.id) ON ticket_history.ticket_id = ticket.id) INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id) INNER JOIN queue ON ticket.queue_id = queue.id) INNER JOIN ticket_priority ON ticket.ticket_priority_id = ticket_priority.id) INNER JOIN ticket_state ON ticket.ticket_state_id = ticket_state.id
WHERE (((ticket_history_type.name)="AddNote") AND ((queue.name)<>"Junk") AND ((ticket_state.name)="New" Or (ticket_state.name)="Open"));
Help me out in this.
Thanks
Cheers,
Got an idea..share it !!
DBA_Vishal
July 27, 2009 at 2:41 am
I think following will be helpful for u :
SELECT
part1.a,part1.b,part2.d
FROM
(
SELECT tbl1.a, tbl2.b
FROM tbl1
INNER JOIN tbl2
ON tbl1.c = tbl2.c
) part1
INNER JOIN
(
SELECT tbl1.a, tbl3.d
FROM tbl3
INNER JOIN tbl1
ON tbl1.c = tbl3.c
) part2
ON part1.a = part2.a
July 27, 2009 at 1:07 pm
Your first query will return a set of "create_time" values, but the SELECT list does not have any identifier by which you would know which ticket had which earliest value. I'd guess that you'll want to add ticket.tn to the SELECT. Also, I find extraneous parentheses distracting and so suggest that you remove those that are not necessary to resolve ambiguity or enclose function parameters.
SELECT Min(ticket_history.create_time) AS Expr1
,ticket.tn as TktNumber
FROM ticket_history
INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id
INNER JOIN ticket ON ticket_history.ticket_id = ticket.id
WHERE ticket_history_type.name='AddNote'
GROUP BY ticket.tn;
The second query shows up in your post with its elements all strung together and also suffers from parenthitis 😉 . Here's my cut at making it more readable, an important point when soliciting help on SSC. SELECT ticket.tn
, ticket.create_time
, users.first_name
, ticket_history_type.name
, ticket_history.create_time
, queue.name
, ticket_priority.name
, ticket_state.name
, ticket.ticket_priority_id
, users.last_name
FROM ticket_history
INNER JOIN ticket ON ticket_history.ticket_id = ticket.id
INNER JOIN users ON ticket.user_id = users.id
INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id
INNER JOIN queue ON ticket.queue_id = queue.id
INNER JOIN ticket_priority ON ticket.ticket_priority_id = ticket_priority.id
INNER JOIN ticket_state ON ticket.ticket_state_id = ticket_state.id
WHERE ticket_history_type.name="AddNote"
AND queue.name"Junk"
-- AND ((ticket_state.name)="New" Or (ticket_state.name)="Open"));
AND ticket_state.name in ("New","Open");
From here, I'm not clear on what you need. Please give a sample of possible data and desired results and what you've tried so far to combine your two queries. This link offers some tips on that topic: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2009 at 10:09 pm
Hi John,
My req. is that I want to display all the tickets which have min addnote time within the one hour of ticket creation. I am trying the query in this way but it fails:
SELECT ticket.tn, ticket.create_time, users.first_name, ticket_history_type.name, ticket_history.create_time, queue.name, ticket_priority.name, ticket_state.name, ticket.ticket_priority_id, users.last_name
FROM ((((ticket_history INNER JOIN (ticket INNER JOIN users ON ticket.user_id = users.id) ON ticket_history.ticket_id = ticket.id) INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id) INNER JOIN queue ON ticket.queue_id = queue.id) INNER JOIN ticket_priority ON ticket.ticket_priority_id = ticket_priority.id) INNER JOIN ticket_state ON ticket.ticket_state_id = ticket_state.id
WHERE (((ticket_history_type.name)="AddNote")
AND ((queue.name)="JUNK")
AND ticket_history.create_time = ( select min(ticket_history.create_time) from ticket_history where ticket_history.ticket_id = ticket.id
Cheers,
Got an idea..share it !!
DBA_Vishal
July 28, 2009 at 1:42 pm
Hi Vishal,
you might want to provide DDL for the table together with some sample data and your expected result. This will help us to help you.
Please see the link in my signature on how to provide sample data.
With reference to your signature: Got sample data .. share it !!
July 28, 2009 at 2:03 pm
Providing enough information is key to getting the help you're requesting. Check out this article http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 28, 2009 at 10:12 pm
Hey Lutz and Drew thanks for the suggestion. 🙂 . I had been trying out this query and finaly it worked :-D. I would have shared the DDL and sample data but as this is a PROD box thought of not sharing it.
SELECT ticket.tn, ticket.create_time, users.first_name, ticket_history_type.name, ticket_history.create_time, queue.name, ticket_priority.name, ticket_state.name, ticket.ticket_priority_id, users.last_name
FROM ((((ticket_history INNER JOIN (ticket INNER JOIN users ON ticket.user_id = users.id) ON ticket_history.ticket_id = ticket.id) INNER JOIN ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id) INNER JOIN queue ON ticket.queue_id = queue.id) INNER JOIN ticket_priority ON ticket.ticket_priority_id = ticket_priority.id) INNER JOIN ticket_state ON ticket.ticket_state_id = ticket_state.id
WHERE (((ticket_history_type.name)="AddNote") AND ((ticket_history.create_time)=((select min(ticket_history.create_time) from ticket_history , ticket_history_type where ticket_history.ticket_id = ticket.id and ticket_history_type.id = ticket_history.history_type_id and ticket_history_type.name ="AddNote"))) AND ((DateDiff("h",[ticket].[create_time],[ticket_history].[create_time]))<1));
Cheers,
Got an idea..share it !!
DBA_Vishal
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply