After P.CardCode, I need a column showing todays value (e.g. in the pivot table No18 as today is the 18/04/2017) minus yesterdays value. Can anyone help?
SELECT
CASE P.[CardCode]
WHEN 'STR-00001' THEN 'WEBSALES'
WHEN 'STR-00002' THEN 'TELEPHONE'
WHEN 'STR-00003' THEN 'SHOP'
ELSE 'WHS' END AS 'Cost Centre',
ISNULL([1],0) as [1], ISNULL([2],0) as [2], ISNULL([3],0) as [3], ISNULL([4],0) as [4], ISNULL([5],0) as [5], ISNULL([6],0) as [6], ISNULL([7],0) as [7], ISNULL([8],0) as [8], ISNULL([9],0) as [9], ISNULL([10],0) as [10], ISNULL([11],0) as [11], ISNULL([12],0) as [12], ISNULL([13],0) as [13], ISNULL([14],0) as [14], ISNULL([15],0) as [15], ISNULL([16],0) as [16], ISNULL([17],0) as [17], ISNULL([18],0) as [18], ISNULL([19],0) as [19], ISNULL([20],0) as [20], ISNULL([21],0) as [21], ISNULL([22],0) as [22], ISNULL([23],0) as [23], ISNULL([24],0) as [24], ISNULL([25],0) as [25], ISNULL([26],0) as [26], ISNULL([27],0) as [27], ISNULL([28],0) as [28], ISNULL([29],0) as [29], ISNULL([30],0) as [30], ISNULL([31],0) as [31]
FROM (
SELECT T0.CardCode, (T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL], DATEPART(DAY, T1.DocDate) as [DAY] FROM dbo.OCRD T0 LEFT JOIN OINV T1 ON T1.CardCode = T0.CardCode AND MONTH(T1.DocDate) = Month(GetDate()) INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry WHERE T0.CardType = 'C'
UNION
SELECT T0.CardCode, -(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL], DATEPART(DAY, T1.DocDate) as [DAY] FROM OCRD T0 LEFT JOIN dbo.ORIN T1 ON T1.CardCode = T0.CardCode AND MONTH(T1.DocDate) = Month(GetDate()) INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry WHERE T0.CardType = 'C'
) S
PIVOT (SUM(S.[BAL]) FOR [DAY] IN ([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])) P
ORDER BY CASE P.[CardCode]
WHEN 'STR-00001' THEN '0.1'
WHEN 'STR-00002' THEN '0.2'
WHEN 'STR-00003' THEN '0.3'
ELSE '0.4' END