CODEWITHSUNDEEP
sql-server-2012
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

SQL - Returning Cumulative Monthly Column Values

I have this SQL query that I use to find Trial Balance movements per 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 from June :

enter image description here

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

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

I have not been successful in achieving this. How do I modify the query to achieve this?

Additional data:

The query:

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'

Returns:

enter image description here

Upvotes: 5

Views: 1000

Answers (3)

dey
dey

Reputation: 3140

You need outer select 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
)

Upvotes: 3

sam
sam

Reputation: 1304

If I understood your question correctly , you need cumulative monthly results in one row with comma separated Debits values 

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 below mentioned query will give you results in one row with comma separated values..

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

Upvotes: 0

Steve Mangiameli
Steve Mangiameli

Reputation: 688

The most direct way is to dump the results of the pivot into a temporary table or, if the data set is fairly small, a table variable. Then just use UNION to get your cumulative results and sub categories from the PIVOT. Here is an example 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

Upvotes: 0

Related Questions