sqldb2

SQL: Finding out if an event happened before or after another event


I have two tables like this:

CREATE TABLE XX 
(
    name VARCHAR(50),
    date DATE,
    a INT,
    b INT,
    c INT
);

INSERT INTO XX (name, date, a, b, c) 
VALUES 
('john', '2010-11-01', 1, 0, 0),
('john', '2010-10-01', 0, 1, 0),
('sara', '1999-02-01', 1, 0, 0),
('julie', '2015-09-01', 1, 0, 0),
('julie', '2015-09-01', 0, 1, 0);


  name       date a b c
  -----------------------
  john 2010-11-01 1 0 0
  john 2010-10-01 0 1 0
  sara 1999-02-01 1 0 0
 julie 2015-09-01 1 0 0
 julie 2015-09-01 0 1 0

And the other table

CREATE TABLE YY 
(
    name VARCHAR(50),
    yy_date DATE,
    yy CHAR(1)
);

INSERT INTO YY (name, yy_date, yy) 
VALUES 
('john', '2015-01-01', 'A'),
('john', '2016-01-01', 'A'),
('john', '2000-02-01', 'B'),
('john', '2010-03-01', 'C'),
('julie', '2017-09-01', 'A'),
('julie', '2010-09-01', 'B');
('tom', '2010-09-01', 'B');


  name  yy_date     yy
  --------------------
  john  2015-01-01  A
  john  2016-01-01  A
  john  2000-02-01  B
  john  2010-03-01  C
  julie 2017-09-01  A
  julie 2010-09-01  B
  tom   2010-09-01  B

I want to write a SQL queries to answer the following question:

Customers that received a type_yy on a certain date, even though they already had xx of the same type prior to that date

E.g.

The final result should be table YY with an extra column indicating if for each (name, type), whether XX happened before YY or if XX happened after YY.

 name   yy_date yy  xx_vs_yy
 --------------------------------------------
 john   2015-01-01  A XX happened before YY   
john   2016-01-01  A XX happened before YY
 john   2000-02-01  B YY happened before XX
 john   2010-03-01  C        Not applicable
 julie  2017-09-01  A XX happened before YY
 julie  2010-09-01  B YY happened before XX
 tom    2010-09-01  B        Not applicable

Is it possible to do something like this using multiple CTE's, CASE WHEN, UNION ALL and MAX statements?

WITH earliest_xx AS 
(
    SELECT name, MIN(date) as earliest_xx_date
    FROM XX
    GROUP BY name
),
yy_with_xx AS 
(
    SELECT 
        y.name, 
        y.yy_date, 
        y.yy, 
        e.earliest_xx_date
    FROM YY y
    LEFT JOIN earliest_xx e ON y.name = e.name
),
comparison_result AS 
(
    SELECT 
        name, 
        yy_date, 
        yy,
        CASE
            WHEN earliest_xx_date IS NULL THEN 'Not applicable'
            WHEN earliest_xx_date < yy_date THEN 'XX happened before YY'
            ELSE 'YY happened before XX'
        END as xx_vs_yy
    FROM yy_with_xx
)
SELECT *
FROM comparison_result
ORDER BY name, yy_date;

I am feeling a bit lost on this problem...


I think I made some progress on this - something needs to be added to ensure the XX date corresponds to the same types between both XX and YY (eg. a to a, b to b, c to c):

WITH xx_mapped AS (
    SELECT name, date, 
           CASE 
               WHEN a = 1 THEN 'A'
               WHEN b = 1 THEN 'B'
               WHEN c = 1 THEN 'C'
           END AS xx_type
    FROM XX
),
earliest_xx AS (
    SELECT name, xx_type, MIN(date) as earliest_xx_date
    FROM xx_mapped
    GROUP BY name, xx_type
),
yy_with_xx AS (
    SELECT 
        y.name, 
        y.yy_date, 
        y.yy, 
        e.earliest_xx_date
    FROM YY y
    LEFT JOIN earliest_xx e ON y.name = e.name AND y.yy = e.xx_type
),
comparison_result AS (
    SELECT 
        name, 
        yy_date, 
        yy,
        CASE
            WHEN earliest_xx_date IS NULL THEN 'Not applicable'
            WHEN earliest_xx_date < yy_date THEN 'XX happened before YY'
            WHEN earliest_xx_date > yy_date THEN 'YY happened before XX'
            ELSE 'XX and YY happened on the same date'
        END as xx_vs_yy
    FROM yy_with_xx
)
SELECT *
FROM comparison_result
ORDER BY name, yy_date;

Solution

  • A left join should get the result you want:

    select y.name, y.yy_date, y.yy,
      case when x.date is null then 'Not applicable'
           when x.date > y.yy_date then 'YY happened before XX'
           else 'XX happened before YY'
      end as xx_vs_yy
    from yy y
    left join xx x on x.name = y.name and (
      x.a = 1 and y.yy = 'A' or
      x.b = 1 and y.yy = 'B' or
      x.c = 1 and y.yy = 'C');
    

    Result:

    NAME    YY_DATE    YY  XX_VS_YY             
    -----  ----------  --  ---------------------
    john   2015-01-01  A   XX happened before YY
    john   2016-01-01  A   XX happened before YY
    john   2000-02-01  B   YY happened before XX
    john   2010-03-01  C   Not applicable       
    julie  2017-09-01  A   XX happened before YY
    julie  2010-09-01  B   YY happened before XX
    tom    2010-09-01  B   Not applicable       
    

    See running example at db<>fiddle.