Teradata SQL: Compare values inside group

Question

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.

1) whats order_date ? 2) The condition isn't clear. user 1 has 4 rows. 2 of them have A so your query is row1's startDate <= row4's endDate ? — eran otzap, Jul 27 '15 at 09:50
@eranotzap sorry about the mistake. I have changed it now. Yes, my condition is that for the same `uid` and `event`, if the `start_Date <= end_date` then both should the corresponding `triggered` values should show `1`. — Black, Jul 27 '15 at 09:54
why do you have 2 records for this ? why not update a endtime for the first A and assign a triggerd flag to it ? — eran otzap, Jul 27 '15 at 11:18

score 2 · Accepted Answer · answered Jul 27 '15 at 10:49

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;

Teradata SQL: Compare values inside group

1 Answers1