CODEWITHSUNDEEP
sqlgroup-byteradata
Romero Azzalini
Romero Azzalini

Reputation: 304

SQL Grouping with Additional Checks

I have a table with the following structure

id event_name event_date
1 a 1.1.2019
1 b 2.1.2019
1 c 2.3.2019
2 a 3.2.2020
2 b 5.1.2020
2 c 4.2.2020
3 a 1.2.2020
3 b 5.2.2020
3 c 3.2.2020
4 a 3.2.2020
4 c 4.2.2020

I'd like to group on the id, afterwards I have to check if the order of event_names matches the order of event_date like event_date for 'a' < event_date for 'b' < event_date for 'c'. Additionally I have to check, whether the event 'b' is in the table (if so the order doesn't matter anymore). This would result in a column 'event_check' with following values:

In this example this would result to

id event_check
1 ok
2 too early
3 too late
4 missing b

Upvotes: 0

Views: 52

Answers (1)

id&#39;7238
id&#39;7238

Reputation: 2593

Try the following SQL statement, it should work on most DBMS.

SELECT
  id,
  CASE 
    WHEN event_date_a < event_date_b AND event_date_b < event_date_c THEN 'ok'
    WHEN event_date_b < event_date_a THEN 'too early'
    WHEN event_date_c < event_date_b THEN 'too late'
    WHEN event_date_b IS NULL THEN 'missing b'
  END AS event_check
FROM (
  SELECT 
    id,
    MIN(CASE WHEN event_name = 'a' THEN event_date END) AS event_date_a,
    MIN(CASE WHEN event_name = 'b' THEN event_date END) AS event_date_b,
    MIN(CASE WHEN event_name = 'c' THEN event_date END) AS event_date_c
  FROM mytable
  GROUP BY id
) agg

Upvotes: 2

Related Questions