Lead and Lag in Teradata Query - For category

Question

I am writing a query to get data as SCD 2 type from a data dump. My data and code are as follows:

create table promotions 
(
start_date date,
end_date date,
promotion_name varchar(50));

Insert statements to populate the table:

insert into promotions values ('9/1/2017','9/2/2017','P1');
insert into promotions values ('9/2/2017','9/3/2017','P1');
insert into promotions values ('9/3/2017','9/4/2017','P1');
insert into promotions values ('9/4/2017','9/5/2017','P1');
insert into promotions values ('9/5/2017','9/6/2017','P2');
insert into promotions values ('9/6/2017','9/7/2017','P2');
insert into promotions values ('9/7/2017','9/8/2017','P2');
insert into promotions values ('9/8/2017','9/9/2017','P2');
insert into promotions values ('9/9/2017','9/10/2017','P2');
insert into promotions values ('9/10/2017','9/11/2017','P2');
insert into promotions values ('9/11/2017','9/12/2017','P3');
insert into promotions values ('9/12/2017','9/13/2017','P3');
insert into promotions values ('9/13/2017','9/14/2017','P3');
insert into promotions values ('9/14/2017','9/15/2017','P3');

Expected Result:

Date_Start      Date_End        Promotion Name
9/1/2017        9/4/2017        P1
9/5/2017        9/10/2017       P2
9/11/2017       9/13/2017       P3

Query I have Written:

with cte as (select rank() over (partition by promotion_name order by start_date asc) as "Rank"
,start_date
,dateadd(day,-1,start_date) as EndDate
,promotion_name
--first_name, last_name
from dbo.promotions)
select * from cte where rank=1;

Output of Query

start_date  EndDate promotion_name
2017-09-01  2017-08-31  P1
2017-09-05  2017-09-04  P2
2017-09-11  2017-09-10  P3

Issue with the above query is that EndDate is getting displayed in the Wrong way, when compared to the output table above.

On SQL server lead and lag functions solve this issue, but On TERADATA im not able to get the equivalent for lead /lag function.

How should I go about it. I dont want to create any volatile / Temp tables, its just a plain query for ETL.

score 4 · Answer 1 · answered Sep 16 '17 at 18:32

LAG and LEAD are just shorter syntax, you can rewrite it like this:

LAG(col1, n) OVER (PARTITION BY ... ORDER BY col2)
=
MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                ROWS BETWEEN n PRECEDING AND n PRECEDING), 0)

LEAD(col1, n) OVER (PARTITION BY ... ORDER BY col2)
=
MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                ROWS BETWEEN n FOLLOWING AND n FOLLOWING), 0)

To get a default value simply use COALESCE:

LAG(col1, n, default) OVER (PARTITION BY ... ORDER BY col2)
=
COALESCE(MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                         ROWS BETWEEN n PRECEDING AND n PRECEDING), 0)
        ,default)

Lead and Lag in Teradata Query - For category

1 Answers1