Table Joins 101

[Justin R. Grenier]

I think this is a dummy question, but I'm clueless, so here goes...

Let's say I have a "Tickets" table that stores Help Desk tickets. It might
look something like the following:

TicketID OpenDate CloseDate TicketDesc Resolution
1 03/01/2005 03/01/2005 dafdffsd addgfgfsdg
2 03/02/2005 03/06/2005 dsahsfg fsdghsffgsfg
3 03/02/2005 <null> dfhfsgfs adfdfdfsgfh

....and a "Contacts" table that stores correspondence regarding Help Desk
Tickets. It might look something like the following:

TicketID ContactDate ContactType Contact_Desc
2 03/04/2005 E-Mail adfdgdgh
2 03/05/2005 Phone fhjhfjf
3 03/07/2005 Phone vnmhgjt
3 03/07/2005 Phone wrtfghjgd
3 03/09/2005 Voice Mail dsafyry

I want to know the percentage of tickets have NEITHER been closed NOR
contacted within one day of the OpenDate. What might my query and/or
reporting strategy look like? I am using Crystal Reports and either Access
or SQL Server. Thanks in advance for your help!


--Justin--

 

[John Vinson]

I think this is a dummy question, but I'm clueless, so here goes...

Let's say I have a "Tickets" table that stores Help Desk tickets. It might
look something like the following:

TicketID OpenDate CloseDate TicketDesc Resolution
1 03/01/2005 03/01/2005 dafdffsd addgfgfsdg
2 03/02/2005 03/06/2005 dsahsfg fsdghsffgsfg
3 03/02/2005 <null> dfhfsgfs adfdfdfsgfh

...and a "Contacts" table that stores correspondence regarding Help Desk
Tickets. It might look something like the following:

TicketID ContactDate ContactType Contact_Desc
2 03/04/2005 E-Mail adfdgdgh
2 03/05/2005 Phone fhjhfjf
3 03/07/2005 Phone vnmhgjt
3 03/07/2005 Phone wrtfghjgd
3 03/09/2005 Voice Mail dsafyry

I want to know the percentage of tickets have NEITHER been closed NOR
contacted within one day of the OpenDate. What might my query and/or
reporting strategy look like? I am using Crystal Reports and either Access
or SQL Server. Thanks in advance for your help!

You'll need a query with several OR criteria:

SELECT Tickets.*
FROM Tickets LEFT JOIN Contacts
ON Contacts.TicketID = Tickets.TicketID
WHERE CloseDate IS NULL
OR Contacts.TicketID IS NULL
OR NOT EXISTS(SELECT TicketID FROM Contacts AS X
WHERE X.TicketID = Tickets.TicketID
AND X.ContactDate < DateAdd("d", 1, Tickets.OpenDate);

The first criteria finds unclosed tickets; the second finds tickets
that have never had any contacts at all; and the third finds tickets
that have had contacts but none within the first day.


John W. Vinson[MVP]

 

[Justin R. Grenier]

OK, let's add a level of complication by presuming that I want to calculate
business days instead of calendar days, excluding both holidays and
weekends. I understand that I need to use the reporting tool to do most of
the footwork on this, and I have a couple of formulas that I think will
work, however...

It would seem that I need to bring into the report the following fields:
TicketID, OpenDate, CloseDate, and the EARLIEST ContactDate for a given
ticket. ...doable? If so, how?

 

[John Vinson]

OK, let's add a level of complication by presuming that I want to calculate
business days instead of calendar days, excluding both holidays and
weekends. I understand that I need to use the reporting tool to do most of
the footwork on this, and I have a couple of formulas that I think will
work, however...

It would seem that I need to bring into the report the following fields:
TicketID, OpenDate, CloseDate, and the EARLIEST ContactDate for a given
ticket. ...doable? If so, how?

One way is to create a Query based on the Contacts table. Make it a
Totals query (by clicking the Greek Sigma icon); Group By the TicketID
and select Min of ContactDate. This query will give the earliest
contact for each ticket.

Then join this query rather than your Contacts table in the query I
suggested.

Weekends and holidays are a bit tricker... you'll need a table of
holidays, or (flipside) a table of working days.


John W. Vinson[MVP]