Deadfull Union - Slow

  • Here's another Union subject...

    I have two indiviual statements that are fast (8 seconds, 2 seconds), but when I union them in a view, the query takes over a minute. 

    When do I begin?  I always have this issue and just end up making two views and using ASP to do the joins.  (The worst way to do it).  I have more time to do some R&D with this project, So I want to learn how I can do this.

  • I really don't think ASP is doing the joins. SQL Server is doing the joins.

    Also I believe that you are doing a CROSS JOIN (Cartesian Product), selecting all possible combinations from the two views. I think you are in need of an inner join, or possible a left or right join.

    Please post your query here, sample date from the two views and the output you wish for, using the supplied test data.


    N 56°04'39.16"
    E 12°55'05.25"

  • I would guess you're using UNION when you should be using UNION ALL, which forces a potentially costly sort/distinct operation. But just a guess until you post more info.

  • Here is the code.   Right now if I run this, it's around 12 seconds.  If I take out both the Where clauses and move this into a view, and then query the view using the where clause, it takes over a minute.  (timesout in ASP).

    SELECT H.PlantNumber, H.PONumber, 'W' as PlantType, H.StatusCode, VM.VendorNumber, VM.VendorName, H.OrderDate, Case when D.ItemTypeCode =  'E' THEN 'N' ELSE D.ItemTypeCode END as InventoryItem, D.ItemNumber, D.PartNumber,

     D.PartDescription AS POPartDescr, IM.PartDescription AS IMPartDescr, IM.ProductCode, WPC.ProductDescription AS ProductCodeDescr,

     WPC.ProductGroupCode + ' - ' + PGC.ProductGroupDescription AS ProductGroupCode, WPC.ProductGroupType AS CommodityGroup, ID.WarehouseLocation,

     D.TotalOrderQty, (Select Sum(InventoryQty) FROM DataWarehouse.dbo.MSSvEDIDetails ED WHERE ED.InvoicePONumber = H.PONumber AND (ED.ParkerPartNumber = D.PartNumber OR ED.CustomerPartNumber = D.PartNumber)) as ShipQty, CASE WHEN Len(D.ReceiptDate)<> 0 THEN D.ReceivedQty END as ReceivedQty,

     ISNULL(D.Price*ER.ExchangeRate, 0) AS UnitPriceCND,  ISNULL(D.Price, 0) AS UnitPriceUS,

     ISNULL(D.TotalOrderQty*D.Price*ER.ExchangeRate, 0) AS ExtPriceCnd, ISNULL(D.TotalOrderQty*D.Price, 0) AS ExtPriceUS,

     D.NeedDate01 AS RequestDate, (Select Max(ShipDate) FROM DataWarehouse.dbo.MSSvEDIDetails ED WHERE (ED.InvoicePONumber = H.PONumber OR ED.PONumber = H.PONumber) AND (ED.ParkerPartNumber = D.PartNumber OR ED.CustomerPartNumber = D.PartNumber))  as ShipDate, D.ReceiptDate, H.ShipVia, V.BuyerId,

     CASE WHEN ID.ManufacturingLeadTime <> 0 THEN ID.ManufacturingLeadTime  ELSE WPC.StandardDeliveryDays END AS LeadDays,

     (Select distinct 'Y' FROM DataWarehouse.dbo.WhseOpenOrders WHERE PartNumber = D.PartNumber) as CrossDock

    FROM dbo.MSSvPOHeader H INNER JOIN

     dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber LEFT OUTER JOIN

     dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber LEFT OUTER JOIN

     Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber LEFT OUTER JOIN

     dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber LEFT OUTER JOIN

     dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber  LEFT OUTER JOIN

     dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode  INNER JOIN

     Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode INNER JOIN

     Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2

    WHERE H.StatusCode<>'C' AND D.TotalOrderQty > ISNULL(D.ReceivedQty,0)

    UNION ALL

    SELECT     H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode, H.VendorNumber, H.VendorName, H.OrderDate, H.InventoryCode as InventoryItem, D.ItemNumber, D.PartNumber,

     D.PartDescription, '' as IMPartDescr, '' as ProductCode, '' as ProductCodeDescr, '' as ProductGroupCode, '' as CommodityGroup,

     (SELECT DISTINCT Min(StockLocation) FROM DataWarehouse.dbo.MfgShopComponents WHERE PlantNumber = D.PlantNumber AND Component = D.PartNumber) as WarehouseLocation,

      D.OnOrderQty, '' as ShipQty,  D.ChangeQty as ReceivedQty,

     CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)* ER.ExchangeRate,0) END AS UnitPriceCND,

     CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)/ ER.ExchangeRate,0) END AS UnitPriceUS,

     CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)* ER.ExchangeRate,0) END AS ExtPriceCND,

     CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)/ ER.ExchangeRate,0) END AS ExtPriceUS,

      D.RequestDate,null as ShipDate, D.ReceiptDateTime as ReceiptDate, '' as ShipVia, D.RequesterId as BuyerId, '' as LeadDays,

     (Select distinct 'Y' FROM DataWarehouse.dbo.MfgShopComponents C INNER JOIN DataWarehouse.dbo.View_MfgBacklog M ON C.OrderNumber = M.OrderNumber Where C.Component = D.PartNumber) as CrossDock

    FROM         MfgPOHeader H INNER JOIN

                          MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber INNER JOIN

     Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2

    WHERE D.ReceiptDateTime is null AND D.OnOrderQty > ISNULL(D.ChangeQty,0)

     

    My way around this in the past, was the make two views and use TSQL/ASP to do the union and where clauses.  I'm not sure the best way on this.

  • The problem is not the UNION.

    The problem is all the sub-SELECTs within each SELECT, eg:

    (Select distinct 'Y' FROM DataWarehouse.dbo.WhseOpenOrders WHERE PartNumber = D.PartNumber) as CrossDock

    When you place a SELECT within a SELECT, you implement essentially a cursor based solution that can nearly always be guaranteed to perform abominably.

    In most cases, these can be re-expressed as derived tables in the FROM part of your query. EG, above, you need a 'Y' indicator if a part number exists in aother table ? In that case, build a derived table of the PartNumbers and LEFT JOIN to it. The derived table gets evaluated ONCE, and doesn't have to be evaluated for each row thru the main resultset. Here is how you would get your column "CrossDock" using a derived table:

    SELECT {Your select columns go here},

      CASE WHEN dt.PartNumber IS NOT NULL THEN 'Y' ELSE 'N' END AS CrossDock

    FROM {Your Current FROM goes here}

    LEFT JOIN

    (

      SELECT DISTINCT PartNumber

      FROM DataWarehouse.dbo.WhseOpenOrders

    ) dt

      On (dt.PartNumber = D.PartNumber)

  • PW:

    Thank you, I never thought about sub-selects slowing things down that much.  I did like you said and put it into a derived table (never thought about doing that either).   I'll try that out and change the rest of them and see how the union works after that.

  • I made the changes and the union seems to be working a little more quicker and stable.  Seperate the queries are 8 seconds together.  In the view, it's around 17 seconds.  (But atleast its not timing out in the ASP Page)

  • Can you post the individual statements, with their WHERE clauses, and the select from the view, with its WHERE clause? There shouldn't be any significant difference in execution times.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • I don't know how to post code the right way, but here is a dump of everything....

     

    View - 17 seconds

    SELECT PO.PlantType,PO.CrossDock,ISNULL(Left(SP.FirstName,1) +' '+ SP.LastName,PO.BuyerId),PO.VendorNumber,PO.VendorName,PO.PlantNumber,PO.PONumber,PO.ItemNumber,PO.PartNumber,PO.POPartDescr,PO.IMPartDescr,PO.ProductCode,PO.ProductCodeDescr,PO.ProductGroupCode,PO.InventoryItem,PO.OrderDate,PO.RequestDate,PO.ShipDate,PO.ReceiptDate,PO.TotalOrderQty,PO.ShipQty,PO.ReceivedQty,PO.UnitPriceCND,PO.UnitPriceUS,PO.ExtPriceCND,PO.ExtPriceUS,PO.LeadDays

     ,IV.Avl_Mil,IV.Avl_Cal,IV.Avl_Mtl,PO.ShipVia

    FROM DataWarehouse.dbo.View_PurchaseOrders PO LEFT OUTER JOIN

     StaffProfiles.dbo.StaffProfiles SP ON PO.BuyerId = SP.ClockNumber

     LEFT OUTER JOIN (SELECT PartNumber, SUM(CASE WHEN PlantNumber='08' THEN AvailableInventory END) as Avl_Mil, SUM(CASE WHEN PlantNumber='10' THEN AvailableInventory END) as Avl_Cal, SUM(CASE WHEN PlantNumber='14' THEN AvailableInventory END) as Avl_Mtl FROM DataWarehouse.dbo.MSSvItemInventoryData GROUP BY PartNumber) IV ON PO.PartNumber = IV.PartNumber

    WHERE  PO.OrderDate between '6/1/2006' and '6/12/2006' AND PO.TotalOrderQty > ISNULL(PO.ReceivedQty,0)

    ORDER BY PO.RequestDate

    T-SQL - 7 seconds

    SELECT H.PlantNumber, H.PONumber, 'W' as PlantType, CASE WHEN D.ReceiptDate is null then 'O' WHEN Len(D.ReceiptDate)<>0 THEN 'C' ELSE H.StatusCode END as StatusCode, VM.VendorNumber, VM.VendorName, H.OrderDate, Case when D.ItemTypeCode =  'E' THEN 'N' ELSE D.ItemTypeCode END as InventoryItem, D.ItemNumber, D.PartNumber,

     D.PartDescription AS POPartDescr, IM.PartDescription AS IMPartDescr, IM.ProductCode, WPC.ProductDescription AS ProductCodeDescr,

     WPC.ProductGroupCode + ' - ' + PGC.ProductGroupDescription AS ProductGroupCode, WPC.ProductGroupType AS CommodityGroup, ID.WarehouseLocation,

     D.TotalOrderQty, ED.InventoryQty as ShipQty, CASE WHEN Len(D.ReceiptDate)<> 0 THEN D.ReceivedQty END as ReceivedQty,

     ISNULL(D.Price*ER.ExchangeRate, 0) AS UnitPriceCND,  ISNULL(D.Price, 0) AS UnitPriceUS,

     ISNULL(D.TotalOrderQty*D.Price*ER.ExchangeRate, 0) AS ExtPriceCnd, ISNULL(D.TotalOrderQty*D.Price, 0) AS ExtPriceUS,

     D.NeedDate01 AS RequestDate, ED.ShipDate, D.ReceiptDate, H.ShipVia, V.BuyerId,

     CASE WHEN ID.ManufacturingLeadTime <> 0 THEN ID.ManufacturingLeadTime  ELSE WPC.StandardDeliveryDays END AS LeadDays,

     CASE WHEN WB.PartNumber IS NOT NULL THEN 'Y' END as CrossDock

    FROM dbo.MSSvPOHeader H INNER JOIN

     dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber LEFT OUTER JOIN

     dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber LEFT OUTER JOIN

     Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber LEFT OUTER JOIN

     dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber LEFT OUTER JOIN

     dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber  LEFT OUTER JOIN

     dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode  INNER JOIN

     Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode INNER JOIN

     Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2 LEFT OUTER JOIN

     (SELECT distinct PartNumber FROM DataWarehouse.dbo.WhseOpenOrders) WB ON D.PartNumber = WB.PartNumber LEFT OUTER JOIN

     (SELECT CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END as PONumber, CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END as PartNumber, MAX(ShipDate) as ShipDate, Sum(InventoryQty) as InventoryQty FROM DataWarehouse.dbo.MSSvEDIDetails GROUP BY CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END, CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END ) ED ON H.PONumber = ED.PONumber AND D.PartNumber = ED.PartNumber

    WHERE  H.OrderDate between '6/1/2006' and '6/12/2006' AND D.TotalOrderQty > ISNULL(D.ReceivedQty,0)

    UNION ALL

    SELECT     H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode, H.VendorNumber, H.VendorName, H.OrderDate, H.InventoryCode as InventoryItem, D.ItemNumber, D.PartNumber,

     '' as POPartDescr, D.PartDescription as IMPartDescr, '' as ProductCode, '' as ProductCodeDescr, '' as ProductGroupCode, '' as CommodityGroup,

     MSC.StockLocation as WarehouseLocation,

      D.OnOrderQty as TotalOrderQty, null as ShipQty,  D.ChangeQty as ReceivedQty,

     CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)* ER.ExchangeRate,0) END AS UnitPriceCND,

     CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)/ ER.ExchangeRate,0) END AS UnitPriceUS,

     CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)* ER.ExchangeRate,0) END AS ExtPriceCND,

     CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)/ ER.ExchangeRate,0) END AS ExtPriceUS,

      D.RequestDate,null as ShipDate, D.ReceiptDateTime as ReceiptDate, '' as ShipVia, D.RequesterId as BuyerId, '' as LeadDays,

     CASE WHEN MSC.OrderNumber is not null then 'Y' END as CrossDock

    FROM MfgPOHeader H INNER JOIN

     MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber INNER JOIN

     Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2 LEFT OUTER JOIN

     (Select SC.PlantNumber, SC.Component, Min(SC.StockLocation) as StockLocation, Min(M.OrderNumber) as OrderNumber FROM DataWarehouse.dbo.MfgShopComponents SC LEFT OUTER JOIN DataWarehouse.dbo.View_MfgBacklog M ON SC.OrderNumber = M.OrderNumber GROUP BY SC.PlantNumber, SC.Component) MSC ON D.PlantNumber = MSC.PlantNumber AND D.PartNumber = MSC.Component

    WHERE  H.OrderDate between '6/1/2006' and '6/12/2006' AND D.OnOrderQty > ISNULL(D.ChangeQty,0)

     

    So I think the T-SQL is quicker because each statement gets the where clause, compared to the view that unions both statements and the applies the where clause.  Or am I way off ??

  • It's probably not that. The optimiser normally knows to add the same where clause
    to each half of the union before evaluating them.
    But if the code you've posted reflects your tests, then you're not comparing like with like,
    since you are not just querying the view + where clause, you're also adding more joins.
    How does this perform? It's the TSQL embedded in the first query as an inline view.
    Run it twice to prevent the initial plan generation from skewing the results.
     
    skewing the results with SELECT PO.PlantType,PO.CrossDock,ISNULL(Left(SP.FirstName,1) +' '+ SP.LastName,PO.BuyerId),
    PO.VendorNumber,PO.VendorName,PO.PlantNumber,PO.PONumber,PO.ItemNumber,PO.PartNumber,
    PO.POPartDescr,PO.IMPartDescr,PO.ProductCode,PO.ProductCodeDescr,PO.ProductGroupCode,
    PO.InventoryItem,PO.OrderDate,PO.RequestDate,PO.ShipDate,PO.ReceiptDate,PO.TotalOrderQty,
    PO.ShipQty,PO.ReceivedQty,PO.UnitPriceCND,PO.UnitPriceUS,PO.ExtPriceCND,PO.ExtPriceUS,PO.LeadDays
    ,IV.Avl_Mil,IV.Avl_Cal,IV.Avl_Mtl,PO.ShipVia

    FROM

    (

    SELECT

    H.PlantNumber, H.PONumber, 'W' as PlantType,

    CASE WHEN D.ReceiptDate is null then 'O' WHEN Len(D.ReceiptDate)<>0 THEN 'C' ELSE H.StatusCode END as StatusCode,
    VM.VendorNumber, VM.VendorName, H.OrderDate,
    Case when D.ItemTypeCode = 'E' THEN 'N' ELSE D.ItemTypeCode END as InventoryItem, D.ItemNumber, D.PartNumber,
    D.PartDescription AS POPartDescr, IM.PartDescription AS IMPartDescr, IM.ProductCode, WPC.ProductDescription AS ProductCodeDescr,
    WPC.ProductGroupCode + ' - ' + PGC.ProductGroupDescription AS ProductGroupCode, WPC.ProductGroupType AS CommodityGroup,
    ID.WarehouseLocation, D.TotalOrderQty, ED.InventoryQty as ShipQty, CASE WHEN Len(D.ReceiptDate)<> 0 THEN D.ReceivedQty END as ReceivedQty,
    ISNULL(D.Price*ER.ExchangeRate, 0) AS UnitPriceCND, ISNULL(D.Price, 0) AS UnitPriceUS,
    ISNULL(D.TotalOrderQty*D.Price*ER.ExchangeRate, 0) AS ExtPriceCnd, ISNULL(D.TotalOrderQty*D.Price, 0) AS ExtPriceUS,
    D.NeedDate01 AS RequestDate, ED.ShipDate, D.ReceiptDate, H.ShipVia, V.BuyerId,
    CASE WHEN ID.ManufacturingLeadTime <> 0 THEN ID.ManufacturingLeadTime ELSE WPC.StandardDeliveryDays END AS LeadDays,
    CASE WHEN WB.PartNumber IS NOT NULL THEN 'Y' END as CrossDock

    FROM

    dbo.MSSvPOHeader H

    INNER

    JOIN dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber

    LEFT

    OUTER JOIN dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber

    LEFT

    OUTER JOIN Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber

    LEFT

    OUTER JOIN dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber

    LEFT

    OUTER JOIN dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber

    LEFT

    OUTER JOIN dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode

    INNER

    JOIN Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode

    INNER

    JOIN Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2

    LEFT

    OUTER JOIN (SELECT distinct PartNumber FROM DataWarehouse.dbo.WhseOpenOrders) WB ON D.PartNumber = WB.PartNumber

    LEFT

    OUTER JOIN (SELECT CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END as PONumber,

    CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END PartNumber,
    MAX(ShipDate) as ShipDate,
    Sum(InventoryQty) as InventoryQty
    FROM DataWarehouse.dbo.MSSvEDIDetails GROUP BY
    CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END,
    CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END ) ED
    ON H.PONumber = ED.PONumber AND D.PartNumber = ED.PartNumber

    UNION

    ALL

    SELECT

    H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode,

    H.VendorNumber, H.VendorName, H.OrderDate, H.InventoryCode as InventoryItem, D.ItemNumber, D.PartNumber,
    '' as POPartDescr, D.PartDescription as IMPartDescr, '' as ProductCode, '' as ProductCodeDescr,
    '' as ProductGroupCode, '' as CommodityGroup, MSC.StockLocation as WarehouseLocation,
    D.OnOrderQty as TotalOrderQty, null as ShipQty, D.ChangeQty as ReceivedQty,
    CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)* ER.ExchangeRate,0) END AS UnitPriceCND,
    CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)/ ER.ExchangeRate,0) END AS UnitPriceUS,
    CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)* ER.ExchangeRate,0) END AS ExtPriceCND,
    CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)/ ER.ExchangeRate,0) END AS ExtPriceUS,
    D.RequestDate,null as ShipDate, D.ReceiptDateTime as ReceiptDate, '' as ShipVia, D.RequesterId as BuyerId, '' as LeadDays,
    CASE WHEN MSC.OrderNumber is not null then 'Y' END as CrossDock

    FROM

    MfgPOHeader H

    INNER

    JOIN MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber

    INNER

    JOIN Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2

    LEFT

    OUTER JOIN (Select SC.PlantNumber, SC.Component, Min(SC.StockLocation) as StockLocation, Min(M.OrderNumber) as OrderNumber

    FROM DataWarehouse.dbo.MfgShopComponents SC LEFT OUTER JOIN DataWarehouse.dbo.View_MfgBacklog M ON SC.OrderNumber = M.OrderNumber
    GROUP BY SC.PlantNumber, SC.Component) MSC ON D.PlantNumber = MSC.PlantNumber AND D.PartNumber = MSC.Component

    )

    PO

    LEFT

    OUTER JOIN StaffProfiles.dbo.StaffProfiles SP ON PO.BuyerId = SP.ClockNumber

    LEFT

    OUTER JOIN ( SELECT PartNumber,

    SUM(CASE WHEN PlantNumber='08' THEN AvailableInventory END) as Avl_Mil,
    SUM(CASE WHEN PlantNumber='10' THEN AvailableInventory END) as Avl_Cal,
    SUM(CASE WHEN PlantNumber='14' THEN AvailableInventory END) as Avl_Mtl
    FROM DataWarehouse.dbo.MSSvItemInventoryData GROUP BY PartNumber) IV
    ON PO.PartNumber = IV.PartNumber

    WHERE

    PO.OrderDate between '6/1/2006' and '6/12/2006' AND PO.TotalOrderQty > ISNULL(PO.ReceivedQty,0)

    ORDER

    BY PO.RequestDate

     

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply