CODEWITHSUNDEEP
sqlteradata
Black
Black

Reputation: 4644

Teradata SQL: Compare values inside group

I've run into a problem recently and cannot seem to find a nice solution to it using SQL (or otherwise). I have a table like below

uid | event | start_date | end_date
 1      A     23/07/2014    NULL
 1      B     25/07/2014    NULL
 1      C     26/08/2014    NULL
 1      A        NULL      25/07/2014
 2      A     23/07/2014    NULL
 2      C     19/09/2014    NULL
 2      B     13/10/2014    NULL
 2      A        NULL      25/10/2014
 2      B     12/09/2014    NULL

For each user (given by a unique user id, uid), I would like to have another column which has a value of 1 if the same event has been triggered with a start_date less than or equal to the end_date. For the example above we would have the resulting table as

uid | event | start_date | end_date   | triggered
 1      A     23/07/2014    NULL            1
 1      B     25/07/2014    NULL            0
 1      C     26/08/2014    NULL            0
 1      A        NULL      25/07/2014       1
 2      A     28/11/2014    NULL            0
 2      C     19/09/2014    NULL            0    
 2      B     13/10/2014    NULL            1
 2      A        NULL      25/10/2014       0 
 2      B        NULL      15/11/2014       1

because for user 1 the event A has a start_date <= end_date so the corresponding triggered values for that event will be 1. Same for user 2, where the eventB is triggered by the given condition. I have come up with a solution which requires a self join to the table but I'm hoping to do this in a nicer way and learn a few new techniques at the same time. Any help or tips would be appreciated.

Upvotes: 1

Views: 846

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you can do what you want with window functions. If I understand the logic:

select t.*,
       (case when min(start_date) over (partition by uid, event) <
                  max(end_date) over (partition by uid, event)
             then 1 else 0
        end) as triggered
from table t;

Upvotes: 2

Related Questions