Rolling Total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rolling Total

Author

Topic

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 08:50:11

Hi all

Could anyone help me please?

I have a query below that displays a £ balance for an 102040 account across period numbers. Unfortunately this only gives me a balance IN that period number, but I want a script that also gives me a running balance at the end of a period number. EG if my balances were £10 in P1 and £20 in P2 I would like another column called rolling total showing £10 in P1 and £30 in P2.

Here is my script:

SELECT NLAccountPeriodBalanceVw.ActualValue, NLAccountPeriodBalanceVw.AccountNumber, NLAccountPeriodBalanceVw.SYSAccountingPeriodID
FROM Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw NLAccountPeriodBalanceVw
WHERE (NLAccountPeriodBalanceVw.AccountNumber='102040')

Hopefully makes sense, would appreciate any help from the geniuses on here!

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 09:46:20

[code]SELECT NLAccountPeriodBalanceVw.ActualValue, NLAccountPeriodBalanceVw.AccountNumber, NLAccountPeriodBalanceVw.SYSAccountingPeriodID,
r.RunningValue
FROM Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw NLAccountPeriodBalanceVw
cross apply
(
select RunningValue = sum(ActualValue)
from Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw x
where x.AccountNumber = NLAccountPeriodBalanceVw.AccountNumber
and x.SYSAccountingPeriodID <= NLAccountPeriodBalanceVw.SYSAccountingPeriodID
) r
WHERE (NLAccountPeriodBalanceVw.AccountNumber='102040')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 10:00:35

Hi KH Thats brilliant.

Thanks I would also like to join in here:

NLNominalAccount.AccountName, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment,

WHERE NLNominalAccount.AccountNumber = NLAccountPeriodBalanceVw.AccountNumber

Could you also help with this please

I really appreciate your help

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 10:07:50

[code]
SELECT NLAccountPeriodBalanceVw.ActualValue,
NLAccountPeriodBalanceVw.AccountNumber,
NLAccountPeriodBalanceVw.SYSAccountingPeriodID,
NLNominalAccount.AccountName,
NLNominalAccount.AccountCostCentre,
NLNominalAccount.AccountDepartment,
r.RunningValue
FROM Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw NLAccountPeriodBalanceVw
INNER JOIN Sage6DGMDLive.dbo.NLNominalAccount NLNominalAccount
ON NLAccountPeriodBalanceVw.AccountNumber = NLNominalAccount.AccountNumber
cross apply
(
select RunningValue = sum(ActualValue)
from Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw x
where x.AccountNumber = NLAccountPeriodBalanceVw.AccountNumber
and x.SYSAccountingPeriodID <= NLAccountPeriodBalanceVw.SYSAccountingPeriodID
) r
WHERE (NLAccountPeriodBalanceVw.AccountNumber='102040')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 15:11:43

Hi KH

Thanks so much that is perfect.

Really appreciate your help. This works great.

Any chance you could breakdown what this code is doing (for a novice) so I can start to learn and understand this for the future?

Cheers
Mark

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 15:20:51

For example...

What is r. doing?

And Cross Apply? never come across this before.

Thanks
Mark

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 16:03:08

Hi KH

I would also like to add into this statement

SYSAccountingPeriod.PeriodNumber,
SYSAccountingPeriod.SYSFinancialYearID,
SYSAccountingPeriod.StartDate,
SYSAccountingPeriod.EndDate

where

SYSAccountingPeriod.SYSAccountingPeriodID = NLAccountPeriodBalanceVw.SYSAccountingPeriodID

Can you also help me with this?

Thanks very much
Mark

In2Minds
Starting Member

17 Posts

Posted - 2014-02-04 : 16:20:06

Hi KH

OK I have added the above into the code, it runs fine but I have now lost my rolling total.

Could you help me on where I have gone wrong please?

SELECT NLAccountPeriodBalanceVw.ActualValue,
NLAccountPeriodBalanceVw.AccountNumber,
NLAccountPeriodBalanceVw.SYSAccountingPeriodID,
NLNominalAccount.AccountName,
NLNominalAccount.AccountCostCentre,
NLNominalAccount.AccountDepartment,
SYSAccountingPeriod.PeriodNumber,
SYSAccountingPeriod.SYSFinancialYearID,
SYSAccountingPeriod.StartDate,
SYSAccountingPeriod.EndDate

FROM Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw NLAccountPeriodBalanceVw
INNER JOIN Sage6DGMDLive.dbo.NLNominalAccount NLNominalAccount
ON NLAccountPeriodBalanceVw.AccountNumber = NLNominalAccount.AccountNumber
INNER JOIN Sage6DGMDLive.dbo.SYSAccountingPeriod SYSAccountingPeriod
ON SYSAccountingPeriod.SYSAccountingPeriodID = NLAccountPeriodBalanceVw.SYSAccountingPeriodID

cross apply
(
select RunningValue = sum(ActualValue)
from Sage6DGMDLive.dbo.NLAccountPeriodBalanceVw x
where x.AccountNumber = NLAccountPeriodBalanceVw.AccountNumber
and x.SYSAccountingPeriodID <= NLAccountPeriodBalanceVw.SYSAccountingPeriodID
) r

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 18:40:34

quote:
What is r. doing?

r is a alias given to the derived table

quote:
And Cross Apply?

see here

quote:
OK I have added the above into the code, it runs fine but I have now lost my rolling total.

You have removed the "r.RunningValue" from the result. Just add it back and it should be fine

KH
[spoiler]Time is always against us[/spoiler]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-05 : 01:37:06

Hi KH

Thanks so much for all of your help, can't thank you enough!

Works fine now, failed to notice I missed the running total in the last script.

Cheers again, all the best
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-05 : 01:54:06

welcome

KH
[spoiler]Time is always against us[/spoiler]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-05 : 17:12:26

Hi KH

Need more help if possible as that previous query did not quite work for what I needed.

Sorry to keep bothering you but am trying to get a running total on 'Total' below (which itself is a sum of (NLAccountPeriodValue.AdjustmentAfterYearEndClose+NLAccountPeriodValue.ActualValue)

Have tried to amend the previous code you did above but can't get it to work properly.

Any chance you could help me out again?

Thanks
Mark

SELECT NLAccountPeriodValue.SYSAccountingPeriodID, SYSAccountingPeriod.PeriodNumber, SYSFinancialYear.SYSFinancialYearID, SYSAccountingPeriod.StartDate, SYSAccountingPeriod.EndDate, NLNominalAccount.AccountNumber, NLAccountPeriodValue.BudgetValue, NLAccountPeriodValue.ActualValue, NLAccountPeriodValue.AdjustmentAfterYearEndClose, Sum(NLAccountPeriodValue.AdjustmentAfterYearEndClose+NLAccountPeriodValue.ActualValue) AS 'Total'
FROM Sage200SDBIS.dbo.NLAccountPeriodValue NLAccountPeriodValue, Sage200SDBIS.dbo.NLNominalAccount NLNominalAccount, Sage200SDBIS.dbo.SYSAccountingPeriod SYSAccountingPeriod, Sage200SDBIS.dbo.SYSFinancialYear SYSFinancialYear
WHERE NLNominalAccount.NLNominalAccountID = NLAccountPeriodValue.NLNominalAccountID AND SYSAccountingPeriod.SYSAccountingPeriodID = NLAccountPeriodValue.SYSAccountingPeriodID AND SYSFinancialYear.SYSFinancialYearID = SYSAccountingPeriod.SYSFinancialYearID
GROUP BY NLAccountPeriodValue.SYSAccountingPeriodID, SYSAccountingPeriod.PeriodNumber, SYSFinancialYear.SYSFinancialYearID, SYSAccountingPeriod.StartDate, SYSAccountingPeriod.EndDate, NLNominalAccount.AccountNumber, NLAccountPeriodValue.BudgetValue, NLAccountPeriodValue.ActualValue, NLAccountPeriodValue.AdjustmentAfterYearEndClose
HAVING (NLNominalAccount.AccountNumber='106018')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-06 : 04:27:32

try

; with cte as
(
	SELECT 		NLAccountPeriodValue.SYSAccountingPeriodID, 
			SYSAccountingPeriod.PeriodNumber, 
			SYSFinancialYear.SYSFinancialYearID, 
			SYSAccountingPeriod.StartDate, 	
			SYSAccountingPeriod.EndDate, 
			NLNominalAccount.AccountNumber, 
			NLAccountPeriodValue.BudgetValue, 
			NLAccountPeriodValue.ActualValue, 
			NLAccountPeriodValue.AdjustmentAfterYearEndClose, 
			Sum(NLAccountPeriodValue.AdjustmentAfterYearEndClose+NLAccountPeriodValue.ActualValue) AS 'Total'
	FROM 		Sage200SDBIS.dbo.NLAccountPeriodValue NLAccountPeriodValue
	INNER JOIN 	Sage200SDBIS.dbo.NLNominalAccount NLNominalAccount	
		ON	NLNominalAccount.NLNominalAccountID = NLAccountPeriodValue.NLNominalAccountID 
	INNER JOIN	Sage200SDBIS.dbo.SYSAccountingPeriod SYSAccountingPeriod
		ON	SYSAccountingPeriod.SYSAccountingPeriodID = NLAccountPeriodValue.SYSAccountingPeriodID 
	INNER JOIN	Sage200SDBIS.dbo.SYSFinancialYear SYSFinancialYear
		ON	SYSFinancialYear.SYSFinancialYearID = SYSAccountingPeriod.SYSFinancialYearID
	WHERE 	        NLNominalAccount.AccountNumber = '106018'	
	GROUP BY 	NLAccountPeriodValue.SYSAccountingPeriodID, 
			SYSAccountingPeriod.PeriodNumber, 
			SYSFinancialYear.SYSFinancialYearID, 
			SYSAccountingPeriod.StartDate, 
			SYSAccountingPeriod.EndDate, 
			NLNominalAccount.AccountNumber, 
			NLAccountPeriodValue.BudgetValue, 
			NLAccountPeriodValue.ActualValue, 
			NLAccountPeriodValue.AdjustmentAfterYearEndClose
)
SELECT	c.SYSAccountingPeriodID, 
	c.PeriodNumber, 
	c.SYSFinancialYearID, 
	c.StartDate, 	
	c.EndDate, 
	c.AccountNumber, 
	c.BudgetValue, 
	c.ActualValue, 
	c.AdjustmentAfterYearEndClose
	c.Total,
	r.RunningTotal
FROM	cte c
CROSS APPLY
(
	SELECT	SUM(Total) AS RunningTotal
	FROM	cte x
	WHERE	x.AccountNumber		= c.AccountNumber
	AND	x.SYSAccountingPeriodID	<= c.SYSAccountingPeriodID
) r

KH
[spoiler]Time is always against us[/spoiler]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-06 : 06:16:07

Hi KH

Thanks so much for coming back to me

Getting the error message

"No Column Name was specified for Column 10 of cte"

Any ideas?

Thanks

In2Minds
Starting Member

17 Posts

Posted - 2014-02-06 : 13:04:31

Hi KH

Thanks so much for coming back to me

Getting the error message

"No Column Name was specified for Column 10 of cte"

Any ideas?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources