Tableau/SQL Calculated Field With Grouping-CodePudding

I have a table with the following structure

id, event_name, event_date
| 1 | a | 1.1.2020 | 
| 2 | b | 3.2.2020 | 
| 3 | b | 3.2.2020 |
| 3 | b | 5.2.2020| 
| 1 | b | 31.12.2019 | 
| 2 | a | 5.1.2020 |

My goal would be to perform a grouping on the id and then I'd have to check wheter the date of an event 'a' comes before an event 'b'. If so I'd like to output 'ok' and 'error' elsewise.

In this example this would result to

id, check
| 1 | error| 
| 2 | ok |  
| 3 | ok |

Would it be possible to perform the task with a calculated field in Tableau? SQL would be also be ok!

CodePudding user response：

Try this

   Select id, case when diff<0 then 'ok' 
   else 'error' end as status from 
   (
    Select id,

    max(case when event_name ='a' then event_date end) - 
    max(case when event_name='b' then event_date end)
   As diff
    From table group by id order by id)

CodePudding user response：

You can use this query with UNION clause:

select id, 'Error' "check" from mydata md where event_name='a' and id in 
(select id from mydata where id=md.id and md.event_name<>event_name 
and md.event_date > event_date) 
union
select id, 'Ok' "check" from mydata md where event_name='a' and id in 
(select id from mydata where id=md.id and md.event_name<>event_name 
and md.event_date < event_date);

Output should be :

| ID | 'ERROR' |
|----|---------|
|  1 |   Error |
|  2 |      Ok |

ID=3 doesn't appear, because event_name both are 'b'.