subquery calculate days between dates

Question

Sub query, SQL, Oracle

I'm new to sub queries and hoping to get some assistance. My thought was the sub query would run first and then the outer query would execute based on the sub query filter of trans_code = 'ABC'. The query works but it pulls all dates from all transaction codes, trans_code 'ABC' and 'DEF' ect.

The end goal is to calculate the number of days between dates.

The table structure is:

acct_num    effective_date
1234        01/01/2020
1234        02/01/2020
1234        03/01/2020
1234        04/01/2021

I want to execute a query to look like this:

account     Effective_Date  Effective_Date_2   Days_Diff
1234        01/01/2020      02/01/2020         31  
1234        02/01/2020      03/01/2020         29
1234        03/01/2020      04/01/2021         395
1234        04/01/2021                         0

Query:

SELECT t3.acct_num,
       t3.trans_code,
       t3.effective_date,
       MIN (t2.effective_date) AS effective_date2,
       MIN (t2.effective_date) - t3.effective_date AS days_diff
FROM (SELECT t1.acct_num, t1.trans_code, t1.effective_date
      FROM lawd.trans t1
      WHERE t1.trans_code = 'ABC') t3
     LEFT JOIN lawd.trans t2 ON t3.acct_num = t2.acct_num
WHERE t3.acct_num = '1234' AND t2.effective_date > t3.effective_date
GROUP BY t3.acct_num, t3.effective_date, t3.trans_code
ORDER BY t3.effective_date asc

TIA!

Answer 1

Use lead():

select t.*,
       lead(effective_date) over (partition by acct_num order by effect_date) as next_efffective_date,
       (lead(effective_date) - effective_date) as diff
from lawd.trans t