salve,
si, scusa, non avevo ragionato in termini di multivalue per il raggruppamento...
puoi allora pre-raggruppare i risultati desiderati in 2 sub query da mettere poi in join tra loro per ottenere un unico risultato...
tipicamente eseguirei l'operazione con 2 Common Table Expression, dove per ognuna di esse ottieni il risultato raggruppato come desideri, che poi metti in FULL OUTER JOIN tra loro 2...
semplificando oltremisura, qualche cosa simile a
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
Id int NOT NULL,
Data date,
Causale varchar(5),
mtscrap int
);
INSERT INTO dbo.t
VALUES (1, '2014-01-01', 'c1', 1), (1, '2014-06-01', 'c1', 1), (1, '2014-08-01', 'c1', 1),
(1, '2014-01-01', 'c2', 1), (1, '2014-06-01', 'c2', 1), (1, '2014-08-01', 'c2', 1);
INSERT INTO dbo.t
VALUES (2, '2014-02-01', 'c1', 1), (2, '2014-04-01', 'c1', 1), (2, '2014-08-01', 'c1', 1),
(2, '2014-02-01', 'c2', 1), (2, '2014-04-01', 'c2', 1), (2, '2014-08-01', 'c2', 1);
GO
PRINT 'proiezione raggruppata in filtro date e causali per un range unico di date';
SELECT t.Id
, SUM(t.mtscrap) AS [Scarto Gen]
, SUM(CASE WHEN t.Causale = 'c1' THEN t.mtscrap ELSE 0 END) AS [Scarto c1]
, SUM(CASE WHEN t.Causale = 'c2' THEN t.mtscrap ELSE 0 END) AS [Scarto c2]
FROM dbo.t t
WHERE t.Data BETWEEN '2014-01-01' AND '2014-06-01'
AND t.Causale LIKE 'c%'
GROUP BY t.Id;
PRINT 'range 2';
WITH cteOtherRange AS (
SELECT t.Id
, SUM(t.mtscrap) AS [Scarto Gen]
, SUM(CASE WHEN t.Causale = 'c1' THEN t.mtscrap ELSE 0 END) AS [Scarto c1]
, SUM(CASE WHEN t.Causale = 'c2' THEN t.mtscrap ELSE 0 END) AS [Scarto c2]
FROM dbo.t t
WHERE t.Data BETWEEN '2014-06-02' AND '2014-12-31'
AND t.Causale LIKE 'c%'
GROUP BY t.Id
)
SELECT * FROM cteOtherRange;
PRINT 'combinazione';
WITH cteOtherRange AS (
SELECT t.Id
, SUM(t.mtscrap) AS [Scarto Gen]
, SUM(CASE WHEN t.Causale = 'c1' THEN t.mtscrap ELSE 0 END) AS [Scarto c1]
, SUM(CASE WHEN t.Causale = 'c2' THEN t.mtscrap ELSE 0 END) AS [Scarto c2]
FROM dbo.t t
WHERE t.Data BETWEEN '2014-06-02' AND '2014-12-31'
AND t.Causale LIKE 'c%'
GROUP BY t.Id
),
cteBase AS (
SELECT t.Id
, SUM(t.mtscrap) AS [Scarto Gen]
, SUM(CASE WHEN t.Causale = 'c1' THEN t.mtscrap ELSE 0 END) AS [Scarto c1]
, SUM(CASE WHEN t.Causale = 'c2' THEN t.mtscrap ELSE 0 END) AS [Scarto c2]
FROM dbo.t t
WHERE t.Data BETWEEN '2014-01-01' AND '2014-06-01'
AND t.Causale LIKE 'c%'
GROUP BY t.Id
)
SELECT
ISNULL(b.Id, o.Id) AS [Id], b.[Scarto Gen], b.[Scarto c1], b.[Scarto c2]
, o.[Scarto Gen] AS [Scarto Gen -2], o.[Scarto c1] AS [Scarto c1 -2], o.[Scarto c2] AS [Scarto c2 -2]
FROM cteBase b
FULL OUTER JOIN cteOtherRange o ON o.Id = b.Id
GO
DROP TABLE dbo.t;
--<-----------
proiezione raggruppata in filtro date e causali per un range unico di date
Id Scarto Gen Scarto c1 Scarto c2
----------- ----------- ----------- -----------
1 4 2 2
2 4 2 2
range 2
Id Scarto Gen Scarto c1 Scarto c2
----------- ----------- ----------- -----------
1 2 1 1
2 2 1 1
combinazione
Id Scarto Gen Scarto c1 Scarto c2 Scarto Gen -2 Scarto c1 -2 Scarto c2 -2
----------- ----------- ----------- ----------- ------------- ------------ ------------
1 4 2 2 2 1 1
2 4 2 2 2 1 1
saluti
