Joining two processed tables without Primary Key I recently started such an exiting activity as digging in databases.
Straight to the point.
There are two tables. First one shows the current stock of products. I simplify it by following query:

SELECT *
FROM dbo.INDIVID
WHERE PAWHLO ='h550'--Specific warehouse

The second one is showing past, current and future orders.It contains hell lot of info including multiple orders of same SKU:
 SELECT DISTINCT P1MTRL AS SKU, SUM(P1UPL1) AS PLANNED_quantity
FROM dbo.PORDHV
WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running). 
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
GROUP BY SKU
ORDER BY PLANNED_quantity DESC

Both of the queries work on themselves, but combining them is something I cant manage,
How can I combine those two queries in one,preferably outer joint, so I dont lose the data neither from stock nor future orders?
I tried:


SELECT DISTINCT PORDHV.P1MTRL,INDIVID.PAANTX AS ON_STOCK, PORDHV.P1UPL1 AS PLANNED
FROM dbo.PORDHV
FULL JOIN 
 dbo.INDIVID
ON
PORDHV.P1MTRL=INDIVID.PAANUM
WHERE PORDHV.P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running). 
AND PORDHV.P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
AND INDIVID.PAWHLO ='h550' 
AND INDIVID.PAANTX  0
ORDER BY PLANNED DESC


But it apparently returns noncense.
Thanks in advance.

P.S.

I was asked to clarify.
I will leave the UNION, with which I managed to get an something the resembles what I need:


SELECT
  INDIVID.PAANUM as P1MTRL,
 SUM(INDIVID.PAANTX) AS AVAILABLE_QUANTITY,
 '' as PLANNED_QUANTITY,
 convert(date,getdate()) as Date
  FROM
  INDIVID
    WHERE PAWHLO ='h550'
  GROUP BY INDIVID.PAANUM
  --ORDER BY QUANTITY DESC
  union
   SELECT DISTINCT DS1.P1MTRL, 
  '' as AVAILABLE_QUANTITY,
  SUM(P1UPL1) AS PLANNED_QUANTITY,
  convert(date,convert(varchar(10),P1PTF)) as Date
FROM dbo.PORDHV as DS1

INNER JOIN (
  SELECT DISTINCT P2ANR as P1MTRL 
  FROM [POPPROD].[dbo].[PORDRDART]
  WHERE PMWHSL = 'H550'
  ) as DS2 on (
  DS1.P1MTRL = DS2.P1MTRL
  )

WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running). 
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
--AND P1MAS2 in ('ph640','ph625')
GROUP BY DS1.P1MTRL, P1PTF
--ORDER BY SUM(P1UPL1) DESC
order by P1MTRL


In this way it returns the union of tables with several rows related to same SKU. I also added date to be able to play with periods in the future. Date for the row of current stock is TODAY, when there are two or more orders of the same SKU on particular date - they are summed up.


400011873   0       0       2018-03-02
400015114   0       0       2018-03-02
400052975   24894   0       2018-03-02
400052977   540     0       2018-03-02
400052979   0       12000   2018-03-05
400052979   8311    0       2018-03-02

How to get those rows of SKU's distinct. What would You improve and aggregate before going to Report Manager?
I'm not sure if this is what are you looking for.

I've set up next example:




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);
GO



If you're trying to get all orders, order by quantity you can use a CROSS APPLY solution:




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 &gt;= '20180101' -- IS IT A TEXT OR A DATE FIELD?
            ) P
WHERE     PAWHLO = 'h550'
AND       PAANTX &gt; 0
ORDER BY  PAANUM, PLANNED DESC
GO



SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |      75
  1 |      100 |      75
  1 |      100 |      60
  1 |      100 |      50
  3 |       50 |      50



But it you want the sum of all orders by SKU you can sum all orders of each SKU and then LEFT JOIN with INDIVID table.




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 &gt;= '20180101' -- IS IT A TEXT OR A DATE FIELD?
           GROUP BY P1MTRL) P
ON        P.P1MTRL = PAANUM
WHERE     PAWHLO = 'h550'
AND       PAANTX &gt; 0
ORDER BY  PAANUM;
GO



SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |     260
  3 |       50 |      50



dbfiddle here