SQL - Returned Total Monthly Column Values

Question

I have this SQL query that I use to search for Trial Balance transfers to each account per month.

/* Fixed Assets 1 */ SELECT * FROM ( SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount' FROM OACT T0 LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] WHERE T0.AcctCode LIKE '111%' AND T0.Levels = 5 AND (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,{?AsAtDate}), 0) AND {?AsAtDate}) GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q PIVOT ( SUM(Amount) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS query 

This returns the following results since June:

I would like to include cumulative monthly results for all rows so that row 1 result should be:

 1, 111110, Building Gross Value, 633604.23, 637764.23, 645313.03, 649061.78, 651097.78, 651097.78, 651397.78 

I did not succeed in this. How to change the query to achieve this?

Additional data:

Request:

 SELECT CAST(T0.TaxDate AS Date), T0.Account, T1.AcctName, T0.Debit, T0.Credit FROM JDT1 T0 INNER JOIN OACT T1 ON T0.Account = T1.AcctCode WHERE T0.[Account] = '111110' 

Return:

Answer 1

You need an external selection in which you add your columns to another:

 SELECT AcctCode AS 'SAP Code', AcctName AS 'Description', col1 AS '1', col1 + col2 AS '2', col1 + col2 + col3 AS '3', ... FROM ( SELECT AcctCode, AcctName, [1] AS col1, [2] AS col2, [3] AS col3, ... FROM ( SELECT T0.AcctCode, T0.AcctName, MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount' FROM OACT T0 LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] WHERE T0.AcctCode LIKE '111%' AND T0.Levels = 5 AND (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,{?AsAtDate}), 0) AND {?AsAtDate}) GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q PIVOT ( SUM(Amount) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS query ) 

Answer 2

The most direct way is to dump the results of the pivot table into a temporary table or, if the data set is quite small, a table variable. Then just use UNION to get your cumulative results and subcategories from PIVOT. The following is an example of using a table variable.

 /* Fixed Assets 1 */ SELECT * INTO #FixedAssets FROM ( SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount' FROM OACT T0 LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] WHERE T0.AcctCode LIKE '111%' AND T0.Levels = 5 AND (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,{?AsAtDate}), 0) AND {?AsAtDate}) GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q PIVOT ( SUM(Amount) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS query GO SELECT [SAP Code] = '111110' , [Description] = 'Building Gross Value' , [1] = SUM([1]) , [2] = SUM([2]) , [3] = SUM([3]) , [4] = SUM([4]) , [5] = SUM([5]) , [6] = SUM([6]) , [7] = SUM([7]) , [8] = SUM([8]) , [9] = SUM([9]) , [10] = SUM([10]) , [11] = SUM([11]) , [12] = SUM([12]) UNION SELECT * FROM #FixedAssets ORDER BY 1 

Answer 3

If I understand your question correctly, you need cumulative monthly results on the same line as comma-separated delimiters.

 create table a (dateval date, acc int,accname varchar(100), debit decimal(10,2)) insert into a values ('20150630', 111110, 'Building Gross Value', 633604.230), ('20150731', 111110, 'Building Gross Value', 2760.000000), ('20150730', 111110, 'Building Gross Value', 1400.000000), ('20150808', 111110, 'BUIIdlngGrossValue', 1890.00), ('20150811', 111110, 'BUIIdlnanossValue', 180.00), ('20150811', 111110, 'Building Gross Value', 375.000000), ('20150819', 111110, 'Building Gross Value', 2622.200000), ('20150821', 111110, 'Building Gross Value', 360.000000), ('20150822', 111110, 'Building Gross Value', 21.600000), ('20150824', 111110, 'Building Gross Value', 100.000000), ('20150825', 111110, 'Building Gross Value', 770.000000), ('20150829', 111110, 'Building Gross Value', 100.000000), ('20150831', 111110, 'Building Gross Value', 340.000000), ('20150831', 111110, 'Building Gross Value', 790.000000), ('20150924', 111110, 'Building Gross Value' ,918.750000), ('20150928', 111110, 'Building Gross Value', 2830.000000), ('20151005', 111110, 'Building Gross Value', 1411.000000), ('20151023', 111110, 'Building Gross Value', 625.000000) 

Now the query below will give you the results on the same line as commas.

 with cte as ( select * from ( select a.dateval,b.acc,b.accname,sum(b.debit) as debit,row_number() over (partition by year(a.dateval),month(a.dateval) order by a.dateval desc) as rn from (select distinct dateval from a) as a inner join a as b on a.dateval >= b.dateval group by a.dateval,b.acc,b.accname ) as a where rn = 1 ) select acc,accname,stuff((select ',' + cast(debit as varchar(1000)) from cte as b where a.acc = b.acc for xml path ('')),1,1,'') from cte as a group by acc,accname