I have the following query;
SELECT STOCK_CODE,
dbo.manu_STOCK.DESCRIPTION,
QTY_IN_STOCK,
Quantity,
ForecastDate
FROM [FS25-w2k8\SQLEXPRESS].sagel50_46772.dbo.SalesForecastLines AS SalesForecastLines1
INNER JOIN dbo.manu_STOCK
ON SalesForecastLines1.ProductCode = dbo.manu_STOCK.STOCK_CODE
This brings up the following information;
STOCK_CODE DESCRIPTION QTY_INSTOCK Quantity ForecastDate
523 gel 12 10 01/08/2014
523 gel 12 10 08/08/2014
I want to be able to modify the query so that it displays the following formation
Stock Code Description WK1 WK2
523 gel 22 22
So it will sum qty in stock and quantity on the first date and the column will be called wk1, second week - wk2 etc.
Can you advise on this please?
IF you want to pivot the results for 52 weeks, you can use the following query. This will pivot data for 52 weeks. This is just concept, I could not test this query.
SELECT * FROM
( SELECT
dbo.manu_STOCK.STOCK_CODE AS [Stock Code],
dbo.manu_STOCK.DESCRIPTION,
DATEPART(WEEK,[Date]) Wk,
QTY_IN_STOCK + Quantity AS Stock
FROM [FS25-w2k8\SQLEXPRESS].sagel50_46772.dbo.SalesForecastLines AS SalesForecastLines1
INNER JOIN dbo.manu_STOCK
ON SalesForecastLines1.ProductCode = dbo.manu_STOCK.STOCK_CODE
) AS Source
PIVOT
(
SUM(Stock)
FOR WK IN
([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
,[31],[32],[33],[34],[35],[36],[37],[38],[39],[40]
,[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
,[51],[52])
) AS PVT ;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments