Weighted 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

Weighted Average

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-10-23 : 10:57:48

Hey everyone . . .

I have been working on this for a day and a half now, and am coming up against a deadline, so I thought I would throw it out to the experts. The purpose of this query is to create a straight line for a graph to display a baseline average, but I am not calculating the correct type of average. . . In the below query I am calculating two types of averages. In the sub-query I am calculating the weighted average and in the main top level select I am doing a simple average calculation. What I need to do is calculate a weighted average in the top level as well, and I am struggling with how I can get the appropriate values for the calculation.

The weighted average should look somthing like this:

(([Average Elapsed Time]*Transactions)/Transactions)

Similar to this snippet:

(avg(duration) *count(duration)/count(duration))

Any thoughts on how I can integrate the weighted average into the main select as well?

Thanks Everyone!

SELECT Client_Mnemonic, AVG(CASE WHEN Seq <= (4) and BarType != 'Trending' THEN [Average Elapsed Time] ELSE NULL END) OVER (PARTITION BY Client_Mnemonic,Group_type) AS AvgTime,[Work Week],Group_type
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq,
([Average Elapsed Time] * [Transactions]) as "Total"
FROM
(
SELECT Client_Mnemonic, round((avg(duration) *count(duration)/count(duration)),0) As "Average Elapsed Time", count(workflow_id) as "Transactions",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) As "Work Week", Group_Type
FROM workflow_data
WHERE
Client_mnemonic = ('XXXX_XX') and (Group_type = 'Pilot') and 
duration <= 1800 and
(
definition = 'Orders - Individual Order Search'  and datepart(dw, workflow_data.Start_Time) not in (1,7)
)
GROUP BY datepart(ww,workflow_data.start_time), Client_Mnemonic, 
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type 
)q
)r
inner join 
(
Select [Work_Week], 
CASE WHEN Seq1 <= 4 THEN 'Baseline' 
             ELSE 'Trending'
           END as "BarType"
FROM
(
SELECT CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) as "Work_Week",
ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) AS Seq1
FROM workflow_data
where client_mnemonic = ('XXXX_XX') and group_type = 'Pilot' 
group by CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) 
)m
)i
on r.[Work Week]=i.Work_Week

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:33:21

hmm...looks like you're taking average over an average. Whats the purpose of this?

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

stwp86
Starting Member

42 Posts

Posted - 2012-10-24 : 10:31:36

That is correct. Basic description is as follows: We are using two queries to populate a trending chart (a combo bar and line chart). The first query populates an average time week by week (bar on a bar chart) and this query adds the average amount of time it took for our "Baseline" period (time before our changes took place) and places a straight line across all week so that we can easily see if our changes had a positive or negative effect on the system.

The issue is that I want to do a weighted average because transaction week by week are variant, so I want the high volume weeks to carry more weight. That is the basic purpose of what I am trying to accomplish here. So, that is the reason for the request to help calculate the weighted average in the above queries main select statement.

Does that help clear it up?

Thanks for the help!

Travis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 11:46:22

ok...Whats does transaction represent? it would help if you could give some sample data to help us understand what these fields mean

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources