SELECT tmpValue.CompanyID,
	tmpValue.DistributorID,
	tmpValue.InventoryID,
	tmpValue.YTDThisYear,
	tmpValue.YTDLastYear,
	tmpValue.MTDActual,
	tmpValue.PercentThisYear,
	tmpValue.P3M,
	tmpValue.P6M,
	tmpSubcat.Hierachy3ID,
	tmpSubcat.Hierachy3CD,
	tmpSubcat.Hierachy3Descr
	INTO #tmpTotalValue
	 FROM
	  (

            SELECT InventoryID,
                   Hierachy3ID,
                   Hierachy3CD,
                   Hierachy3Descr
            FROM MRCDMS..DMSViewInventoryItem
            WHERE CompanyID = @CompanyID
                  AND Hierachy3ID IS NOT NULL
        ) tmpSubcat
          LEFT JOIN
    (
        --------------------------Start Get YTD This Year, and YTD Last Year, MTD Actual--------------------
        --Get YTD this year

        SELECT
               tmpThisYear.CompanyID,
               tmpThisYear.DistributorID,
               tmpThisYear.InventoryID,
               tmpThisYear.YTDThisYear,
               tmpLastYear.YTDLastYear,
			      --If devide with zero, result = 0
               (COALESCE(tmpLastYear.YTDLastYear /
			    NULLIF(tmpThisYear.YTDThisYear, 0), 0)) PercentThisYear,
			   tmpMTD.MTDActual,
               tmpP3M.P3M,
               tmpP6M.P6M

  FROM
        (
            SELECT CompanyID,
                   DistributorID,
                   InventoryID,
                   SUM(OrderQty) AS YTDThisYear
            FROM MRCBaseline..DMSBLSales
            WHERE OrderDate >= @_FromDate
                  AND OrderDate <= @_ToDate
            GROUP BY CompanyID,
                     InventoryID,
                     DistributorID
        ) tmpThisYear
            LEFT JOIN
            --2. Get YTD Last year
            (
                SELECT CompanyID,
                       DistributorID,
                       InventoryID,
                       SUM(OrderQty) AS YTDLastYear
                FROM MRCBaseline..DMSBLSales
              WHERE OrderDate >= DATEADD(YEAR, -1, @_FromDate)
                  AND OrderDate <=  DATEADD(YEAR, -1, @_ToDate)
                GROUP BY CompanyID,
                         InventoryID,
                         DistributorID
            ) tmpLastYear
                ON tmpLastYear.CompanyID = tmpThisYear.CompanyID
                   AND tmpLastYear.DistributorID = tmpThisYear.DistributorID
                   AND tmpLastYear.InventoryID = tmpThisYear.InventoryID
            LEFT JOIN
            --3.Get MTD this month
            (
                SELECT CompanyID,
                       DistributorID,
                       InventoryID,
                       SUM(OrderQty) AS MTDActual
                FROM MRCBaseline..DMSBLSales
                WHERE OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0)
                      AND OrderDate <= @_ToDate
                GROUP BY CompanyID,
                         InventoryID,
                         DistributorID
            ) tmpMTD
                ON tmpMTD.CompanyID = tmpThisYear.CompanyID
                   AND tmpMTD.DistributorID = tmpThisYear.DistributorID
                   AND tmpMTD.InventoryID = tmpThisYear.InventoryID
            --4.Get P3M
            LEFT JOIN
            (
                SELECT CompanyID,
                       DistributorID,
                       InventoryID,
                       (SUM(OrderQty) / 3) AS P3M
                FROM MRCBaseline..DMSBLSales
                WHERE OrderDate >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0))
                      AND OrderDate <= @_ToDate
                GROUP BY CompanyID,
                         InventoryID,
                         DistributorID
            ) tmpP3M
                ON tmpP3M.CompanyID = tmpThisYear.CompanyID
                   AND tmpP3M.DistributorID = tmpThisYear.DistributorID
                   AND tmpP3M.InventoryID = tmpThisYear.InventoryID
            --4.Get P6M
            LEFT JOIN
            (
                SELECT CompanyID,
                       DistributorID,
                       InventoryID,
                       (SUM(OrderQty) / 6) AS P6M
                FROM MRCBaseline..DMSBLSales
                WHERE OrderDate >= DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0))
                      AND OrderDate <= @_ToDate
                GROUP BY CompanyID,
                         InventoryID,
                         DistributorID
            ) tmpP6M
                ON tmpP6M.CompanyID = tmpThisYear.CompanyID
                   AND tmpP6M.DistributorID = tmpThisYear.DistributorID
                   AND tmpP6M.InventoryID = tmpThisYear.InventoryID
    ) tmpValue   ON tmpSubcat.InventoryID = tmpValue.InventoryID;
        --Get Inventory by sub-cat