SQL statement to return the Min and Max amount of stock per article for a given Month

I have a table from which I am trying to return the quantity per day that the article was in the system.

Example is in table Bestand the are multiple palletes of a different articles that each have a Booking In and Out date; I am try to find out the Min and Max amount of stock that was in the system per article and month.

My thinking is that if I can return the stock quantity for each day and then read out the Min and Max values.

The Timespan would be set at the time of running the SQL and the articles would be fixed.

To find out the quantity for each day I have used the following SQL:

SELECT DISTINCT 
    a.artbez1 AS Artikelbezeichnung, 
    b.artikelnr AS Artikelnummer,
    SUM(CASE WHEN TO_DATE('2019-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') BETWEEN b.neu_datum AND b.aender_datum THEN 1 * b.menge_ist ELSE 0 END) AS "01 Nov 2019"
FROM
    artikel a, bestand b
WHERE 
    b.artikelnr IN ('273632002', .... (huge long list of numbers) ....)
    AND b.artikelnr = a.artikelnr
GROUP BY 
    a.artbez1, b.artikelnr;

This returns for example:

ARTIKELBEZEICHNUNG	ARTIKELNUMMER	01 Nov 2019
SC-4400.CW	220450002	39
S-320.FK120	220502004	0
H-595.FK120	220800004	35
AC-548.FK209	220948032	0
AS-6800.CW	221355002	20

I would like return this for each day of the Month and then from that return the Min and Max Value for each Article

I have the following SQL to return the days of a given Month and was wondering if anyone had any ideas on how they could be combined (If at all possible):

SELECT to_date('01.11.2019','dd.mm.yyyy')+LEVEL-1
FROM dual 
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(to_date('01.11.2019','dd.mm.yyyy')),'DD')

DATES
2019-11-01 00:00:00
2019-11-02 00:00:00
2019-11-03 00:00:00
2019-11-04 00:00:00
2019-11-05 00:00:00
2019-11-06 00:00:00
2019-11-07 00:00:00

The result i am try to get would be something like:

ARTIKELBEZEICHNUNG	ARTIKELNUMMER	Nov 19 Min	Nov 19 Max
SC-4400.CW	220450002	5	39
S-320.FK120	220502004	0	15
H-595.FK120	220800004	2	35
AC-548.FK209	220948032	0	0
AS-6800.CW	221355002	10	20

Is this at all possible in SQL?

Thanks for taking the time to read my post.

JeRi

Solution

You can use a partitioned outer join:

WITH calendar ( day ) AS (
  SELECT DATE '2019-11-01'
  FROM   DUAL
UNION ALL
  SELECT day + INTERVAL '1' DAY
  FROM   calendar
  WHERE  day < LAST_DAY( DATE '2019-11-01' )
),
daily_totals ( artbez1, Artikelnr, Day, total_menge_ist ) AS (
  SELECT MAX( ab.artbez1 ), 
         ab.artikelnr,
         c.day,
         COALESCE( SUM( ab.menge_ist ), 0 )
  FROM   calendar c
         LEFT OUTER JOIN 
         ( SELECT a.artikelnr,
                  a.artbez1,
                  b.neu_datum,
                  b.aender_datum,
                  b.menge_ist
           FROM   artikel a
                  LEFT JOIN bestand b
                  ON ( a.artikelnr = b.artikelnr )
           -- WHERE  b.artikelnr IN ('273632002', .... (huge long list of numbers) ....)
         ) ab
         PARTITION BY ( ab.artikelnr, ab.artbez1 )
         ON ( c.day BETWEEN ab.neu_datum AND ab.aender_datum )
  GROUP BY ab.artikelnr, c.day
)
SELECT MAX( artbez1 ) AS Artikelbezeichnung,
       artikelnr AS Artikelnummer,
       TRUNC( day, 'MM' ) AS month,
       MIN( total_menge_ist ) AS min_total_menge_ist,
       MAX( total_menge_ist ) AS max_total_menge_ist
FROM   daily_totals
GROUP BY artikelnr, TRUNC( day, 'MM' );

Which, for the sample data:

CREATE TABLE artikel ( artikelnr, artbez1 ) AS
SELECT 220450002, 'SC-4400.CW'   FROM DUAL UNION ALL
SELECT 220502004, 'S-320.FK120'  FROM DUAL UNION ALL
SELECT 220800004, 'H-595.FK120'  FROM DUAL UNION ALL
SELECT 220948032, 'AC-548.FK209' FROM DUAL UNION ALL
SELECT 221355002, 'AS-6800.CW'   FROM DUAL;

CREATE TABLE bestand ( artikelnr, neu_datum, aender_datum, menge_ist ) AS
SELECT 220450002, DATE '2019-10-30', DATE '2019-11-01', 20 FROM DUAL UNION ALL
SELECT 220450002, DATE '2019-11-01', DATE '2019-11-05', 19 FROM DUAL UNION ALL
SELECT 220502004, DATE '2019-11-05', DATE '2019-11-03',  5 FROM DUAL UNION ALL
SELECT 220800004, DATE '2019-11-01', DATE '2019-11-15', 35 FROM DUAL UNION ALL
SELECT 221355002, DATE '2019-10-20', DATE '2019-11-05',  5 FROM DUAL UNION ALL
SELECT 221355002, DATE '2019-10-25', DATE '2019-11-10',  5 FROM DUAL UNION ALL
SELECT 221355002, DATE '2019-10-28', DATE '2019-11-13',  5 FROM DUAL UNION ALL
SELECT 221355002, DATE '2019-10-30', DATE '2019-11-15',  5 FROM DUAL UNION ALL
SELECT 221355002, DATE '2019-11-05', DATE '2019-11-20',  5 FROM DUAL;

Outputs:

ARTIKELBEZEICHNUNG | ARTIKELNUMMER | MONTH               | MIN_TOTAL_MENGE_IST | MAX_TOTAL_MENGE_IST
:----------------- | ------------: | :------------------ | ------------------: | ------------------:
SC-4400.CW         |     220450002 | 2019-11-01 00:00:00 |                   0 |                  39
S-320.FK120        |     220502004 | 2019-11-01 00:00:00 |                   0 |                   0
AC-548.FK209       |     220948032 | 2019-11-01 00:00:00 |                   0 |                   0
H-595.FK120        |     220800004 | 2019-11-01 00:00:00 |                   0 |                  35
AS-6800.CW         |     221355002 | 2019-11-01 00:00:00 |                   0 |                  25

db<>fiddle here