clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432229 distinct fiddles created so far.

CREATE TABLE INDIVID (PAANUM INT, PAWHLO VARCHAR(10), PAANTX INT); INSERT INTO INDIVID VALUES (1, 'h550', 100),(2, 'h560', 300),(3, 'h550', 50); CREATE TABLE PORDHV (P1MTRL INT, P1STAT CHAR(1), P1PTF VARCHAR(10), P1UPL1 INT); INSERT INTO PORDHV VALUES (1, 'A', '20180301', 50), (1, 'T', '20180401', 50), (1, 'A', '20180401', 75), (1, 'A', '20180501', 60), (2, 'A', '20180301', 50), (2, 'A', '20180401', 75), (3, 'A', '20180301', 50), (1, 'A', '20180401', 75);
11 rows affected
 hidden batch(es)


SELECT PAANUM AS SKU, PAANTX AS ON_STOCK, P.PLANNED FROM INDIVID CROSS APPLY (SELECT P1UPL1 AS PLANNED FROM PORDHV WHERE P1MTRL = INDIVID.PAANUM AND P1STAT NOT IN ('E','T','P','R') AND PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD? ) P WHERE PAWHLO = 'h550' AND PAANTX > 0 ORDER BY PAANUM, PLANNED DESC
SKU ON_STOCK PLANNED
1 100 75
1 100 75
1 100 60
1 100 50
3 50 50
 hidden batch(es)


SELECT PAANUM AS SKU, PAANTX AS ON_STOCK, P.PLANNED FROM INDIVID LEFT JOIN (SELECT P1MTRL, SUM(P1UPL1) AS PLANNED FROM PORDHV WHERE P1STAT NOT IN ('E','T','P','R') AND PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD? GROUP BY P1MTRL) P ON P.P1MTRL = PAANUM WHERE PAWHLO = 'h550' AND PAANTX > 0 ORDER BY PAANUM;
SKU ON_STOCK PLANNED
1 100 260
3 50 50
 hidden batch(es)