centralmike
asked on
teradata sql olap functions
I would like to return the last record in a group and the previous row proceeding the last row. I was wondering if anyone new how to use ranking or partioning to solve this problem in sql statement.
example 1
member date amount
1111 01/14 20.00
1111 02/14 15.00
1111 03/14 50.00
2222 07/13 10.00
2222 01/14 15.00
3333 01/08 20.00
3333 01/09 50.00
3333 01/12 75.00
3333 01/13 80.00
Results
member date amount
1111 02/14 15.00
1111 03/14 50.00
2222 07/13 10.00
2222 01/14 15.00
3333 01/12 75.00
3333 01/13 80.00
example 1
member date amount
1111 01/14 20.00
1111 02/14 15.00
1111 03/14 50.00
2222 07/13 10.00
2222 01/14 15.00
3333 01/08 20.00
3333 01/09 50.00
3333 01/12 75.00
3333 01/13 80.00
Results
member date amount
1111 02/14 15.00
1111 03/14 50.00
2222 07/13 10.00
2222 01/14 15.00
3333 01/12 75.00
3333 01/13 80.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create table test_table (member int, crtdate Date, amount money)
insert into test_table select 1111, '01/01/14', 20.00
insert into test_table select 1111, '02/01/14', 15.00
insert into test_table select 1111, '03/01/14', 50.00
insert into test_table select 2222, '07/01/13', 10.00
insert into test_table select 2222, '01/01/14', 15.00
insert into test_table select 3333, '01/01/08', 20.00
insert into test_table select 3333, '01/01/09', 50.00
insert into test_table select 3333, '01/01/12', 75.00
insert into test_table select 3333, '01/01/13', 80.00
select * from
(
SELECT t2.rn,t2.member,t2.crtdate
FROM (SELECT t1.member,t1.crtdate,t1.am
ROW_NUMBER() OVER (PARTITION BY t1.member ORDER BY t1.crtdate) as rn
FROM test_table t1) t2
) tl
WHERE rn > (select (max(rn) - 2) from
(
SELECT t2.rn,t2.member,t2.crtdate
FROM (SELECT t1.member,t1.crtdate,t1.am
ROW_NUMBER() OVER (PARTITION BY t1.member ORDER BY t1.crtdate) as rn
FROM test_table t1) t2
) b
WHERE member = tl.member GROUP BY member)