Hope someone can help, I don't even know if what I want is possible.
I have the following code
WITH CTE AS
(
SELECT *, ISNULL ([JAN], 0) + ISNULL ([FEB], 0) + ISNULL ([MAR], 0) + ISNULL ([APR], 0) + ISNULL ([MAY], 0) + ISNULL ([JUN], 0) + ISNULL ([JUL], 0) + ISNULL ([AUG], 0) + ISNULL ([SEP], 0) + ISNULL ([OCT], 0) + ISNULL ([NOV], 0) + ISNULL ([DEC], 0) AS TOTAL FROM
(SELECT CASE
WHEN MONTH ([DateInstructionReceived]) = '1' THEN 'JAN'
WHEN MONTH ([DateInstructionReceived]) = '2' THEN 'FEB'
WHEN MONTH ([DateInstructionReceived]) = '3' THEN 'MAR'
WHEN MONTH ([DateInstructionReceived]) = '4' THEN 'APR'
WHEN MONTH ([DateInstructionReceived]) = '5' THEN 'MAY'
WHEN MONTH ([DateInstructionReceived]) = '6' THEN 'JUN'
WHEN MONTH ([DateInstructionReceived]) = '7' THEN 'JUL'
WHEN MONTH ([DateInstructionReceived]) = '8' THEN 'AUG'
WHEN MONTH ([DateInstructionReceived]) = '9' THEN 'SEP'
WHEN MONTH ([DateInstructionReceived]) = '10' THEN 'OCT'
WHEN MONTH ([DateInstructionReceived]) = '11' THEN 'NOV'
WHEN MONTH ([DateInstructionReceived]) = '12' THEN 'DEC'
END AS [FileDate],
[CaseNumber],
[Solicitor]
FROM [dbo].[Instructions]
WHERE [LoadedDate] >= (SELECT CAST (GETDATE() AS DATE))
AND YEAR ([DateInstructionReceived]) = '2018') AS i
PIVOT
(
COUNT (CaseNumber)
FOR [FileDate] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
)
AS pvt
)
SELECT * FROM CTE
UNION
SELECT 'ZZTOTAL', SUM (JAN), SUM (FEB), SUM (MAR), SUM (APR), SUM (MAY), SUM (JUN), SUM (JUL), SUM (AUG), SUM (SEP), SUM (OCT), SUM (NOV), SUM (DEC), SUM (TOTAL)
FROM CTE
This works perfectly.
What I'd like to do is add in another column to the CTE (product) and then be able to pivot on both solicitor and product (as you'd be able to do easily in Excel).
Any guidance appreciated as always.