[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1125
  • Last Modified:

Calculate Average Balance

I am looking for a way to calculate a average balance based on daily balances, or a better suggestion would be fine.
I have a balance field in my table and the date field, is there a way where i can add balance field for each month from the 1st to end of the month and then divide it by the number of days to get a accurate average balance?

any help would be really appreciated!!!!

0
fahVB
Asked:
fahVB
  • 4
  • 3
  • 2
2 Solutions
 
chapmandewCommented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance)
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
0
 
fahVBAuthor Commented:
Thanks, this gives me the balance of the account, not the average balance...

0
 
RiteshShahCommented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
compute avg(avgs)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
chapmandewCommented:
Im pretty sure it gives you the avg balance for the time period....
0
 
fahVBAuthor Commented:
Ritesh, its throwing this error

Msg 207, Level 16, State 1, Line 4
Invalid column name 'avgs'.
Msg 411, Level 16, State 1, Line 4
COMPUTE clause #1, aggregate expression #1 is not in the select list.

0
 
RiteshShahCommented:
try this one:

select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
compute avg(avg(balance))
0
 
fahVBAuthor Commented:
getting same ending balance instead of average balance. is this query be modified so i can enter the starting date and ending date of the month...

0
 
RiteshShahCommented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
where datefield>='your start date' and datefield<='your end date'
group by datepart(mm, datefield) , datepart(yy, datefield)
0
 
fahVBAuthor Commented:
Thank you all...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now