January 13, 2009 at 6:17 pm
Hi Guys
Here is my sample table
Year Month DoctorNo Expenditure
2000 1 1Q $2000
2000 2 2F $12345
.
.
.
2008 4 3ER $6788
NOw I need to calculate the moving average and plot it on a graph
where I need to show the Expenditure by each doctor every month for 3 yrs.
Please help with the formula.
Thanks
January 13, 2009 at 6:35 pm
There are a lot of different "moving averages", how do you want yours to be defined?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 6:46 pm
It should be the cost(expenditure) divided by the no. of Doctors at each month.
HOpe this helps
January 13, 2009 at 7:19 pm
Ah, well, that's not a moving average, that's just an average, hang on...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 7:29 pm
OK, like this:
Select [Year], [Month], SUM(Expenditure)/Count(Distinct Doctor)
From YourTable
Group By [Year], [Month]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 7:47 pm
Hi friend
I tried this query already.
May be i could not explain the requirements very well. My apologies...
Its basically the expenditure divided by 12 at each month . its basically the rolling average.
WE want to know what was the doctor_count and expenditure at each month
Hpe it helps
January 13, 2009 at 8:11 pm
OK, here's, that:
Select [Year]
, [Month]
, Count(*) as [Monthly Records]
, SUM(Expenditure) as [Monthly Expenditures]
, Count(Distinct Doctor) as [Total Doctors]
From YourTable
Group By [Year], [Month]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply