CODEWITHSUNDEEP
sqlleft-joinself-join
Miguel
Miguel

Reputation: 1

Left joins on the same table with date ranges

I have looked for this solution but none was a good match for my particular problem.

I am looking at help desk data and want to identify those customers that have called in about the same issue within a 28 day period (-14 prior or +14 days after the incident).

The attempt is to calculate resolution rate and first call resolution. I am using one table that I am self joining.

Here is a sample of the data:

CustID | TICKET |Code       |Date
01     | 001    |Benefits   |11/01/2015
01     | 001    |Transport  |11/01/2015
01     | 009    |Benefits   |11/13/2015
01     | 010    |Claims     |11/14/2015
01     | 010    |Transport  |11/30/2015
02     | 010    |Benefits   |11/30/2015

Then end result I want is all the original values and appended to it all the other rows that meet the criteria of the same customer, same issue but only those instances that occurred 14 prior or after the incident date:

CustID | TICKET |Code     |Date      |CustID2 |Ticket2|Code2   |Date2
01     | 001    |Benefits |11/01/2015|01      |009    |Benefits|11/13/2015
01     | 001    |Transport|11/01/2015|NULL    |NULL   |NULL    |NULL
01     | 009    |Benefits |11/13/2015|01      |001    |Benefits|11/01/2015
01     | 010    |Claims   |11/14/2015|NULL    |NULL   |NULL    |NULL
01     | 001    |Transport|11/30/2015|NULL    |NULL   |NULL    |NULL
02     | 010    |Benefits |11/30/2015|NULL    |NULL   |NULL    |NULL

I tried using a left join but the end result is similar to an inner join because of the data range.

SELECT 
T1.CustID, 
T1.TICKET, 
T1.Code, 
T1.Date, 
T2.CustID AS CustID2, 
T2.TICKET As Ticket2, 
T2.Code As Code2, 
T2.Date AS Date2

FROM TABLE AS T1 
LEFT JOIN TABLE AS T2 
ON T1.CustID = T2.CustID AND T1.Code = T2.Code 
WHERE 
T1.TICKET <> T2.TICKET
AND T2.Date Between T1.Date-14 
AND T1.Date+14

I am thinking I might have to do this as two queries but while I can get all the results that match my criteria I cannot get those that do not match.

Upvotes: 0

Views: 4075

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32155

Yep, you're creating implicit INNER JOINs because you're not allowing fields from T2 to be NULL in your WHERE clause, and they will be thanks to the LEFT JOIN.

You can either do this:

SELECT T1.CustID
    ,T1.TICKET
    ,T1.Code
    ,T1.DATE
    ,T2.CustID AS CustID2
    ,T2.TICKET AS Ticket2
    ,T2.Code AS Code2
    ,T2.DATE AS Date2
FROM TABLE AS T1
LEFT JOIN TABLE AS T2
    ON T1.CustID = T2.CustID
        AND T1.Code = T2.Code
        AND T1.TICKET <> T2.TICKET
        AND T2.DATE BETWEEN T1.DATE - 14
            AND T1.DATE + 14

Or this:

SELECT T1.CustID
    ,T1.TICKET
    ,T1.Code
    ,T1.DATE
    ,T2.CustID AS CustID2
    ,T2.TICKET AS Ticket2
    ,T2.Code AS Code2
    ,T2.DATE AS Date2
FROM TABLE AS T1
LEFT JOIN TABLE AS T2
    ON T1.CustID = T2.CustID
        AND T1.Code = T2.Code
WHERE (
        T1.TICKET <> T2.TICKET
        OR (T2.CustID IS NULL AND T2.Code IS NULL)
        )
    AND (
        T2.DATE BETWEEN T1.DATE - 14
            AND T1.DATE + 14
        OR (T2.CustID IS NULL AND T2.Code IS NULL)
        )

Upvotes: 1

Related Questions