joolsw
asked on
SQL 2012 Report Builder 3.0 query
I have a query (see below) which reports on product sales, and I need to add products set to a status of discontinued.
The field is StockStatusID and the table is product.stockstatus.
I need to link the field to the table.
The query at the moment is as below. Anyone out there that can help?
Thank you in advance.
Jools
WITH SalesSumm_CTE(ProdID,
DepotID,
CurrentUnits,
CurrentValue,
Mth1Units,
Mth1Value,
Mth2Units,
Mth2Value,
Mth3Units,
Mth3Value,
Mth4Units,
Mth4Value,
Mth5Units,
Mth5Value,
Mth6Units,
Mth6Value,
Mth7Units,
Mth7Value,
Mth8Units,
Mth8Value,
Mth9Units,
Mth9Value,
Mth10Units,
Mth10Value,
Mth11Units,
Mth11Value,
Mth12Units,
Mth12Value,
Mth13Units,
Mth13Value) AS
(
select Product.ProdID ,
MonthlyProdSales.DepotID ,
SUM(CASE WHEN Mths.PeriodLabel = 1 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS CurrentUnits,
SUM(CASE WHEN Mths.PeriodLabel = 1 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS CurrentValue,
SUM(CASE WHEN Mths.PeriodLabel = 2 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth1Units,
SUM(CASE WHEN Mths.PeriodLabel = 2 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth1Value,
SUM(CASE WHEN Mths.PeriodLabel = 3 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth2Units,
SUM(CASE WHEN Mths.PeriodLabel = 3 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth2Value,
SUM(CASE WHEN Mths.PeriodLabel = 4 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth3Units,
SUM(CASE WHEN Mths.PeriodLabel = 4 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth3Value,
SUM(CASE WHEN Mths.PeriodLabel = 5 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth4Units,
SUM(CASE WHEN Mths.PeriodLabel = 5 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth4Value,
SUM(CASE WHEN Mths.PeriodLabel = 6 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth5Units,
SUM(CASE WHEN Mths.PeriodLabel = 6 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth5Value,
SUM(CASE WHEN Mths.PeriodLabel = 7 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth6Units,
SUM(CASE WHEN Mths.PeriodLabel = 7 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth6Value,
SUM(CASE WHEN Mths.PeriodLabel = 8 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth7Units,
SUM(CASE WHEN Mths.PeriodLabel = 8 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth7Value,
SUM(CASE WHEN Mths.PeriodLabel = 9 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth8Units,
SUM(CASE WHEN Mths.PeriodLabel = 9 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth8Value,
SUM(CASE WHEN Mths.PeriodLabel = 10 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth9Units,
SUM(CASE WHEN Mths.PeriodLabel = 10 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth9Value,
SUM(CASE WHEN Mths.PeriodLabel = 11 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth10Units,
SUM(CASE WHEN Mths.PeriodLabel = 11 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth10Value,
SUM(CASE WHEN Mths.PeriodLabel = 12 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth11Units,
SUM(CASE WHEN Mths.PeriodLabel = 12 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth11Value,
SUM(CASE WHEN Mths.PeriodLabel = 13 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth12Units,
SUM(CASE WHEN Mths.PeriodLabel = 13 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth12Value,
SUM(CASE WHEN Mths.PeriodLabel = 14 THEN MonthlyProdSales.SalesUnit s ELSE 0 END) AS Mth14Units,
SUM(CASE WHEN Mths.PeriodLabel = 14 THEN MonthlyProdSales.SalesValu e ELSE 0 END) AS Mth14Value
FROM dbo.[LastXPeriods](@CompID , 14) Mths
INNER JOIN GeneralTables.PeriodMonth
ON PeriodMonth.PerMonthID = Mths.ID
INNER JOIN GeneralTables.PeriodYear
ON PeriodMonth.YearID = PeriodYear.YearID
INNER JOIN GeneralTables.StockDepot
ON StockDepot.CompID = PeriodYear.CompID
INNER JOIN Product.Product
ON StockDepot.CompID = Product.CompID
INNER JOIN summsales.MonthlyProdSales
on StockDepot.DepotID = MonthlyProdSales.DepotID
AND Product.ProdID = MonthlyProdSales.ProdID
AND MonthlyProdSales.PeriodYea r = PeriodYear.PeriodYear
AND MonthlyProdSales.PeriodNum ber = PeriodMonth.PeriodNumber
WHERE StockDepot.DepotID = @DepotID
AND StockDepot.RecordStatus = 20
AND Product.RecordStatus = 20
GROUP BY Product.ProdID, MonthlyProdSales.DepotID
)
SELECT VWProductDetail.SupAccount
, VWProductDetail.SupName
, VWProductDetail.ProdName
, VWProductDetail.Pack
, VWProductDetail.PipCode
, VWProductDetail.CatalogNo
, VWProductDetail.SaleClassD esc
, VWProductDetail.StkClassDe sc
, VWProductDetail.PurClassDe sc
, VWStockDetail.StockStatusI D
, vwStockDetail.TradePrice
, vwStockDetail.AvgCostPrice
, vwStockDetail.CostPrice
, vwStockDetail.RetailPrice
, vwStockDetail.StkQty
, vwStockDetail.OnOrderQty
, MthSales.*
FROM product.VWProductDetail
INNER JOIN SalesSumm_CTE MthSales
ON VWProductDetail.ProdID = MthSales.ProdID
INNER JOIN Product.vwStockDetail
ON MthSales.ProdID = vwStockDetail.ProdID
AND MthSales.DepotID = vwStockDetail.DepotID
WHERE VWProductDetail.CompID = @CompID
AND VWProductDetail.RecordStat us = 20
AND VWProductDetail.PurClassID IN (@PurClassIDs)
AND vwStockDetail.RecordStatus = 20
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.ProdGroupI D IN (@MajorGroup)
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.subGroupID IN (@MinorGroup)
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.StatusDesc <> 'Discontinued'
The field is StockStatusID and the table is product.stockstatus.
I need to link the field to the table.
The query at the moment is as below. Anyone out there that can help?
Thank you in advance.
Jools
WITH SalesSumm_CTE(ProdID,
DepotID,
CurrentUnits,
CurrentValue,
Mth1Units,
Mth1Value,
Mth2Units,
Mth2Value,
Mth3Units,
Mth3Value,
Mth4Units,
Mth4Value,
Mth5Units,
Mth5Value,
Mth6Units,
Mth6Value,
Mth7Units,
Mth7Value,
Mth8Units,
Mth8Value,
Mth9Units,
Mth9Value,
Mth10Units,
Mth10Value,
Mth11Units,
Mth11Value,
Mth12Units,
Mth12Value,
Mth13Units,
Mth13Value) AS
(
select Product.ProdID ,
MonthlyProdSales.DepotID ,
SUM(CASE WHEN Mths.PeriodLabel = 1 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 1 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 2 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 2 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 3 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 3 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 4 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 4 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 5 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 5 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 6 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 6 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 7 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 7 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 8 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 8 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 9 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 9 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 10 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 10 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 11 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 11 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 12 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 12 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 13 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 13 THEN MonthlyProdSales.SalesValu
SUM(CASE WHEN Mths.PeriodLabel = 14 THEN MonthlyProdSales.SalesUnit
SUM(CASE WHEN Mths.PeriodLabel = 14 THEN MonthlyProdSales.SalesValu
FROM dbo.[LastXPeriods](@CompID
INNER JOIN GeneralTables.PeriodMonth
ON PeriodMonth.PerMonthID = Mths.ID
INNER JOIN GeneralTables.PeriodYear
ON PeriodMonth.YearID = PeriodYear.YearID
INNER JOIN GeneralTables.StockDepot
ON StockDepot.CompID = PeriodYear.CompID
INNER JOIN Product.Product
ON StockDepot.CompID = Product.CompID
INNER JOIN summsales.MonthlyProdSales
on StockDepot.DepotID = MonthlyProdSales.DepotID
AND Product.ProdID = MonthlyProdSales.ProdID
AND MonthlyProdSales.PeriodYea
AND MonthlyProdSales.PeriodNum
WHERE StockDepot.DepotID = @DepotID
AND StockDepot.RecordStatus = 20
AND Product.RecordStatus = 20
GROUP BY Product.ProdID, MonthlyProdSales.DepotID
)
SELECT VWProductDetail.SupAccount
, VWProductDetail.SupName
, VWProductDetail.ProdName
, VWProductDetail.Pack
, VWProductDetail.PipCode
, VWProductDetail.CatalogNo
, VWProductDetail.SaleClassD
, VWProductDetail.StkClassDe
, VWProductDetail.PurClassDe
, VWStockDetail.StockStatusI
, vwStockDetail.TradePrice
, vwStockDetail.AvgCostPrice
, vwStockDetail.CostPrice
, vwStockDetail.RetailPrice
, vwStockDetail.StkQty
, vwStockDetail.OnOrderQty
, MthSales.*
FROM product.VWProductDetail
INNER JOIN SalesSumm_CTE MthSales
ON VWProductDetail.ProdID = MthSales.ProdID
INNER JOIN Product.vwStockDetail
ON MthSales.ProdID = vwStockDetail.ProdID
AND MthSales.DepotID = vwStockDetail.DepotID
WHERE VWProductDetail.CompID = @CompID
AND VWProductDetail.RecordStat
AND VWProductDetail.PurClassID
AND vwStockDetail.RecordStatus
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.ProdGroupI
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.subGroupID
AND VWProductDetail.SupID IN (@SupID)
AND VWProductDetail.StatusDesc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER