Help with date difference query

Question

Post reply

Help with date difference query

phurwitz

SSC-Addicted

Points: 456
More actions
November 3, 2006 at 11:54 am

#192995

I am trying to write a query on my company's helpdesk ticketing system.
My primary table (hd_ticket) has a createdate field for the ticket.
I have a communications table (hd_communicationlist) that lists all the communication b/w us and our client with a createdate field that is a time/date stamp.
I'd like to write a query that gives me a list of all tickets where the first createdate on the communications list is greater than or equal to 24 hours.
Here is the query I've written just to get the data, without the date part:
select hd_ticket.pk_ticket,
hd_ticket.company,
hd_ticket.createdate,
hd_communicationlist.pk_communicationlist,
hd_communicationlist.fk_ticket,
hd_communicationlist.createdate,
hd_communicationlist.fk_communicationtype,
hd_communicationlist.content
from hd_ticket inner join hd_communicationlist on hd_ticket.pk_ticket=hd_communicationlist.fk_ticket
where pk_ticket ='34950'
any help on how to accomplish this would be very much appreciated...
Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

SELECT t.pk_ticket, t.company, t.createdate

FROM hd_ticket t

INNER JOIN (SELECT fk.ticket, MIN (createdate) firstCreateDate

FROM hd_communicationlist

GROUP BY fk.ticket) c

ON t.pk_ticket = c.ticket

WHERE t.pk_ticket = '34950' AND DATEADD(hour, 24, c.firstCreateDate) >= c.firstCreateDate

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Loner, I think there is a slight logic error in your SQL. Shouldn't the where clause look like this:

WHERE t.pk_ticket = '34950' and DATEDIFF(hour, t.createdate, c.createdate) > 24

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

If I may offer a suggestion to Loner's query and Lynn's correction:

DECLARE @dtCheck datetime
SET @dtCheck = DATEADD(hour, -24, GetDate())

SELECT t.pk_ticket, t.company, t.createdate
FROM hd_ticket t
    INNER JOIN (SELECT fk.ticket, MIN (createdate) firstCreateDate
                FROM hd_communicationlist
                WHERE createdate < @dtCheck
                GROUP BY fk.ticket) c
        ON t.pk_ticket = c.ticket
WHERE t.pk_ticket = '34950'

This will allow the dateadd function to run only once for the query, instead of on each row of the table, eliminating a table scan. Also, by doing the check in the subquery may help to reduce intermediate sets and provide quicker query (though testing would be advised).

In general, if possible, you want to avoid functions on table values, in where clauses, as it almost always forces table scans to evaluate the function on all rows. If possible, setup the function on the value to compare, rather than the table column.

Hope this helps.

Mark

Vladan SSC-Insane Points: 22072 More actions · Answer 4

It seems to me that there is some confusion about what the 24 hours should be... Paul, could you explain that once again?

"where the first createdate on the communications list is greater than or equal to 24 hours" is a bit unclear. 24 hours (or more) before GETDATE() or 24 hours (or more) after create_date in table hd_ticket?

In other words, are you trying to find rows that are older than 24 hours, or are you trying to find rows where it took more than 24 hours from creation in primary table until communication was created?

I suppose it is the latter, but Mark's solution works with 24 hours from GETDATE().

phurwitz SSC-Addicted Points: 456 More actions · Answer 5

Hi All,

Thanks for the great suggestions and I am going to try this morning.

The main purpose of my query is to find tickets where it took my team longer than 24 hours to make first contact with the customer. So the hd_ticket is the primary ticketing system table and the hd_communicationlist is where all the contact with the customer is recorded. so if the first record in the communication list with the earliest createdate is 24 hours or more after the createdate in the hd_ticketlist, I know it took longer than 24 hours to contact a customer.

Thanks!