Link to home
Start Free TrialLog in
Avatar of Sue Taylor
Sue TaylorFlag for United States of America

asked on

SQL Help

I have an existing report that I want to add a calculated field that I can later filter.  The report shows columns for "available", "qty_in_process" and "qty_for_production".  I want to be able to sum these three fields, then only display them on the report if the total of these three fields is less than another field in the report called "inv_min".  I'm not sure how to go about this and would love help.

Current SQL is as follows:

SELECT     p21_view_inv_loc.item_id, p21_view_inv_mast.inv_mast_uid, p21_view_inv_mast.extended_desc, p21_view_inv_loc.purchase_class,
                      p21_view_inv_loc.product_group_id, p21_view_inv_mast.weight, derivedtbl_1.available, derivedtbl_4.unit_size AS pallet_qty, p21_view_inv_loc.inv_min,
                      CASE purchase_class WHEN 'A' THEN 2 WHEN 'B' THEN 3 WHEN 'C' THEN 4 WHEN 'D' THEN 6 ELSE 0 END AS Multiplier,
                      p21_view_inv_mast.default_purchase_disc_group, p21_view_inv_loc.stockable, derivedtbl_2.qty_in_vessel, derivedtbl_3.order_quantity,
                      p21_view_inv_loc.purchase_discount_group, trend.trend_avg, trend.[3mth_avg], trend.[6mth_avg], trend.[12mth_avg], Excel_item.customer_part_no AS excel_item,
                      p21_view_inv_loc.location_id, pri_suppl.pri_supplier, p21_view_inv_loc_stock_status_1.qty_in_production, p21_view_inv_loc_stock_status_1.qty_for_process,
                      p21_view_inv_loc_stock_status_1.location_id AS Expr1, p21_view_inv_loc_stock_status_1.qty_for_production, p21_view_inv_loc.qty_in_process
FROM         p21_view_inv_loc INNER JOIN
                      p21_view_inv_mast ON p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid INNER JOIN
                          (SELECT     ([3mth_avg] + [6mth_avg] + [12mth_avg]) / 3 AS trend_avg, inv_mast_uid, item_id, [3mth_avg], [6mth_avg], [12mth_avg]
                            FROM          alprfa_view_corp_use_avg) AS trend ON p21_view_inv_mast.inv_mast_uid = trend.inv_mast_uid INNER JOIN
                      p21_view_inv_loc_stock_status AS p21_view_inv_loc_stock_status_1 ON
                      p21_view_inv_mast.inv_mast_uid = p21_view_inv_loc_stock_status_1.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_inventory_supplier_x_loc.primary_supplier, p21_view_inventory_supplier.inv_mast_uid, p21_view_inventory_supplier.item_id,
                                                   p21_view_inventory_supplier.supplier_id AS pri_supplier
                            FROM          p21_view_inventory_supplier INNER JOIN
                                                   p21_view_inventory_supplier_x_loc ON
                                                   p21_view_inventory_supplier.inventory_supplier_uid = p21_view_inventory_supplier_x_loc.inventory_supplier_uid
                            WHERE      (p21_view_inventory_supplier_x_loc.location_id = 101) AND (p21_view_inventory_supplier_x_loc.primary_supplier = 'Y')) AS pri_suppl ON
                      p21_view_inv_mast.inv_mast_uid = pri_suppl.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     customer_part_no, inv_mast_uid
                            FROM          p21_view_job_price_line
                            WHERE      (job_price_hdr_uid = 150) AND (row_status_flag = 704)) AS Excel_item ON
                      p21_view_inv_mast.inv_mast_uid = Excel_item.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_item_uom.unit_of_measure, p21_view_item_uom.unit_size, p21_view_item_uom.item_id, p21_view_inv_mast_1.inv_mast_uid,
                                                   p21_view_inv_mast_1.item_id AS Expr1
                            FROM          p21_view_item_uom INNER JOIN
                                                   p21_view_inv_mast AS p21_view_inv_mast_1 ON p21_view_item_uom.inv_mast_uid = p21_view_inv_mast_1.inv_mast_uid
                            WHERE      (p21_view_item_uom.unit_of_measure = 'pallet')) AS derivedtbl_4 ON p21_view_inv_mast.inv_mast_uid = derivedtbl_4.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     inv_mast_uid, order_quantity
                            FROM          p21_view_inv_loc AS p21_view_inv_loc_2
                            WHERE      (location_id = 101)) AS derivedtbl_3 ON p21_view_inv_loc.inv_mast_uid = derivedtbl_3.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     SUM(p21_view_vessel_receipts_line.container_qty_received) - SUM(p21_view_vessel_receipts_line.container_qty_unloaded) AS qty_in_vessel,
                                                   p21_view_po_line.inv_mast_uid
                            FROM          p21_view_vessel_receipts_line INNER JOIN
                                                   p21_view_po_line ON p21_view_vessel_receipts_line.po_line_uid = p21_view_po_line.po_line_uid
                            WHERE      (p21_view_vessel_receipts_line.row_status_flag <> 976)
                            GROUP BY p21_view_po_line.inv_mast_uid, p21_view_po_line.po_no, p21_view_po_line.qty_ordered, p21_view_vessel_receipts_line.container_qty_received,
                                                   p21_view_vessel_receipts_line.container_qty_unloaded
                            HAVING      (SUM(p21_view_vessel_receipts_line.container_qty_received) - SUM(p21_view_vessel_receipts_line.container_qty_unloaded) > 0)) AS derivedtbl_2 ON
                      p21_view_inv_loc.inv_mast_uid = derivedtbl_2.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_inv_loc_1.item_id,
                                                   SUM(p21_view_inv_loc_1.qty_on_hand - p21_view_inv_loc_1.qty_allocated - p21_view_inv_loc_stock_status.qty_non_pickable - p21_view_inv_loc_stock_status.qty_quarantined)
                                                    AS available
                            FROM          p21_view_inv_loc AS p21_view_inv_loc_1 INNER JOIN
                                                   p21_view_inv_loc_stock_status ON p21_view_inv_loc_1.location_id = p21_view_inv_loc_stock_status.location_id AND
                                                   p21_view_inv_loc_1.inv_mast_uid = p21_view_inv_loc_stock_status.inv_mast_uid
                            WHERE      (p21_view_inv_loc_1.company_id = '1')
                            GROUP BY p21_view_inv_loc_1.item_id) AS derivedtbl_1 ON p21_view_inv_loc.item_id = derivedtbl_1.item_id
WHERE     (p21_view_inv_loc.location_id = 101) AND (p21_view_inv_loc.purchase_discount_group LIKE '%DOM%') AND (p21_view_inv_loc_stock_status_1.location_id = 101)
                      AND (p21_view_inv_loc.stockable = 'y')
C--Users-staylor-Desktop-Current-SQ.docx
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
For starters, please edit your question to use a CODE block (toolbar, third button from right), and not a copy-paste of your T-SQL or an attached file, as that would make this much easier to read.

Also there's a wompload of T-SQL here, so if you can edit this down to only what's relevant to the question that would help.
Avatar of Sue Taylor
Sue Taylor
Flag of United States of America image

ASKER

[SELECT     p21_view_inv_loc.item_id, p21_view_inv_mast.inv_mast_uid, p21_view_inv_mast.extended_desc, p21_view_inv_loc.purchase_class,
                      p21_view_inv_loc.product_group_id, p21_view_inv_mast.weight, derivedtbl_1.available, derivedtbl_4.unit_size AS pallet_qty, p21_view_inv_loc.inv_min,
                      CASE purchase_class WHEN 'A' THEN 2 WHEN 'B' THEN 3 WHEN 'C' THEN 4 WHEN 'D' THEN 6 ELSE 0 END AS Multiplier,
                      p21_view_inv_mast.default_purchase_disc_group, p21_view_inv_loc.stockable, derivedtbl_2.qty_in_vessel, derivedtbl_3.order_quantity,
                      p21_view_inv_loc.purchase_discount_group, trend.trend_avg, trend.[3mth_avg], trend.[6mth_avg], trend.[12mth_avg], Excel_item.customer_part_no AS excel_item,
                      p21_view_inv_loc.location_id, pri_suppl.pri_supplier, p21_view_inv_loc_stock_status_1.qty_in_production, p21_view_inv_loc_stock_status_1.qty_for_process,
                      p21_view_inv_loc_stock_status_1.location_id AS Expr1, p21_view_inv_loc_stock_status_1.qty_for_production, p21_view_inv_loc.qty_in_process
FROM         p21_view_inv_loc INNER JOIN
                      p21_view_inv_mast ON p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid INNER JOIN
                          (SELECT     ([3mth_avg] + [6mth_avg] + [12mth_avg]) / 3 AS trend_avg, inv_mast_uid, item_id, [3mth_avg], [6mth_avg], [12mth_avg]
                            FROM          alprfa_view_corp_use_avg) AS trend ON p21_view_inv_mast.inv_mast_uid = trend.inv_mast_uid INNER JOIN
                      p21_view_inv_loc_stock_status AS p21_view_inv_loc_stock_status_1 ON
                      p21_view_inv_mast.inv_mast_uid = p21_view_inv_loc_stock_status_1.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_inventory_supplier_x_loc.primary_supplier, p21_view_inventory_supplier.inv_mast_uid, p21_view_inventory_supplier.item_id,
                                                   p21_view_inventory_supplier.supplier_id AS pri_supplier
                            FROM          p21_view_inventory_supplier INNER JOIN
                                                   p21_view_inventory_supplier_x_loc ON
                                                   p21_view_inventory_supplier.inventory_supplier_uid = p21_view_inventory_supplier_x_loc.inventory_supplier_uid
                            WHERE      (p21_view_inventory_supplier_x_loc.location_id = 101) AND (p21_view_inventory_supplier_x_loc.primary_supplier = 'Y')) AS pri_suppl ON
                      p21_view_inv_mast.inv_mast_uid = pri_suppl.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     customer_part_no, inv_mast_uid
                            FROM          p21_view_job_price_line
                            WHERE      (job_price_hdr_uid = 150) AND (row_status_flag = 704)) AS Excel_item ON
                      p21_view_inv_mast.inv_mast_uid = Excel_item.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_item_uom.unit_of_measure, p21_view_item_uom.unit_size, p21_view_item_uom.item_id, p21_view_inv_mast_1.inv_mast_uid,
                                                   p21_view_inv_mast_1.item_id AS Expr1
                            FROM          p21_view_item_uom INNER JOIN
                                                   p21_view_inv_mast AS p21_view_inv_mast_1 ON p21_view_item_uom.inv_mast_uid = p21_view_inv_mast_1.inv_mast_uid
                            WHERE      (p21_view_item_uom.unit_of_measure = 'pallet')) AS derivedtbl_4 ON p21_view_inv_mast.inv_mast_uid = derivedtbl_4.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     inv_mast_uid, order_quantity
                            FROM          p21_view_inv_loc AS p21_view_inv_loc_2
                            WHERE      (location_id = 101)) AS derivedtbl_3 ON p21_view_inv_loc.inv_mast_uid = derivedtbl_3.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     SUM(p21_view_vessel_receipts_line.container_qty_received) - SUM(p21_view_vessel_receipts_line.container_qty_unloaded) AS qty_in_vessel,
                                                   p21_view_po_line.inv_mast_uid
                            FROM          p21_view_vessel_receipts_line INNER JOIN
                                                   p21_view_po_line ON p21_view_vessel_receipts_line.po_line_uid = p21_view_po_line.po_line_uid
                            WHERE      (p21_view_vessel_receipts_line.row_status_flag <> 976)
                            GROUP BY p21_view_po_line.inv_mast_uid, p21_view_po_line.po_no, p21_view_po_line.qty_ordered, p21_view_vessel_receipts_line.container_qty_received,
                                                   p21_view_vessel_receipts_line.container_qty_unloaded
                            HAVING      (SUM(p21_view_vessel_receipts_line.container_qty_received) - SUM(p21_view_vessel_receipts_line.container_qty_unloaded) > 0)) AS derivedtbl_2 ON
                      p21_view_inv_loc.inv_mast_uid = derivedtbl_2.inv_mast_uid LEFT OUTER JOIN
                          (SELECT     p21_view_inv_loc_1.item_id,
                                                   SUM(p21_view_inv_loc_1.qty_on_hand - p21_view_inv_loc_1.qty_allocated - p21_view_inv_loc_stock_status.qty_non_pickable - p21_view_inv_loc_stock_status.qty_quarantined)
                                                    AS available
                            FROM          p21_view_inv_loc AS p21_view_inv_loc_1 INNER JOIN
                                                   p21_view_inv_loc_stock_status ON p21_view_inv_loc_1.location_id = p21_view_inv_loc_stock_status.location_id AND
                                                   p21_view_inv_loc_1.inv_mast_uid = p21_view_inv_loc_stock_status.inv_mast_uid
                            WHERE      (p21_view_inv_loc_1.company_id = '1')
                            GROUP BY p21_view_inv_loc_1.item_id) AS derivedtbl_1 ON p21_view_inv_loc.item_id = derivedtbl_1.item_id
WHERE     (p21_view_inv_loc.location_id = 101) AND (p21_view_inv_loc.purchase_discount_group LIKE '%DOM%') AND (p21_view_inv_loc_stock_status_1.location_id = 101)
                      AND (p21_view_inv_loc.stockable = 'y')
][/code]
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sue Taylor
Sue Taylor
Flag of United States of America image

ASKER

Thank you so much!