CODEWITHSUNDEEP
sqlsql-server
C.Gilles
C.Gilles

Reputation: 3

Rolling Sum - Year by Year/Month By Month

I'm attempting to group several accounts together while keep a rolling / renewing balance. The query using one account:

SELECT FiscalPeriod, BalanceAmt,
       SUM(balanceamt) OVER(ORDER BY fiscalperiod) as BalanceAmt
FROM EpicorLive10.Erp.GLPeriodBal
WHERE FiscalYear = '2018'
AND BalanceAcct IN ('01260|0000|000')
GROUP BY fiscalperiod,BalanceAmt
ORDER BY FiscalPeriod

OUTPUT

Output1

With two accounts output:

Output2

As you can see, it doesn't group both account together..

What I want to happen is to add several accounts, sum everything / group everything within the 12 rows of fiscal period / year.

Ultimately, I'd need: All accounts

Any advice is appreciated.

--UPDATE** Current Query**  
select Datex, balanceamt into #temp 
FROM EpicorLive10.Erp.GLPeriodBal as A  
JOIN EpicorLive10.dbo.Date_Fiscal as B  
ON a.FiscalPeriod = b.Fiscal_Period and a.FiscalYear = b.Fiscal_Year   
WHERE FiscalYear = '2018'  
AND BalanceAcct IN (   
'01260|0000|000',  
'01261|0000|000',  
'01262|0000|000',  
'01263|0000|000',  
'01264|0000|000',  
'01555|0000|000',  
'01560|0000|000',  
'01245|0000|000',  
'01250|0000|000')  
GROUP BY Datex,BalanceAmt  


declare @rollup table (rollupid int identity, fiscalperiod int,     balanceamt float)  
insert @rollup  
select Datex, sum(balanceamt) from #temp  
group by Datex  
order by Datex  


declare @holding table (fiscalperiod int, balanceamt float, runningbal     float)  
declare @iterator int=0  
declare @currentbal float =0  
while @iterator<=(select max(rollupid) from @rollup) begin  
select @currentbal=@currentbal+balanceamt from @rollup where       fiscalperiod=@iterator  
insert @holding  
select fiscalperiod, balanceamt, @currentbal from  
@rollup where  
fiscalperiod=@iterator  
 set @iterator=@iterator+1  
 end  

select * from @holding  

drop table #temp

Account Table: Account Table
Date_Fiscal Table: Date_Fiscal

Upvotes: 0

Views: 79

Answers (3)

Daniel Marcus
Daniel Marcus

Reputation: 2686

select Datex, balanceamt into #temp 
FROM EpicorLive10.Erp.GLPeriodBal  A  
JOIN EpicorLive10.dbo.Date_Fiscal  B  
ON a.FiscalPeriod = b.Fiscal_Period and a.FiscalYear = b.Fiscal_Year   
WHERE FiscalYear = '2018'  
AND BalanceAcct IN (   
'01260|0000|000',  
'01261|0000|000',  
'01262|0000|000',  
'01263|0000|000',  
'01264|0000|000',  
'01555|0000|000',  
'01560|0000|000',  
'01245|0000|000',  
'01250|0000|000')  
GROUP BY Datex, BalanceAmt  


declare @rollup table (rollupid int identity, Datex datetime, balanceamt float)  
insert @rollup  
select Datex, sum(balanceamt) from #temp  
group by Datex  
order by Datex  


declare @holding table (Datex datetime, balanceamt float, runningbal float)  
declare @iterator int=1  
declare @currentbal float =0  
while @iterator<=(select max(rollupid) from @rollup) begin  
select @currentbal=@currentbal+balanceamt from @rollup where @iterator=rollupid   
insert @holding  
select Datex, balanceamt, @currentbal from  
@rollup where @iterator=rollupid     
 set @iterator=@iterator+1  
 end  

select * from @holding  

drop table #temp

Upvotes: 0

AntDC
AntDC

Reputation: 1907

SELECT FiscalPeriod, Sum(BalanceAmt),
       SUM(balanceamt) OVER(ORDER BY fiscalperiod  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as BalanceAmt
FROM EpicorLive10.Erp.GLPeriodBal
WHERE FiscalYear = '2018'
AND BalanceAcct IN ('01260|0000|000')
GROUP BY fiscalperiod,BalanceAmt
ORDER BY FiscalPeriod

What are the accounts? Are thet 01260, 0000 & 000? If yes your IN would be ('01260', '0000', '000') This is untested but I took it from a similar one I have.

Hope it helps

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Here is how you use window functions with aggregation:

SELECT FiscalPeriod, BalanceAmt,
       SUM(SUM(balanceamt)) OVER (ORDER BY fiscalperiod) as BalanceAmt
FROM EpicorLive10.Erp.GLPeriodBal
WHERE FiscalYear = '2018' AND BalanceAcct IN ('01260|0000|000')
GROUP BY fiscalperiod
ORDER BY FiscalPeriod;

Note that your IN looks strange. It looks more like regular expression, which SQL Server does not support.

The SUM(SUM()) looks strange at first, but you'll get used to it quickly enough. The inner SUM() is from the aggregation. The outer SUM() is for the window function.

Upvotes: 1

Related Questions