Query criteria help needed-CodePudding

Below image are 2 tables i want to convert into a query. The query example is the result i want to achieve. Ive been trying to get this working for hours but for some reason i can't get it to work, all info can be found in the image.

enter image description here

CodePudding user response：

I imagine that you have also AZ344 article in TABLE1. So, You can try this query:

with NEWTABLE1 as (select sum(nvl([TOTAL STOCK],0)) as [TOTAL STOCK], ARTICLE, TYPE 
from TABLE1 group by ARTICLE, TYPE) 
select T2.SUPPLIER, NEWTABLE1.ARTICLE, NEWTABLE1.TYPE, T2.[MIN.STOCK], 
NEWTABLE1.[TOTAL STOCK] 
from NEWTABLE1 T1 inner join TABLE2 T2 
on T1.ARTICLE = T2.ARTICLE
where T1.[TOTAL STOCK] < nvl(T2.[MIN.TOTAL],T1.[TOTAL STOCK])

The nvl() function is for Oracle, you have to change it if you use other database.

The [] are used because you have blank or other special character in the name of the columns.

CodePudding user response：

You can first use the SUM window function to compute the overall amount of stocks partitioned by article, then use a LEFT JOIN to compute what articles need restock:

WITH `TOTAL STOCKS PER ARTICLE` AS (
    SELECT 
        `ARTICLE`,
        SUM(`TOTAL STOCK`) OVER(PARTITION BY `ARTICLE`) AS `TOTAL STOCK`
    FROM 
        `STOCK`
)
SELECT DISTINCT
    `PART DATA`.`SUPLIER`,
    `PART DATA`.`ARTICLE`,
    `PART DATA`.`TYPE`,
    `PART DATA`.`MIN. STOCK`,
    COALESCE(`TOTAL STOCKS PER ARTICLE`.`TOTAL STOCK`, 0) AS `TOTAL STOCK` 
FROM 
    `PART DATA`
LEFT JOIN 
    `TOTAL STOCKS PER ARTICLE` 
ON 
    `PART DATA`.`ARTICLE` = `TOTAL STOCKS PER ARTICLE`.`ARTICLE`
HAVING
    `MIN. STOCK` > `TOTAL STOCK`

This uses MySQL syntax. Check the sql fiddle here: https://www.db-fiddle.com/f/spwn3uksVztva8L1oXMPWq/0.