SQL - Combine Two rows in to One

UDA
Teradata Employee

SQL - Combine Two rows in to One

I need sql help on below case

i have two rows against each each product subscription. I.e One if you subscribe for product then one is for Activation (A) and once you cancel your subscriber... will be having one row for Cancellation(C)/Decactivation.

Sample Data

UserID Status ProductID Event Date

1 A 1001 01-01-2009

1 C 1001 02-02-2009

2 A 4001 03-01-2009

3 C 1561 01-01-2009

4 A 1562 01-01-2009

4 C 1562 05-01-2009

I want to show output like below

UserID ProductID Start Date End Date

1 1001 01-01-2009 02-02-2009

2 4001 03-01-2009 NULL

3 1561 NULL 01-01-2009

4 1562 01-01-2009 05-01-2009

Sample table and data

database rad_edw;

CREATE TABLE TEST_SHAMI ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

t_id INTEGER,

t_date TIMESTAMP(0),

t_event VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

prod_id INTEGER

)

PRIMARY INDEX ( t_id );

insert into TEST_SHAMI

values (1, cast('2009-07-01 00:00:00' as timestamp(0)), 'ACT',1001);

insert into TEST_SHAMI

values (1, cast('2009-08-01 00:00:00' as timestamp(0)), 'DEACT',1001);

insert into TEST_SHAMI

values (2, cast('2009-07-17 00:00:00' as timestamp(0)), 'DEACT',1003);

insert into TEST_SHAMI

values (3, cast('2009-07-01 00:00:00' as timestamp(0)), 'ACT',1009);

Can you please hlep me on how i can get the above in SQL statement....!
4 REPLIES
Senior Apprentice

Re: SQL - Combine Two rows in to One

Is the combination UserID/Status/ProductID unique?

Then it's a Full Outer Join:

SELECT
COALESCE(t1.t_id,t2.t_id),
COALESCE(t1.prod_id,t2.prod_id),
t1.t_date AS start_date,
t2.t_date AS end_date
FROM
(SELECT * FROM test_shami WHERE t_event = 'ACT') AS t1
FULL OUTER JOIN
(SELECT * FROM test_shami WHERE t_event = 'DEACT') AS t2
ON t1.t_id = t2.t_id
AND t1.prod_id = t2.prod_id;

Dieter
Teradata Employee

Re: SQL - Combine Two rows in to One

Its not always unique.....Because one can subscriber to a service and cancel it in one day many times. So we can have multiple rows against the one user for same product activation and cancel.
Senior Apprentice

Re: SQL - Combine Two rows in to One

Then how do you distinguish between those rows?
Looks like removing the duplicates within the Derived Tables using DISTINCT or GROUP BY might work.

Dieter
Teradata Employee

Re: SQL - Combine Two rows in to One

Its not the duplicates because Event time will be different. A mobile user subscriber a service for today and cancel it after few hours and then do subscribe for a service again tomorrow ... so we are having 3 rows with different timestamps....

You can now build two records One with service subscriber start and end date... And when with start date but null in END Date.