1

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?

closed as unclear what you're asking by ypercubeᵀᴹ, RDFozz, Mr.Brownstone, Erik Darling, Tom V Feb 28 '18 at 7:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Does table INDIVID have an SKU column? – ypercubeᵀᴹ Feb 27 '18 at 16:02
  • Do you need to show PORDHV.P1UPL1 or SUM(PORDHV.P1UPL1) in final resultset ? – a1ex07 Feb 27 '18 at 16:55
  • 2
    It would be helpful to show us what your desired results would look like - that would help us understand better. – RDFozz Feb 27 '18 at 22:02
  • Hello Together, had a problems to reach my email. Essentially I need to end up with a table, where I will have Unique SKU, it's current stock level (even if 0), and future orders, that are active (even if 0). – Alex Himiak Mar 2 '18 at 10:10
1

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 >= '20180101' -- IS IT A TEXT OR A DATE FIELD?
            ) P
WHERE     PAWHLO = 'h550'
AND       PAANTX > 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 >= '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;
GO
SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |     260
  3 |       50 |      50

dbfiddle here

  • none works. Please see the edited main topic- maybe it will be clearer now, – Alex Himiak Mar 2 '18 at 10:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.