Reporting Summary Data 4

[sitadba]

Hi All,

I need to write a summary report that includes only summary information regarding the trouble tickets handled in our support department. I am able to write a query for each separate total I need but I'm not sure how to get it all together on 1 report.

For example, 1 report I need is for 2002 data only, By Engineer, How many tickets were assigned to them, how many tickets they closed, how many tickets they reassigned.

Opened Closed Reassigned

Engineer 1

Engineer 2

Engineer 3

In the database there is a table called 'problem'. In it there is the following columns;

assignee_name (Engineer)
Open_time (Date Opened)
Status
Close_time
Close_by

It's like I want to put a select statement within a select statement. I'm not sure how to go about getting this information combined in 1 report.

I would appreciate any suggestions.
Brgds,
Denise

 

[rac2]

Couple of questions.

What are the values of the status column? I might imagine that status = {closed, reassigned}.

Does a reassigned ticket become a new ticket for another engineer? Should it be counted as a ticket opened for the engineer receiving the reassignment?

Do the counts of closed and re-assigned add up to the counts of opened? In other words do you want to look at the set of tickets opened and for those count how many were closed and re-assigned? The alternative would be to count three different sets of tickets, opened during the year, closed during the year, and re-assigned during the year. These are different because a ticket could be opened on Dec 31, 2001 and closed on Jan 1, 2002. It would not be counted as opened during 2002, but it would be counted as closed. In the other scenario, it would not be counted as opened therefore it would not be counted as closed.


At any rate whatever answers you are looking for, there is one easy solution and that is to join views based on the three queries you have that work.

CREATE VIEW StatsOpened AS
SELECT assignee_name, COUNT(*) AS Opened
. . . whatever works
GROUP BY assignee_name

CREATE VIEW StatsClosed AS
SELECT assignee_name, COUNT(*) AS Closed
. . . whatever works
GROUP BY assignee_name

CREATE VIEW StatsReassigned AS
SELECT assignee_name, COUNT(*) AS Reassigned
. . . whatever works
GROUP BY assignee_name


SELECT a.assignee_name,
a.Opened,
b.Closed,
c.Reassigned
FROM StatsOpened a
LEFT JOIN StatsClosed b ON a.assignee_name=b.assignee_name
LEFT JOIN StatsReassigned c ON a.assignee_name=c.assignee_name


This will give you stats for every engineer with a ticket opened. If you have other engineers and you want to show them you can use the table of engineers as the basic table and join the three stats views to it.

. . .
FROM engineers e
LEFT JOIN StatsOpened a ON e.engineer_name=a.assignee_name
LEFT JOIN StatsClosed b ON e.engineer_name=b.assignee_name
LEFT JOIN StatsReassigned c ON e.engineer_name=c.assignee_name

Does that help?

 

[sitadba]

Thank you very much! This is a big help. I will create a separate view for each query and join them. I didn't realize I could do that. My separate queries are working perfectly so this will be easy.

Many thanks for the direction.

 

[dnoeth]

Probably more efficient than lots of self-joins:
SELECT
assignee_name
,COUNT(CASE WHEN status = 'Opened' THEN 1 END) AS Opened
,COUNT(CASE WHEN status 'Closed' THEN 1 END) AS Closed
...
GROUP BY assignee_name

If your DBMS doesn't support CASE, look for IIF/DECODE...

Dieter

 

[sitadba]

Thank you Dieter.

We are using Oracle. So we can use DECODE. I will try both suggestions and see which way works best for us. Many thanks for your help.

Brgds,
Denise

 

[adam0101]

I use a variation of Dieter's code for this type of thing.

SELECT   assignee_name,
         [Opened]         =SUM(CASE WHEN status='Opened' 
                                    THEN 1
                                    ELSE 0
                                    END),
         [Closed]         =SUM(CASE WHEN status='Closed'
                                    THEN 1
                                    ELSE 0
                                    END)
FROM     problem
GROUP BY assignee_name

Adam

http://www.adventex.com

 

[sitadba]

Thank you so much to all of you! I was able to get the report I needed. Each of the suggestions work. This forum has been really helpful.

Brgds,
Denise