CODEWITHSUNDEEP
sqlt-sql
D_Bester
D_Bester

Reputation: 5911

Left Outer Join Using Where to Exclude Records from the Right Table

Using Sql Server 2008. We have a table of jobs done. Normally for any customer, job#1 is followed by job#2 which is followed by job#3. 

CustNum, JobDate, TypeJob
100,   4/10/2019, 2
100,   4/11/2019, 1
100,   4/12/2019, 2
100,   4/13/2019, 3
100,   4/13/2019, 3
222,   4/10/2019, 2
222,   4/11/2019, 1
333,   4/11/2019, 2
444,   3/1/2019,  3
444,   4/10/2019, 1
444,   4/11/2019, 2

I'm looking for all customers which have job#2 with date > job#1 (both existing) but job#3 was not entered afterward.

Here's what I did and it works but it seems clumsy. 

Select
  L.CustNum, 
  L.JobDate1, 
  L.JobDate2,
  R.JobDate3
From
(
    --A<B has JobDate1 followed by JobDate2
    Select 
      First.CustNum as [CustNum], 
      First.JobDate as JobDate1,
      Second.JobDate as JobDate2
    From
    (
        Select [CustNum], Max([JobDate]) as JobDate From tbJobs
        Where [TypeJob] = 1
        Group by CustNum
    ) First
    Join
    (
        Select [CustNum], Max([JobDate]) as JobDate From tbJobs
        Where [TypeJob] = 2
        Group by CustNum
    ) Second
    On First.CustNum = Second.CustNum
    Where Second.JobDate > First.JobDate
) L
Left Outer Join
(
    --A<B #and# C>A has JobDate1 followed by JobDate2 and JobDate3
    Select 
      First.CustNum as [CustNum], 
      --First.JobDate as JobDate1,
      --Second.JobDate as JobDate2,
      Third.JobDate as JobDate3
    From
    (
        Select [CustNum], Max([JobDate]) as JobDate From tbJobs 
        Where [TypeJob] = 1
        Group by CustNum
    ) First
    Join
    (
        Select [CustNum], Max([JobDate]) as JobDate From tbJobs
        Where [TypeJob] = 2
        Group by CustNum
    ) Second
    On First.CustNum = Second.CustNum
    Join
    (
        Select [CustNum], Max([JobDate]) as JobDate From tbJobs
        Where [TypeJob] = 3
        Group by CustNum
    ) Third
    On Second.CustNum = Third.CustNum
    Where Third.JobDate > First.JobDate
    And Second.JobDate > First.JobDate
) R
On First.CustNum = Third.CustNum
Where JobDate3 is null
Order by CustNum

What I really would like to do is something like this:

Select ... From
(Select ...) First
Join
(Select ...) Second
Left Outer Join
(Select ...) Third
On ...
Where Second.JobDate > First.JobDate 
    And (Third.JobDate > First.JobDate) is null

How would I (is it possible) formulate a Where statement so it ignores any Third.JobDate <= First.JobDate and only finds rows where Third.JobDate (greater than First.JobDate) is null?

Using Sql Server 2008.

Upvotes: 1

Views: 89

Answers (2)

Valeriy Tesher
Valeriy Tesher

Reputation: 1

Use common table expressions:

;WITH First AS (
SELECT CustNum, MAX(JobDate) JobDate1
  FROM tbJobs
 WHERE TypeJob = 1
 GROUP BY CustNum
), Second AS (
SELECT CustNum, MAX(JobDate) JobDate2
  FROM tbJobs
 WHERE TypeJob = 2
 GROUP BY CustNum
), Third AS (
SELECT CustNum, MAX(JobDate) JobDate3
  FROM tbJobs
 WHERE TypeJob = 3
 GROUP BY CustNum
)
SELECT f.CustNum, JobDate1, JobDate2, JobDate3
  FROM First f
 INNER JOIN Second s ON f.CustNum = s.CustNum AND f.JobDate1 < s.JobDate2
  LEFT JOIN Third t ON s.CustNum = t.CustNum AND s.JobDate2 < t.JobDate3
 WHERE JobDate3 IS NULL

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

A simple aggregation query should work here:

WITH cte AS (
    SELECT
        CustNum,
        MAX(CASE WHEN TypeJob = 1 THEN JobDate END) AS date1,
        MAX(CASE WHEN TypeJob = 2 THEN JobDate END) AS date2,
        MAX(CASE WHEN TypeJob = 3 THEN JobDate END) AS date3
    FROM tbJobs
    GROUP BY CustNum
)

SELECT CustNum
FROM cte
WHERE
    COALESCE(date2, date1) > COALESCE(date1, date2) AND
    (date3 < date2 OR date3 IS NULL);

The use of COALESCE in the HAVING clause ensures that a customer only passes if he has both the first and second dates present.

Upvotes: 1

Related Questions