Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement