Pfthb

I have an issue where I am using a query to filter my results based on the lead time of an article. The confusing part for me is there is a sub section of that where i need to filter certain articles based on lead time and the country of origin. I am trying to do this using a NOT EXISTSstatement in side of WHERE but the results don't seem to be what I would expect. For example all articles with a country of origin of HONDURAS and a lead time of 90 should not return any results with a so_conf_del_date of after 2/15/19...however in the results I get many results of dates well into march which should be excluded from what I can see. Thought I would get a different set of eyes on it as I really don't use not exists a ton. thanks.

UPDATE: changing to NOT IN from NOT EXISTS and updated the correlation. Note also the subquery performs exactly as it should so not sure why I am getting results back outside of the range

Query UPDATED:

/* What to do about multiple COO's in ONE RANGE? ...join on order ORDER_ITEM.SEASONAL_INDICATOR? */
/* Use CASE WHEN in subquery to JOIN outerquery on vw_order_item.seasonal_indicator = OneRange_NAM.SEASON */
/* Assumption is being made that 'seasonal indicator' in vw_order_item would align with 'season' and thus COO in OneRange */
/* Added Date/LeadTime Parameters */
/* Use 'Planned Delivery Time'...this includes lead time and delivery time? */
/* Right join to ONE RANGE */
/* ARUN has no fully unallocated quantities - YES IT DOES */
/* 11/12/18 - replaced RDD with CDD */

SELECT kd.business_segment_desc, 
 q0.plant_code,
 q0.req_cat AS 'requirement_category',
 m.[department], 
 q0.commission_code_id,
 aa.LeadTime,
 q0.so_conf_del_date, 
 q0.valid_to_date,
 q0.sales_order_number, 
 q0.sales_order_item_number,
 q0.sold_to, 
 q0.bill_to,
 m.working_number,
 q0.material,
 m.[description],
 q0.open_quantity,
 q0.allocated_quantity,
 q0.unallocated_quantity,
 q0.percent_unallocated,
 aa.ActivationStatus AS 'global status',
 m.ib_lock,
 o.country_of_origin

FROM pdx_sap_user..vw_mm_material m
JOIN pdx_sap_user..vw_kd_business_segment kd ON m.business_segment_code = kd.business_segment_code
JOIN asagdwpdx_prod..ArticleNumbers aa ON m.material = aa.ArticleNumber
JOIN adi_user_maintained..VW_ONERange_NAM o ON m.material = o.article 
 AND aa.ArticleNumber = o.Article
JOIN

(SELECT i.plant_code, 
 h.commission_code_id, 
 a.so_conf_del_date,
 h.valid_to_date,
 i.sales_order_number, 
 i.sales_order_item_number,
 h.sold_to,
 h.bill_to,
 i.material,
 i.open_quantity,
 ((i.open_quantity) - SUM(a.quantity)) AS 'allocated_quantity',
 SUM(a.quantity) AS 'unallocated_quantity',
 a.req_cat,
 ROUND(SUM(a.quantity)/(i.open_quantity),2) AS 'percent_unallocated',
 SUM(a.quantity) AS 'arun_allocated_qty',
 CASE 
 WHEN i.seasonal_indicator = '18S' 
 THEN 'SS2018' 
 WHEN i.seasonal_indicator = '18F'
 THEN 'FW2018'
 WHEN i.seasonal_indicator = '19S' 
 THEN 'SS2019' 
 WHEN i.seasonal_indicator = '19F' 
 THEN 'FW2019'
 ELSE 'NO SEASON'
 END AS 'seasonal_indicator'

FROM pdx_sap_user..vw_order_item i 
JOIN pdx_sap_user..vw_order_header h ON i.sales_order_number = h.sales_order_number 
JOIN pdx_sap_user..vw_arun_norm_new a ON i.sales_order_number = a.sales_order_number 
AND i.sales_order_item_number = a.sales_order_item_number


WHERE i.open_quantity > 0
AND h.commission_code_id = 'B'
AND a.stock_type = 'A'

GROUP BY i.plant_code, 
 h.commission_code_id, 
 a.so_conf_del_date,
 h.valid_to_date,
 i.sales_order_number, 
 i.sales_order_item_number,
 h.sold_to,
 h.bill_to,
 i.material,
 i.open_quantity,
 a.req_cat,
 i.unallocated_quantity,
 i.seasonal_indicator

HAVING SUM((a.quantity)/i.open_quantity) > .5) q0 ON m.material = q0.material 
 AND q0.seasonal_indicator = o.season

WHERE aa.LeadTime = '30'
AND q0.so_conf_del_date < '01/15/2019'
OR aa.LeadTime = '45'
AND q0.so_conf_del_date < '02/01/2019'
OR aa.LeadTime = '60'
AND q0.so_conf_del_date < '02/15/2019'
OR aa.LeadTime = '75'
AND q0.so_conf_del_date < '03/01/2019'
OR aa.LeadTime = '90'
AND q0.so_conf_del_date < '03/15/2019'
OR aa.LeadTime = '105'
AND q0.so_conf_del_date < '04/01/2019'
OR aa.LeadTime = '120'
AND q0.so_conf_del_date < '04/15/2019'
OR aa.LeadTime = '135'
AND q0.so_conf_del_date < '05/01/2019'

AND q0.sales_order_number NOT IN ( SELECT q01.sales_order_number

 FROM pdx_sap_user..vw_order_item q01
 JOIN pdx_sap_user..vw_arun_norm_new a1 ON q01.sales_order_number = a1.sales_order_number 
 AND q01.sales_order_item_number = a1.sales_order_item_number
 JOIN asagdwpdx_prod..ArticleNumbers aa1 ON q01.material = aa1.ArticleNumber
 JOIN adi_user_maintained..VW_ONERange_NAM o1 ON q01.material = o1.article 

WHERE 
(
aa1.LeadTime = '30'
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '12/15/2018'
)
OR 
(
aa1.LeadTime = '45' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/01/2019'
)
OR 
(
aa1.LeadTime = '60' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/15/2019'
)
OR 
(
aa1.LeadTime = '75' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '02/01/2019'
)
OR 
(
aa1.LeadTime = '90' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '02/15/2019'
)
OR 
(
aa1.LeadTime = '90' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '03/01/2019'
)
OR 
(
aa1.LeadTime = '105' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '03/15/2019'
)
AND q0.sales_order_number = q01.sales_order_number
AND aa.LeadTime = aa1.LeadTime
AND q0.so_conf_del_date = a1.so_conf_del_date
AND o.Country_of_Origin = o1.Country_of_Origin)

GROUP BY kd.business_segment_desc, 
 q0.plant_code,
 q0.req_cat,
 m.[department], 
 q0.commission_code_id,
 aa.LeadTime,
 q0.so_conf_del_date, 
 q0.valid_to_date,
 q0.sales_order_number, 
 q0.sales_order_item_number,
 q0.sold_to, 
 q0.bill_to,
 m.working_number,
 q0.material,
 m.[description],
 q0.open_quantity,
 q0.allocated_quantity,
 q0.unallocated_quantity,
 q0.percent_unallocated,
 aa.ActivationStatus,
 m.ib_lock,
 o.country_of_origin

ORDER BY q0.sales_order_number DESC; 

WORKING VERSION:

/* What to do about multiple COO's in ONE RANGE? ...join on order ORDER_ITEM.SEASONAL_INDICATOR? */
/* Use CASE WHEN in subquery to JOIN outerquery on vw_order_item.seasonal_indicator = OneRange_NAM.SEASON */
/* Assumption is being made that 'seasonal indicator' in vw_order_item would align with 'season' and thus COO in OneRange */
/* Added Date/LeadTime Parameters */
/* Use 'Planned Delivery Time'...this includes lead time and delivery time? */
/* Right join to ONE RANGE */
/* ARUN has no fully unallocated quantities - YES IT DOES */
/* 11/12/18 - replaced RDD with CDD */

SELECT kd.business_segment_desc, 
 q0.plant_code,
 q0.req_cat AS 'requirement_category',
 m.[department], 
 q0.commission_code_id,
 aa.LeadTime,
 q0.so_conf_del_date, 
 q0.valid_to_date,
 q0.sales_order_number, 
 q0.sales_order_item_number,
 q0.sold_to, 
 q0.bill_to,
 m.working_number,
 q0.material,
 m.[description],
 q0.open_quantity,
 q0.allocated_quantity,
 q0.unallocated_quantity,
 q0.percent_unallocated,
 aa.ActivationStatus AS 'global status',
 m.ib_lock,
 o.country_of_origin

FROM pdx_sap_user..vw_mm_material m
JOIN pdx_sap_user..vw_kd_business_segment kd ON m.business_segment_code = kd.business_segment_code
JOIN asagdwpdx_prod..ArticleNumbers aa ON m.material = aa.ArticleNumber
JOIN adi_user_maintained..VW_ONERange_NAM o ON m.material = o.article 
 AND aa.ArticleNumber = o.Article
JOIN

(SELECT i.plant_code, 
 h.commission_code_id, 
 a.so_conf_del_date,
 h.valid_to_date,
 i.sales_order_number, 
 i.sales_order_item_number,
 h.sold_to,
 h.bill_to,
 i.material,
 i.open_quantity,
 ((i.open_quantity) - SUM(a.quantity)) AS 'allocated_quantity',
 SUM(a.quantity) AS 'unallocated_quantity',
 a.req_cat,
 ROUND(SUM(a.quantity)/(i.open_quantity),2) AS 'percent_unallocated',
 SUM(a.quantity) AS 'arun_allocated_qty',
 CASE 
 WHEN i.seasonal_indicator = '18S' 
 THEN 'SS2018' 
 WHEN i.seasonal_indicator = '18F'
 THEN 'FW2018'
 WHEN i.seasonal_indicator = '19S' 
 THEN 'SS2019' 
 WHEN i.seasonal_indicator = '19F' 
 THEN 'FW2019'
 ELSE 'NO SEASON'
 END AS 'seasonal_indicator'

FROM pdx_sap_user..vw_order_item i 
JOIN pdx_sap_user..vw_order_header h ON i.sales_order_number = h.sales_order_number 
JOIN pdx_sap_user..vw_arun_norm_new a ON i.sales_order_number = a.sales_order_number 
AND i.sales_order_item_number = a.sales_order_item_number


WHERE i.open_quantity > 0
AND h.commission_code_id = 'B'
AND a.stock_type = 'A'
AND i.sales_order_number NOT IN (SELECT q01.sales_order_number

 FROM pdx_sap_user..vw_order_item q01
 JOIN pdx_sap_user..vw_arun_norm_new a1 ON q01.sales_order_number = a1.sales_order_number 
 AND q01.sales_order_item_number = a1.sales_order_item_number
 JOIN asagdwpdx_prod..ArticleNumbers aa1 ON q01.material = aa1.ArticleNumber
 JOIN adi_user_maintained..VW_ONERange_NAM o1 ON q01.material = o1.article 

WHERE 
(
aa1.LeadTime = '30'
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '12/15/2018'
)
OR 
(
aa1.LeadTime = '45' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/01/2019'
)
OR 
(
aa1.LeadTime = '60' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/15/2019'
)
OR 
(
aa1.LeadTime = '75' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '02/01/2019'
)
OR 
(
aa1.LeadTime = '90' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '02/15/2019'
)
OR 
(
aa1.LeadTime = '90' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '03/01/2019'
)
OR 
(
aa1.LeadTime = '105' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '03/15/2019'
)
AND q01.sales_order_number = i.sales_order_number)

GROUP BY i.plant_code, 
 h.commission_code_id, 
 a.so_conf_del_date,
 h.valid_to_date,
 i.sales_order_number, 
 i.sales_order_item_number,
 h.sold_to,
 h.bill_to,
 i.material,
 i.open_quantity,
 a.req_cat,
 i.unallocated_quantity,
 i.seasonal_indicator

HAVING SUM((a.quantity)/i.open_quantity) > .5) q0 ON m.material = q0.material 
 AND q0.seasonal_indicator = o.season

WHERE aa.LeadTime = '30'
AND q0.so_conf_del_date < '01/15/2019'
OR aa.LeadTime = '45'
AND q0.so_conf_del_date < '02/01/2019'
OR aa.LeadTime = '60'
AND q0.so_conf_del_date < '02/15/2019'
OR aa.LeadTime = '75'
AND q0.so_conf_del_date < '03/01/2019'
OR aa.LeadTime = '90'
AND q0.so_conf_del_date < '03/15/2019'
OR aa.LeadTime = '105'
AND q0.so_conf_del_date < '04/01/2019'
OR aa.LeadTime = '120'
AND q0.so_conf_del_date < '04/15/2019'
OR aa.LeadTime = '135'
AND q0.so_conf_del_date < '05/01/2019'

GROUP BY kd.business_segment_desc, 
 q0.plant_code,
 q0.req_cat,
 m.[department], 
 q0.commission_code_id,
 aa.LeadTime,
 q0.so_conf_del_date, 
 q0.valid_to_date,
 q0.sales_order_number, 
 q0.sales_order_item_number,
 q0.sold_to, 
 q0.bill_to,
 m.working_number,
 q0.material,
 m.[description],
 q0.open_quantity,
 q0.allocated_quantity,
 q0.unallocated_quantity,
 q0.percent_unallocated,
 aa.ActivationStatus,
 m.ib_lock,
 o.country_of_origin

ORDER BY q0.sales_order_number DESC

Your OR is short circuiting. For example, look at this piece:

SELECT *
FROM Table
WHERE aa1.LeadTime = '30'
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '12/15/2018'
OR aa1.LeadTime = '45' 

Returns all records with aa1.LeadTime = '45' regardless of the other conditions.

I guess you mean this:

WHERE 
(
aa1.LeadTime = '30'
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '12/15/2018'
)
OR 
(
aa1.LeadTime = '45' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/01/2019'
)
OR 
(
aa1.LeadTime = '60' 
AND o1.country_of_origin IN ('EL SALVADOR','HONDURAS','MEXICO','U S A' ,'GUATEMALA')
AND a1.so_conf_del_date > '01/15/2019'
)
....
....

which is a very different expression.