-- =======   Final Snapshot Query  - Worked as of 02-10-04 ========================================

-- ======================================== Does not include detail subquery's WHERE clause ===================================

SELECT TOP 100 PERCENT 
              Detail.Company, Detail.Item, Detail.LineStation, '' As MainWarehouse, Detail.LSStartDate, Detail.QtyToBeBkfl, Detail.RqmtOrigin,
              Detail.LineStationAssyOrd as AssyOrd, Max(Detail.OpenOrdersAssyOrd) AS FirstOfOpenOrdersAssyOrd,
              Max(Detail.AssyLine) AS FirstOfAssyLine, Max(Detail.LineNumb) AS FirstOfLineNumb, Max(Detail.SFWhse) AS FirstOfOpenOrdersSFWhse, 
              Max(Detail.LineStatOrdStat) AS FirstOfLineStatOrdStat, Max(Detail.FlrStock) AS FirstOfFlrStock, Max(Detail.SupplySys) AS FirstOfSupplySys,
              Max(Detail.SupplyType) AS FirstOfSupplyType, Max(Detail.SupplyCode) AS FirstOfSupplyCode,
              Max(Detail.PlanEndDate) as FirstOfOpenOrderPlanEndDate, Count(Detail.Item) AS CountOfItem, 
              GetDate() - 6 As CutoffDay,
              GetDate() as TimeStamp, 0 as QueryCodeChanged  
FROM 
( 
  SELECT TOP 100 PERCENT
                LineStationCompany as Company, LineStationItem as Item, OpenOrdersAssyOrd,
                LineStationAssyOrd, AssyLine, LineNumb, Linestation, LinestationSFWhse as SFWhse, LSStartDate, PlanEndDate, LinestatOrdStat, FlrStock,
                SupplySys, SupplyType, SupplyCode, QtyToBeBkfl, RqmtOrigin 
  FROM
  (
    SELECT  TOP 100 PERCENT A.Company AS OpenOrdersCompany,
                  B.Company AS LineStationCompany, A.Item AS OpenOrdersItem, B.Item AS LineStationItem,
                  Case when A.AssyOrd = B.AssyOrd Then B.AssyOrd Else 'nomatch' End AS OpenOrdersAssyOrd, B.AssyOrd AS LineStationAssyOrd,
                  A.AssyLine, A.LineNumb, B.LineStation, A.SFWhse AS OpenOrdersSFWhse, B.SFWhse As LineStationSFWhse, B.LSStartDate,
                  A.PlanEndDate, A.LineStatOrdStat, A.FlrStock, A.SupplySys, A.SupplyType, A.SupplyCode, B.QtyToBeBkfl, B.RqmtOrigin 
    FROM 
    ( 
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, AssyLine, LineNumb, SFWhse, PlanEndDate, LineStatOrdStat, FlrStock, SupplySys, SupplyType, SupplyCode 
      FROM C614.ASCOpenOrderDetail 
      WHERE LineStatOrdStat='Completed' AND cast(planstartdate as datetime) < getdate() - 6 
      ORDER BY Item, AssyOrd, SFWhse 
    ) A 
    RIGHT OUTER JOIN 
    (
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, LineStation, LSStartDate, SFWhse, QtyToBeBkfl, RqmtOrigin 
      FROM C614.ASCLineStation
      WHERE QtyToBeBkfl > 0 AND cast(lsstartdate as datetime) < getdate() - 6
    ORDER BY Item, AssyOrd, SFWhse 
    ) B
    ON B.Item = A.Item AND B.SFWhse = A.SFWhse AND B.AssyOrd = A.AssyOrd 
    ORDER BY B.Item, B.AssyOrd, B.SFWhse 
  ) Beavis
  ORDER BY Beavis.LineStationItem, Beavis.LineStationAssyOrd, Beavis.LineStationSFWhse 
) Detail

GROUP BY Detail.Company, Detail.Item, Detail.LineStation, Detail.LSStartDate, Detail.QtyToBeBkfl, Detail.RqmtOrigin, Detail.LineStationAssyOrd
ORDER BY Detail.Item, Detail.LineStationAssyOrd, Detail.FirstOfOpenOrdersSFWhse


-- ================================== Includes Detail subquery ===============================================================================

SELECT TOP 100 PERCENT 
              Detail.Company, Detail.Item, Detail.LineStation, '' As MainWarehouse, Detail.LSStartDate, Detail.QtyToBeBkfl, Detail.RqmtOrigin,
              Detail.LineStationAssyOrd as AssyOrd, Max(Detail.OpenOrdersAssyOrd) AS FirstOfOpenOrdersAssyOrd,
              Max(Detail.AssyLine) AS FirstOfAssyLine, Max(Detail.LineNumb) AS FirstOfLineNumb, Max(Detail.SFWhse) AS FirstOfOpenOrdersSFWhse, 
              Max(Detail.LineStatOrdStat) AS FirstOfLineStatOrdStat, Max(Detail.FlrStock) AS FirstOfFlrStock, Max(Detail.SupplySys) AS FirstOfSupplySys,
              Max(Detail.SupplyType) AS FirstOfSupplyType, Max(Detail.SupplyCode) AS FirstOfSupplyCode,
              Max(Detail.PlanEndDate) as FirstOfOpenOrderPlanEndDate, Count(Detail.Item) AS CountOfItem, 
              GetDate() - 6 As CutoffDay,
              GetDate() as TimeStamp, 0 as QueryCodeChanged  
FROM 
( 
  SELECT TOP 100 PERCENT
                LineStationCompany as Company, LineStationItem as Item, OpenOrdersAssyOrd,
                LineStationAssyOrd, AssyLine, LineNumb, Linestation, LinestationSFWhse as SFWhse, LSStartDate, PlanEndDate, LinestatOrdStat, FlrStock,
                SupplySys, SupplyType, SupplyCode, QtyToBeBkfl, RqmtOrigin 
  FROM
  (
    SELECT  TOP 100 PERCENT A.Company AS OpenOrdersCompany,
                  B.Company AS LineStationCompany, A.Item AS OpenOrdersItem, B.Item AS LineStationItem,
                  Case when A.AssyOrd = B.AssyOrd Then B.AssyOrd Else 'nomatch' End AS OpenOrdersAssyOrd, B.AssyOrd AS LineStationAssyOrd,
                  A.AssyLine, A.LineNumb, B.LineStation, A.SFWhse AS OpenOrdersSFWhse, B.SFWhse As LineStationSFWhse, B.LSStartDate,
                  A.PlanEndDate, A.LineStatOrdStat, A.FlrStock, A.SupplySys, A.SupplyType, A.SupplyCode, B.QtyToBeBkfl, B.RqmtOrigin 
    FROM 
    ( 
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, AssyLine, LineNumb, SFWhse, PlanEndDate, LineStatOrdStat, FlrStock, SupplySys, SupplyType, SupplyCode 
      FROM C614.ASCOpenOrderDetail 
      WHERE LineStatOrdStat='Completed' AND cast(planstartdate as datetime) < getdate() - 6 
      ORDER BY Item, AssyOrd, SFWhse 
    ) A 
    RIGHT OUTER JOIN 
    (
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, LineStation, LSStartDate, SFWhse, QtyToBeBkfl, RqmtOrigin 
      FROM C614.ASCLineStation
      WHERE QtyToBeBkfl > 0 AND cast(lsstartdate as datetime) < getdate() - 6
    ORDER BY Item, AssyOrd, SFWhse 
    ) B
    ON B.Item = A.Item AND B.SFWhse = A.SFWhse AND B.AssyOrd = A.AssyOrd 
    ORDER BY B.Item, B.AssyOrd, B.SFWhse 
  ) Beavis
  WHERE Beavis.LineStationItem in (SELECT DISTINCT Item FROM C614.ASCOpenOrderDetail)
  ORDER BY Beavis.LineStationItem, Beavis.LineStationAssyOrd, Beavis.LineStationSFWhse 
) Detail

GROUP BY Detail.Company, Detail.Item, Detail.LineStation, Detail.LSStartDate, Detail.QtyToBeBkfl, Detail.RqmtOrigin, Detail.LineStationAssyOrd
ORDER BY Detail.Item, Detail.LineStationAssyOrd, Detail.FirstOfOpenOrdersSFWhse


-- ========================== Detail (returned the same number of records as the Beavis subquery 03-15-04 ======================================================

  SELECT TOP 100 PERCENT
                LineStationCompany as Company, LineStationItem as Item, OpenOrdersAssyOrd,
                LineStationAssyOrd, AssyLine, LineNumb, Linestation, LinestationSFWhse as SFWhse, LSStartDate, PlanEndDate, LinestatOrdStat, FlrStock,
                SupplySys, SupplyType, SupplyCode, QtyToBeBkfl, RqmtOrigin 
  FROM
  (
    SELECT  TOP 100 PERCENT A.Company AS OpenOrdersCompany,
                  B.Company AS LineStationCompany, A.Item AS OpenOrdersItem, B.Item AS LineStationItem,
                  Case when A.AssyOrd = B.AssyOrd Then B.AssyOrd Else 'nomatch' End AS OpenOrdersAssyOrd, B.AssyOrd AS LineStationAssyOrd,
                  A.AssyLine, A.LineNumb, B.LineStation, A.SFWhse AS OpenOrdersSFWhse, B.SFWhse As LineStationSFWhse, B.LSStartDate,
                  A.PlanEndDate, A.LineStatOrdStat, A.FlrStock, A.SupplySys, A.SupplyType, A.SupplyCode, B.QtyToBeBkfl, B.RqmtOrigin 
    FROM 
    ( 
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, AssyLine, LineNumb, SFWhse, PlanEndDate, LineStatOrdStat, FlrStock, SupplySys, SupplyType, SupplyCode 
      FROM C614.ASCOpenOrderDetail 
      WHERE LineStatOrdStat='Completed' AND cast(planstartdate as datetime) < getdate() - 6 
      ORDER BY Item, AssyOrd, SFWhse 
    ) A 
    RIGHT OUTER JOIN 
    (
      SELECT TOP 100 PERCENT
                    Company, Item, AssyOrd, LineStation, LSStartDate, SFWhse, QtyToBeBkfl, RqmtOrigin 
      FROM C614.ASCLineStation
      WHERE QtyToBeBkfl > 0 AND cast(lsstartdate as datetime) < getdate() - 6
    ORDER BY Item, AssyOrd, SFWhse 
    ) B
    ON B.Item = A.Item AND B.AssyOrd = A.AssyOrd AND B.SFWhse = A.SFWhse
    ORDER BY B.Item, B.AssyOrd, B.SFWhse 
  ) Beavis
  INNER JOIN   
  (
    Select Distinct Item
    From
    (
      Select Item From C614.ASCOpenOrderDetail
      Union All
      Select Item 
      From C614.ASCLineStation
      Where Item Not In 
      (
        Select a.Item
        from C614.ASCLineStation a
        Inner Join C614.ASCOpenOrderDetail  b
        On a.Item = b.Item AND a.AssyOrd = b.AssyOrd
      ) 
    ) PTNList    
  ) VanDreesen
  ON Beavis.LineStationItem = VanDreesen.Item
  ORDER BY Beavis.LineStationItem, Beavis.LineStationAssyOrd, Beavis.LineStationSFWhse