Sum of qty between two date ranges-CodePudding

I have two tables- Tab1 and Tab2.

Tab1 contains
Item and Dates

|Item|Dates     |
|I1  |06-30-2022|
|I1  |07-02-2022|
|I1  |07-05-2022|

Tab2 contains Item ,Qty and ItemAvailDate

   |Item|Qty|ItemAvailDate|
   |I1  |10 |06-30-2022|
   |I1  |20 |07-01-2022|
   |I1  |40 |07-02-2022|
   |I1  |30 |07-03-2022|
   |I1  |40 |07-04-2022|
   |I1  |50 |07-05-2022|

I want the quantities from Tab2 to be summed up in the range of the dates in Tab1, i.e,

|Item|Dates     |Total(Qty)|
|I1  |06-30-2022|30        |
|I1  |07-02-2022|110       |
|I1  |07-05-2022|50        |

Row 1 has sum of qty from first date in Tabl - 06-30-2022 till next date in Tab1 - 07-02-2022(excluding 07-02)
Row 2 has sum of qty from 07-02-2022 till 07-05-2022(excluding 07-05)
Row3 has sum of qty of 07-05-2022 as no other rows after this

All qty of Tab2 from the first date in Tab1 should be summed up till the next date in Tab1(excluding that date).
The dates should be partitioned by the items available in Tab1

I do not want to use Loops. Any easy logic using Analytical function of oracle ?
Thanks in Advance!

CodePudding user response：

Use LEAD and a correlated sub-query:

WITH next_dates (item, dates, next_date) AS (
  SELECT item,
         dates,
         LEAD(dates, 1, dates   1) OVER (PARTITION BY item ORDER BY dates)
  FROM   tab1
)
SELECT item,
       dates,
       (
         SELECT SUM(qty)
         FROM   tab2 t2
         WHERE  n.item = t2.item
         AND    n.dates <= t2.itemavaildate
         AND    t2.itemavaildate < n.next_date
       ) AS total
FROM   next_dates n

Which, for the sample data:

CREATE TABLE Tab1 (Item, Dates) AS
SELECT 'I1', DATE '2022-06-30' FROM DUAL UNION ALL
SELECT 'I1', DATE '2022-07-02' FROM DUAL UNION ALL
SELECT 'I1', DATE '2022-07-05' FROM DUAL;

CREATE TABLE Tab2 (Item, Qty, ItemAvailDate) AS
SELECT 'I1', 10, DATE '2022-06-30' FROM DUAL UNION ALL
SELECT 'I1', 20, DATE '2022-07-01' FROM DUAL UNION ALL
SELECT 'I1', 40, DATE '2022-07-02' FROM DUAL UNION ALL
SELECT 'I1', 30, DATE '2022-07-03' FROM DUAL UNION ALL
SELECT 'I1', 40, DATE '2022-07-04' FROM DUAL UNION ALL
SELECT 'I1', 50, DATE '2022-07-05' FROM DUAL;

Outputs:

ITEM DATES TOTAL

I1 30-JUN-22 30

I1 02-JUL-22 110

I1 05-JUL-22 50

ITEM	DATES	TOTAL
I1	30-JUN-22	30
I1	02-JUL-22	110
I1	05-JUL-22	50

db<>fiddle here

CodePudding user response：

In Oracle 12.1 and later, match_recognize can do quick work of such assignments. First UNION ALL the rows from both tables, assigning null quantity for rows from table 1, and keeping track of which table the rows come from in an additional table tbl. Then everything is ready for a trivial application of match_recognize:

select item, dates, total_qty
from   (
         select item, null as qty, dates, 1 as tbl from tab1
         union all
         select item, qty, itemavaildate, 2 as tbl from tab2
       )
match_recognize (
  partition by item
  order     by dates, tbl
  measures  t1.dates as dates, sum(qty) as total_qty
  pattern   ( t1 t2*)
  define    t1 as tbl = 1, t2 as tbl = 2
);