T-SQL – Pivot por semana

Actualmente estoy tratando de crear un T-SQL, que se ejecuta a través de una list de entregas en una tabla, y los agrupa por el cliente y el depósito, por lo que cada fila será

Cliente, Depósito, Valor total (sum de una columna llamada Tasa)

Sin embargo, el cliente desea que el valor total se divida en las últimas 9 semanas, por lo que, en lugar del valor total, tendremos columnas como esta:

22/01/2012 29/01/2012 05/02/2012 12/02/2012 19/02/2012 26/02/2012 04/03/2012 11/03/2012 18/03/2012 

Por supuesto, las dates cambiarán para cuando ejecuten la consulta; solo serán las últimas 9 semanas. También quieren una columna para el Promedio de todos estos.

Entiendo que pivot puede ayudarme pero estoy un poco perplejo sobre cómo hacer esto. Aquí está mi consulta actual:

 SELECT d.Name AS 'Depot, s.Name AS 'Customer', SUM(c.Rates) AS 'Total Value' FROM Deliveries AS c INNER JOIN Account AS s ON c.Customer = s.ID INNER JOIN Depots AS d ON c.CollectionDepot = d.Letter GROUP BY d.Name, s.Name 

¡Muchas gracias!

EDITAR: Aquí hay una captura de pantalla de los datos actualmente: no necesitaremos la columna "total" al final, solo para mostrarte. La columna 'Fecha' está presente en la tabla Entregas y se llama TripDate

enter image description here

Debería utilizar la palabra key PIVOT que está disponible en su versión de SQL Server. He explicado cómo debería verse su consulta, por supuesto que se requerirán algunos ajustes, ya que es difícil de probar sin tener una copy de sus datos.

  SELECT Depots.Name AS 'Depot', Account.Name, '22/01/2012', '29/01/2012', '05/02/2012', '12/02/2012', FROM (SELECT Name, FROM Deliveries INNER JOIN Account ON Deliveries.Customer = Account.ID INNER JOIN Depots ON Account.CollectionDepot) AS Source PIVOT ( SUM(Deliveries.Rates) FOR Date IN ('22/01/2012', '29/01/2012', '05/02/2012', '12/02/2012') ) AS 'Pivot Table' 

Como reference, puede usar esto como una guía:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Sin saber su información exacta. Es difícil pnetworkingecir lo que está recibiendo. Pero puedo darte una sugerencia de una solución.

Estructura de la tabla

 CREATE TABLE Deliveries ( Customer INT, CollectionDepot INT, Rates FLOAT, TripDate DATETIME ) CREATE TABLE Account ( Name VARCHAR(100), ID INT ) CREATE TABLE Depots ( Name VARCHAR(100), Letter INT ) 

Datos de testing

 INSERT INTO Deliveries VALUES (1,1,452,GETDATE()-10), (1,1,800,GETDATE()-30), (1,1,7895,GETDATE()-2), (1,1,451,GETDATE()-2), (1,1,478,GETDATE()-89), (1,1,4512,GETDATE()-31), (1,1,782,GETDATE()-20), (1,1,652,GETDATE()-5), (1,1,752,GETDATE()-452) INSERT INTO Account VALUES ('Customer 1',1) INSERT INTO Depots VALUES ('Depot 1',1) 

Tabla que contiene los ranges y la date formateada

 CREATE TABLE #tmp ( StartDate DATETIME, EndDate DATETIME, FomatedDate VARCHAR(20) ) 

Calcule los intervalos de dates

 ;WITH Nbrs ( n ) AS ( SELECT 0 UNION ALL SELECT 1+n FROM Nbrs WHERE n < 8 ) INSERT INTO #tmp SELECT DATEADD(WEEK,-n-1,GETDATE()), DATEADD(WEEK,-n,GETDATE()), convert(varchar, DATEADD(WEEK,-n,GETDATE()), 112) FROM Nbrs ORDER BY -n 

Las columnas de date para el pivote

 DECLARE @cols VARCHAR(MAX) SELECT @cols = COALESCE(@cols + ','+QUOTENAME(FomatedDate), QUOTENAME(FomatedDate)) FROM #tmp 

Declarar algunos sql dynamics y ejecutarlos

 DECLARE @query NVARCHAR(4000)= N'SELECT * FROM ( SELECT Depots.Name AS Depot, Account.Name AS Customer, Deliveries.Rates, tmp.FomatedDate, AVG(Deliveries.Rates) OVER(PARTITION BY 1) AS Average, SUM(Deliveries.Rates) OVER(PARTITION BY 1) AS Total FROM Deliveries JOIN Account ON Deliveries.Customer = Account.ID JOIN Depots ON Deliveries.CollectionDepot = Depots.Letter JOIN #tmp AS tmp ON Deliveries.TripDate BETWEEN tmp.StartDate AND tmp.EndDate ) AS p PIVOT ( AVG(rates) FOR FomatedDate IN ('+@cols+') ) AS pvt' EXECUTE(@query) 

Y luego limpiando después de mí mismo.

 DROP TABLE Deliveries DROP TABLE Account DROP TABLE Depots DROP TABLE #tmp