SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rolling Total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  08:50:11  Show Profile  Reply with Quote
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)

Singapore
17587 Posts

Posted - 02/04/2014 :  09:46:20  Show Profile  Reply with Quote
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')



KH
Time is always against us

Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  10:00:35  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/04/2014 :  10:07:50  Show Profile  Reply with Quote

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')



KH
Time is always against us

Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  15:11:43  Show Profile  Reply with Quote
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



Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  15:20:51  Show Profile  Reply with Quote
For example...

What is r. doing?

And Cross Apply? never come across this before.

Thanks
Mark
Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  16:03:08  Show Profile  Reply with Quote
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


Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/04/2014 :  16:20:06  Show Profile  Reply with Quote
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











Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/04/2014 :  18:40:34  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/05/2014 :  01:37:06  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/05/2014 :  01:54:06  Show Profile  Reply with Quote
welcome


KH
Time is always against us

Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/05/2014 :  17:12:26  Show Profile  Reply with Quote
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')






















Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/06/2014 :  04:27:32  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/06/2014 :  06:16:07  Show Profile  Reply with Quote
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
Go to Top of Page

In2Minds
Starting Member

17 Posts

Posted - 02/06/2014 :  13:04:31  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000