CODEWITHSUNDEEP
sqloracle-database
sleven
sleven

Reputation: 107

Return value in a table based on nearest date in another SQL

I apologize if the title is ambiguous, but I couldn't think of a better way to distill down my objective.

I have two tables: AcctProdHist ph and AcctBalHist bh that I have aliased ph and bh, respectively. AcctProdHist has the product history for an account and its productdate. AcctBalHist contains a history of the account balance by date balance changed balancedate.

I am trying to find the balance in AcctBalHist for the balancedate nearest the productdate in AcctProdHist. There are two situations:

Here is my code for combining both tables and getting a full balance history:

select
ph.acctnbr, 
ph.product,
ph.productdate,
bh.balancedate,
bh.balance

from AcctProdHist ph, AcctBalHist bh

where ((ph.acctnbr = bh.acctnbr(+)))

and ph.acctnbr in (12345,67890)

Here are the results:

ACCTNBR Product ProductDate BalanceDate Balance
12345   BYBU    7/16/2018   8/1/2018    550
12345   BYBU    7/16/2018   7/31/2018   510
12345   BYBU    7/16/2018   7/12/2018   500
12345   BYBU    7/16/2018   7/11/2018   460
12345   BYBU    7/16/2018   7/2/2018    450
67890   ABAU    7/20/2018   8/5/2018    103
67890   ABAU    7/20/2018   8/1/2018    102
67890   ABAU    7/20/2018   7/31/2018   101
67890   ABAU    7/20/2018   7/22/2018   100

In this case, the Balance I want for ACCTNBR 12345 is 500. The Balance I want for ACCTNBR 67890 is 100.

I'm guessing i'll have to do a UNION query as i'm working with two different situations, but I don't even know where to start.

Thanks!

EDIT: Adding Sample Data from the individual tables.

AcctProdHist

ACCTNBR Product ProductDate InactiveDate
12345   ATRU    5/1/2016    7/16/2018
12345   BYBU    7/16/2018   
67890   ABAU    7/20/2018

AcctBalHist

ACCTNBR BalanceDate Balance
12345   1/1/2018    225
12345   2/6/2018    268
12345   4/20/2018   315
12345   6/1/2018    400
12345   7/2/2018    450
12345   7/11/2018   460
12345   7/12/2018   500
12345   7/31/2018   510
12345   8/1/2018    550
67890   7/22/2018   100
67890   7/31/2018   101
67890   8/1/2018    102

Upvotes: 1

Views: 187

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You must rank your rows. Use ROW_NUMBER for this, giving the closest balance record #1:

select
  acctnbr, 
  product,
  productdate,
  balancedate,
  balance,
from
(
  select
    ph.acctnbr, 
    ph.product,
    ph.productdate,
    bh.balancedate,
    bh.balance,
    row_number() over (partition by ph.acctnbr
                       order by case when ph.productdate >= bh.balancedate then 1 else 2 end,
                                abs(ph.productdate - bh.balancedate)
                      ) as rn
  from AcctProdHist ph
  left join AcctBalHist bh on bh.acctnbr = ph.acctnbr
  where ph.acctnbr in (12345,67890)
)
where rn = 1
order by acctnbr;

Upvotes: 1

Related Questions