I need to create a report that tracks trouble tickets. Each ticket can have several rows in the database depending on what actions have been taken.
What I need is to have the report only show the 1st action that was taken for each ticket. I do have a date/time field to identify each action.
Any thoughts?
Listserv Archives (BOB member since 2002-06-25)
I need to create a report that tracks trouble tickets. Each ticket can have several rows in the database
depending on what
actions have been taken.
What I need is to have the report only show the 1st
action that was taken
for each ticket. I do have a date/time field to
identify each action.
Steve,
Why not use the MIN function on the date and time?
–
Michael Malone
Senior Consultant
WCI Consulting
Listserv Archives (BOB member since 2002-06-25)
In a message dated 1/7/00 10:16:09 PM Central Standard Time, skrandel@BASECONSULTING.COM writes:
I need to create a report that tracks trouble tickets. Each ticket can have
several rows in the database depending on what actions have been taken.
What I need is to have the report only show the 1st action that was taken for each ticket. I do have a date/time field to identify each action.
Any thoughts?
If you use the Complex Condition wizard, you should be able to do this. What you are looking for is the MIN (First) date activity by trouble ticket. The Complex Condition wizard is somewhere between obvious and confusing but if you read the prompts carefully you should be able to create this query.
The SQL should end up being generated as something like:
select
from
where
and trouble_ticket_date =
(select min(trouble_ticket_date)
from trouble_ticket_table_2
where trouble_ticket_table_1.ticket_id = trouble_ticket_table_2.ticket_id)
… or something like that.
One potential problem: can a trouble ticket have two actions on the same day? If so, you will need date and time to determine the “first” action…
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
One more idea: you can create an alias to your “fact table”, say, “first_actions_facts” and put an additional condition, similar to what Dave suggested, as a self join onto this table:
table.ticket_date_time = (select min(x.trouble_ticket_date)
from trouble_ticket_table x
where table.ticket_id = x.ticket_id)
This only works if your primary key is the ticket_id+date_time, and will give you the first ticket for every ticket_id.
Then define additional objects “first_whatever” based on this table alias.
hope this helps.
Walter
DRathbun@AOL.COM schrieb:
In a message dated 1/7/00 10:16:09 PM Central Standard Time, skrandel@BASECONSULTING.COM writes:
I need to create a report that tracks trouble tickets. Each ticket can have
several rows in the database depending on what actions have been taken.
What I need is to have the report only show the 1st action that was taken for each ticket. I do have a date/time field to identify each action.
Any thoughts?
If you use the Complex Condition wizard, you should be able to do this. What you are looking for is the MIN (First) date activity by trouble ticket. The Complex Condition wizard is somewhere between obvious and confusing but if you read the prompts carefully you should be able to create this query.
The SQL should end up being generated as something like:
select
from
where
and trouble_ticket_date =
(select min(trouble_ticket_date)
from trouble_ticket_table_2
where trouble_ticket_table_1.ticket_id = trouble_ticket_table_2.ticket_id)
… or something like that.
One potential problem: can a trouble ticket have two actions on the same day? If so, you will need date and time to determine the “first” action…
–
Walter Muellner
Delphi Software GmbH / Austria
w.muellner@delphi.at, Tel. +43-1-8151456, http://www.delphi.at
Listserv Archives (BOB member since 2002-06-25)
Hi Steve,
What I need is to have the report only show the 1st action that was taken for each ticket. I do have a date/time field to identify each action.
David has mentioned one solution, but the catch is that a correlated subquery can be quite time consuming. I have a faster alternative if you don’t have too many items to pick up from this first activity and you have the possibility to extend the universe…
Make a class first activity, in this class copy all object you need on the report from the original ‘all activities’ objects. Now adapt them as follows:
substr(min(to_char(activitity.datetime,‘yyyydddhh24mi’)|| ),12)
What the object does is find the minimum datetime/ combination (within whatever group by BO generates due to other objects selected). This will have the first activity old object within the group in it, because datetime is the most significant. The object finally returns only the old object select part.
It doesn’t matter how many objects you select in this way, finding the minimum only requires the main group by execution, and therefore it’s lots faster than a correlated subquery.
Good luck,
Marianne
Listserv Archives (BOB member since 2002-06-25)
If you’re trying to find the minimum action date for each ticket you will need to use a correlated sub-query. A CSQ takes a value from each row in the outer query (THIS ticket) and passes it into an inner query (Minimum Action Date for THIS ticket). CSQs are created using the Complex Calculation Wizard.
=====
Philip Biancucci
Data Warehouse Project Leader
LINC Systems, Corp.
W: 860/715-2053
VM: 781/890-1003 x 7404
EM: pbiancucci@lincsys.com
pbiancucci@yahoo.com
__________________________________________________ Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com
Listserv Archives (BOB member since 2002-06-25)