daily pastebin goal
18%
SHARE
TWEET

Untitled

a guest May 16th, 2018 101 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.     SELECT tmpValue.CompanyID,
  3.     tmpValue.DistributorID,
  4.     tmpValue.InventoryID,
  5.     tmpValue.YTDThisYear,
  6.     tmpValue.YTDLastYear,
  7.     tmpValue.MTDActual,
  8.     tmpValue.PercentThisYear,
  9.     tmpValue.P3M,
  10.     tmpValue.P6M,
  11.     tmpSubcat.Hierachy3ID,
  12.     tmpSubcat.Hierachy3CD,
  13.     tmpSubcat.Hierachy3Descr
  14.     INTO #tmpTotalValue
  15.      FROM
  16.       (
  17.          
  18.             SELECT InventoryID,
  19.                    Hierachy3ID,
  20.                    Hierachy3CD,
  21.                    Hierachy3Descr
  22.             FROM MRCDMS..DMSViewInventoryItem
  23.             WHERE CompanyID = @CompanyID
  24.                   AND Hierachy3ID IS NOT NULL
  25.         ) tmpSubcat
  26.           LEFT JOIN
  27.     (
  28.         --------------------------Start Get YTD This Year, and YTD Last Year, MTD Actual--------------------
  29.         --Get YTD this year
  30.    
  31.         SELECT
  32.                tmpThisYear.CompanyID,
  33.                tmpThisYear.DistributorID,
  34.                tmpThisYear.InventoryID,
  35.                tmpThisYear.YTDThisYear,
  36.                tmpLastYear.YTDLastYear,
  37.                   --If devide with zero, result = 0
  38.                (COALESCE(tmpLastYear.YTDLastYear /
  39.                 NULLIF(tmpThisYear.YTDThisYear, 0), 0)) PercentThisYear,
  40.                tmpMTD.MTDActual,
  41.                tmpP3M.P3M,
  42.                tmpP6M.P6M
  43.          
  44.   FROM
  45.         (
  46.             SELECT CompanyID,
  47.                    DistributorID,
  48.                    InventoryID,
  49.                    SUM(OrderQty) AS YTDThisYear
  50.             FROM MRCBaseline..DMSBLSales
  51.             WHERE OrderDate >= @_FromDate
  52.                   AND OrderDate <= @_ToDate
  53.             GROUP BY CompanyID,
  54.                      InventoryID,
  55.                      DistributorID
  56.         ) tmpThisYear
  57.             LEFT JOIN
  58.             --2. Get YTD Last year
  59.             (
  60.                 SELECT CompanyID,
  61.                        DistributorID,
  62.                        InventoryID,
  63.                        SUM(OrderQty) AS YTDLastYear
  64.                 FROM MRCBaseline..DMSBLSales
  65.               WHERE OrderDate >= DATEADD(YEAR, -1, @_FromDate)
  66.                   AND OrderDate <=  DATEADD(YEAR, -1, @_ToDate)
  67.                 GROUP BY CompanyID,
  68.                          InventoryID,
  69.                          DistributorID
  70.             ) tmpLastYear
  71.                 ON tmpLastYear.CompanyID = tmpThisYear.CompanyID
  72.                    AND tmpLastYear.DistributorID = tmpThisYear.DistributorID
  73.                    AND tmpLastYear.InventoryID = tmpThisYear.InventoryID
  74.             LEFT JOIN
  75.             --3.Get MTD this month
  76.             (
  77.                 SELECT CompanyID,
  78.                        DistributorID,
  79.                        InventoryID,
  80.                        SUM(OrderQty) AS MTDActual
  81.                 FROM MRCBaseline..DMSBLSales
  82.                 WHERE OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0)
  83.                       AND OrderDate <= @_ToDate
  84.                 GROUP BY CompanyID,
  85.                          InventoryID,
  86.                          DistributorID
  87.             ) tmpMTD
  88.                 ON tmpMTD.CompanyID = tmpThisYear.CompanyID
  89.                    AND tmpMTD.DistributorID = tmpThisYear.DistributorID
  90.                    AND tmpMTD.InventoryID = tmpThisYear.InventoryID
  91.             --4.Get P3M
  92.             LEFT JOIN
  93.             (
  94.                 SELECT CompanyID,
  95.                        DistributorID,
  96.                        InventoryID,
  97.                        (SUM(OrderQty) / 3) AS P3M
  98.                 FROM MRCBaseline..DMSBLSales
  99.                 WHERE OrderDate >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0))
  100.                       AND OrderDate <= @_ToDate
  101.                 GROUP BY CompanyID,
  102.                          InventoryID,
  103.                          DistributorID
  104.             ) tmpP3M
  105.                 ON tmpP3M.CompanyID = tmpThisYear.CompanyID
  106.                    AND tmpP3M.DistributorID = tmpThisYear.DistributorID
  107.                    AND tmpP3M.InventoryID = tmpThisYear.InventoryID
  108.             --4.Get P6M
  109.             LEFT JOIN
  110.             (
  111.                 SELECT CompanyID,
  112.                        DistributorID,
  113.                        InventoryID,
  114.                        (SUM(OrderQty) / 6) AS P6M
  115.                 FROM MRCBaseline..DMSBLSales
  116.                 WHERE OrderDate >= DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0))
  117.                       AND OrderDate <= @_ToDate
  118.                 GROUP BY CompanyID,
  119.                          InventoryID,
  120.                          DistributorID
  121.             ) tmpP6M
  122.                 ON tmpP6M.CompanyID = tmpThisYear.CompanyID
  123.                    AND tmpP6M.DistributorID = tmpThisYear.DistributorID
  124.                    AND tmpP6M.InventoryID = tmpThisYear.InventoryID
  125.     ) tmpValue   ON tmpSubcat.InventoryID = tmpValue.InventoryID;
  126.         --Get Inventory by sub-cat
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top