Home » SQL & PL/SQL » SQL & PL/SQL » Record Plus Prior Record - Single Row
Record Plus Prior Record - Single Row [message #678203] Fri, 15 November 2019 21:23 Go to next message
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 #678204 is a reply to message #678203] Fri, 15 November 2019 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Record Plus Prior Record - Single Row [message #678205 is a reply to message #678203] Sat, 16 November 2019 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Have a look at LAG function.

Re: Record Plus Prior Record - Single Row [message #678212 is a reply to message #678203] Sun, 17 November 2019 06:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
RTFM analytic function LAG.

SY>
Re: Record Plus Prior Record - Single Row [message #678222 is a reply to message #678212] Mon, 18 November 2019 10:00 Go to previous message
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
Previous Topic: Query performance condundrum.
Next Topic: question for reverse
Goto Forum:
  


Current Time: Thu Mar 28 04:51:13 CDT 2024