Calculate the monthly average - 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

Calculate the monthly average

Author

Topic

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 14:04:36

I want to calculate the monthly average as the idea below:
Person_A has 56 tasks in 8 months where 8 months is in 2 years.
For example:
2/2011 has 5 tasks
3/2011 has 6 tasks
7/2011 has 8 tasks
9/2011 has 10 tasks
11/2011 has 5 tasks
12/2011 has 8 tasks.
4/2012 has 9 tasks
7/2012 has 5 tasks

Total task in 2 years are 56
Total months in 2 years are 8
Then Average = Total task / total month = 56/8 = 7

I did my query but I don't know how to group and sum the total months and total task.

-----------

select AssignedTo, datepart(mm,createdDateTime)as Months , count(createdDateTime) as WOPerMonth,
--sum(count(CreatedDateTime)/ datepart(mm,CreatedDateTime)) as Monthly_AVG
FROM Task
where
createddatetime between '2012-01-01 00:00:00.000' and getdate()
GROUP BY assignedTo, datepart(mm,createdDateTime)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:14:03

[code]
select AssignedTo,
SUM(taskcount)/count(distinct datename(mm,createddatetime) + datename(yy,createdDateTime)) as Monthly_AVG
FROM Task
where
createddatetime between '2012-01-01 00:00:00.000' and getdate()
GROUP BY AssignedTo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 14:20:40

I tried it, and it errors
The message is:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 14:27:30

Yeah, I just changed the data and got it.
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:54:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 15:38:53

Now I got stuck on "join" the query.
I have a query about month_AVG
I want to join with another query to get the expect result like this:

Assignee TaskOverDue MonthlyAVG
Person A 10 5
Person B 5 8
Person C 0 7
Person D 1 6

How can I go join 2 query together?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 16:10:45

what all other query returns? does it have month level info?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 16:17:48

the other query returns the number of over due work task,
Person A has 10 OD Tasks
Person B has 5 OD Tasks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 16:44:59

quote:
Originally posted by JadeV

the other query returns the number of over due work task,
Person A has 10 OD Tasks
Person B has 5 OD Tasks

then join this query to average query on persion (AssignedTo) field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 16:47:20

yes

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 17:31:31

You meant join the query to AVG query on person (AssignedTo) field,
use inner join?

select Mth_AVG.assignedTo, OD_Task.OD_DueDays, Mth_AVG.average
from
(
query average
} as Mth_AVG
inner join
(
query over due
) as OD_Task
on Mth_AVG.AssignedTo = OD_Task.AssignedTo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 17:41:13

quote:
Originally posted by JadeV

You meant join the query to AVG query on person (AssignedTo) field,
use inner join?

select Mth_AVG.assignedTo, OD_Task.OD_DueDays, Mth_AVG.average
from
(
query average
} as Mth_AVG
inner join
(
query over due
) as OD_Task
on Mth_AVG.AssignedTo = OD_Task.AssignedTo

yep... i would say left join as all persons may not have overdue tasks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 17:56:24

one problem is when I run the query after join with the period time from only year 2012, but it still count all database from year 2011 to year 2012 :(

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-31 : 18:04:00

in separate query, they run correct, but when join them, the result is not correct.
The data in a period from Jan 2012 to July 2012 always get all database from Jan 2011 to July 2012

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 22:52:03

where are you doing date filter? actually you should be doing it as where clause outside like below


select Mth_AVG.assignedTo, OD_Task.OD_DueDays, Mth_AVG.average
from
(
query average
} as Mth_AVG
inner join
(
query over due
) as OD_Task
on Mth_AVG.AssignedTo = OD_Task.AssignedTo
where createddatetime between '2012-01-01 00:00:00.000' and getdate()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-01 : 10:05:16

Yes, I did like this, but when I copy the query to Crystal Report, and run it in 1 year, then it take all database in 2 years

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 10:11:15

first run in SSMS and see whether it gives you proper results. then adapt it for crystal reports.

Also have you made it parameterised to make sure query retrieves result based on report parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-01 : 11:53:56

In SSMS, it runs correct, but in Crystal Report is not.
I created a parameter in Crystal Report with the Date in startDate and EndDate to review the period time. THen it doesn't change follow the parameter. :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 12:05:15

quote:
Originally posted by JadeV

In SSMS, it runs correct, but in Crystal Report is not.
I created a parameter in Crystal Report with the Date in startDate and EndDate to review the period time. THen it doesn't change follow the parameter. :(

did you link parameters to query?
the earlier posted query had hardcoded values so if you want it to be based on parameter the query should be like



...
where createddatetime >=@Startdate
and createdatetime<@Enddate+1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources