Post reply

How to find all customers that has 2 different tickets in order

  • May 20, 2011 at 3:19 am

    #389559

    I have a table of customers (CustId , Name , Phone,City ) and table of tickets (TicketId,CustId,Subject, ticketDate) . I would like to get list of all customers (Name,City,Phone,Subject,TickeDate) that have a ticket with subject="info" first and another ticket with subject="problem" later (the ticketdate of the "Info" is earlier then the ticketdate of the"problem").

    I created a view customersandTickets : select name,City,Phone ,t.subject,t.ticketdate from customers Left outer join tickets as t on custid=t.custid

    Then I tried : select ct.name,ct.City,ct.Phone ,ct.subject,ct.ticketdate from customersandTickets as ct where (subject="info" and custid IN (Select custid from tickets as tik where (subject="problem" and (ct.ticketdate<tik.ticketdate)))

    I got 188 rows.

    To validate this I tried the other way (problem first and later then info):

    select ct.name,ct.City,ct.Phone ,ct.subject,ct.ticketdate from customersandTickets as ct where (subject="probekm" and custid IN (Select custid from tickets as tik where (subject="info" and (ct.ticketdate>tik.ticketdate)))

    I got 192 rows. So what the different ?

    In any case I wanted to have the results :

    Name,City,Phone,Subject,TickeDate

    David , NY ,123 , Info , 01.01.2010

    David , NY ,123 , Problem , 02.02.2010

    How do I get this information in the right way ?

  • May 20, 2011 at 4:35 am

    #1327545

    SELECT *

    FROM customers c

    INNER JOIN tickets t1 ON t1.CustId = c.CustId AND t1.[subject] = 'info'

    INNER JOIN tickets t2 ON t2.CustId = c.CustId AND t2.[subject] = 'problem'

    WHERE t2.ticketDate > t1.ticketDate

    This query will return customers who have both an 'info' and a 'problem' where the 'problem' came after the 'info'. TBH there's a multitude of ways of getting the results you want depending upon the distribution of the data. You could process the above query to get two rows per customer, or you could write the query differently, say by preaggregating the tickets table:

    SELECT CustId

    FROM tickets

    WHERE [subject] IN ('info','problem')

    GROUP BY CustId,[subject]

    HAVING COUNT(*) = 2

    If you can provide some sample data, you will get a tested solution.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • May 22, 2011 at 1:09 am

    #1328160

    Hi Chris ,

    Thanks for the sample SQL code.

    I'll be by the customer site and will test both approaches. sample data is a problem due to the nature of tables content.

    TIA

    Shai

  • May 22, 2011 at 12:21 pm

    #1328224

    Shai Bar-Lev (5/22/2011)

    Hi Chris ,

    Thanks for the sample SQL code.

    I'll be by the customer site and will test both approaches. sample data is a problem due to the nature of tables content.

    TIA

    Shai

    So make some up! 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

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