Record Plus Prior Record - Single Row [message #678203] |
Fri, 15 November 2019 21:23 |
|
GIL149
Messages: 1 Registered: November 2019
|
Junior Member |
|
|
I have Table1 that contains Cust, Trans_Type, Date...
What I am trying to do is collect all records between a date range...then on the same ROW for each record I want to add two fields that show me the prior Trans_Type and Date to the Transaction pulled...
I have a tried variations but I can't get it to pull the exact previous transaction...it pulls all previous transactions blowing up the report... Any ideas? Sorry if the syntax is not exactly PL SQL, just started with Oracle SQL Developer recently.
SELECT
T1.*,
T2.Trans_Type,
T2.Date
FROM TABLE1 T1
LEFT JOIN TABLE1 T2 ON T2.CUST=T1.CUST AND T2.DATE<T1.DATE
|
|
|
|
|
|
Re: Record Plus Prior Record - Single Row [message #678222 is a reply to message #678212] |
Mon, 18 November 2019 10:00 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
drop table table1;
create table table1 (cust_id number, trans_date date, trans_type varchar2(10), stock_name varchar2(10) );
insert into table1 values (1, SYSDATE-3, 'BUY', 'IBM');
insert into table1 values (1, SYSDATE-2, 'BUY', 'ORACLE');
insert into table1 values (1, SYSDATE-1, 'SELL', 'ORACLE');
insert into table1 values (1, SYSDATE, 'SELL', 'IBM');
commit;
select cust_id, trans_date, trans_type, stock_name,
lag(trans_type) over (order by cust_id, trans_date) as prev_type,
lag(stock_name) over (order by cust_id, trans_date) as prev_stock
from table1
order by cust_id, trans_date;
CUST_ID TRANS_DAT TRANS_TYPE STOCK_NAME PREV_TYPE PREV_STOCK
---------- --------- ---------- ---------- ---------- ----------
1 15-NOV-19 BUY IBM
1 16-NOV-19 BUY ORACLE BUY IBM
1 17-NOV-19 SELL ORACLE BUY ORACLE
1 18-NOV-19 SELL IBM SELL ORACLE
JP
|
|
|