Trimean Formula? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Trimean Formula?

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-08-05 : 16:26:27

In excel, we have an analysis that uses the trimeam function (TRIMEAN – Returns the trimmed mean of a selected interior range of a data set.)

Does sql provide, or can I build?...a trimean function?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-05 : 16:43:10

No, it doesn't. Yes, you can.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 02:48:47

The trimean value is computed by adding the 25th percentile plus twice the 50th percentile plus the 75th percentile and dividing by four.

The 25th, 50th, and 75th percentile of the dataset

{50, 50, 50, 50, 50, 50, 50, 50, 50, 50,
51, 51, 51, 51, 51, 51, 51, 51, 52, 53,
53, 53, 55, 55, 55, 55, 56, 56, 56, 58,
58, 59, 60, 60, 61, 63, 63, 63, 64, 67,
67, 69, 70, 70, 75, 77, 78, 80, 85, 103}

are 51, 55, and 63 respectively.
Therefore, the trimean is computed (52 + 2 * 55 + 63) / 4, which equals 56.

N 56°04'39.26"
E 12°55'05.63"

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-08-06 : 09:15:03

peso...excellent...now...how do I turn it into a function...and apply it in a ...mmm..select statement?..

I've gotten far enough to understand I have a sorted list of values...for a monthly period, for example...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 09:44:28

[code]DECLARE @Sample TABLE
(
Data INT
)

INSERT @Sample
SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL
SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL
SELECT 51 UNION ALL SELECT 51 UNION ALL SELECT 51 UNION ALL SELECT 51 UNION ALL SELECT 51 UNION ALL
SELECT 51 UNION ALL SELECT 51 UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL
SELECT 53 UNION ALL SELECT 53 UNION ALL SELECT 55 UNION ALL SELECT 55 UNION ALL SELECT 55 UNION ALL
SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 56 UNION ALL SELECT 56 UNION ALL SELECT 58 UNION ALL
SELECT 58 UNION ALL SELECT 59 UNION ALL SELECT 60 UNION ALL SELECT 60 UNION ALL SELECT 61 UNION ALL
SELECT 63 UNION ALL SELECT 63 UNION ALL SELECT 63 UNION ALL SELECT 64 UNION ALL SELECT 67 UNION ALL
SELECT 67 UNION ALL SELECT 69 UNION ALL SELECT 70 UNION ALL SELECT 70 UNION ALL SELECT 75 UNION ALL
SELECT 77 UNION ALL SELECT 78 UNION ALL SELECT 80 UNION ALL SELECT 85 UNION ALL SELECT 103

SELECT SUM(k) / 4.0
FROM (
SELECT AVG(1.0E * Data) AS k
FROM (
SELECT TOP 1 WITH TIES
Data
FROM @Sample
ORDER BY ABS(25 - 100 * ROW_NUMBER() OVER (ORDER BY Data) / COUNT(*) OVER ())
) AS d

UNION ALL

SELECT AVG(1.0E * Data) * 2
FROM (
SELECT TOP 1 WITH TIES
Data
FROM @Sample
ORDER BY ABS(50 - 100 * ROW_NUMBER() OVER (ORDER BY Data) / COUNT(*) OVER ())
) AS d

UNION ALL

SELECT AVG(1.0E * Data)
FROM (
SELECT TOP 1 WITH TIES
Data
FROM @Sample
ORDER BY ABS(75 - 100 * ROW_NUMBER() OVER (ORDER BY Data) / COUNT(*) OVER ())
) AS d
) AS e[/code]

N 56°04'39.26"
E 12°55'05.63"

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-08-06 : 10:45:27

Peso... EXACTLY...worked like a champ!..thank you!!!

I validated using the excel trimmean function...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 10:48:52

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-08-06 : 18:34:36

and you thought you were done!...HA!..

Peso, Data...in my case...is the difference between a creation date..and a resolved date...
I use datediff(hh,Create_Time,Resolved_Time)/24. which gives me the number of day it took to close the order.

if I do a straight replace on Data with 'datediff(hh,Create_Time,resolved_time)/24.'....I get
Msg 8155, Level 16, State 2, Line 1 No column was specified for column 1 of 'd'.

what am I missing?

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-08-06 : 18:49:25

obtw...the tri-mean is one aggregate metric related to a month's worth of orders... so I ACTUALLY need to fit it in a a field in the following aggregation

select substring(CONVERT(VARCHAR(11), Resolved_Time, 106),4,11)as ReportPeriod
, region,category,type,item
,sum(case when datediff(hh,Create_Time,resolved_time)/24.<=5 then 1 else 0 end) as OnTimeTickets
,count(*)as TotalTickets
,(sum(case when datediff(hh,Create_Time,resolved_time)/24.<=5 then 1. else 0. end))/count(*) as PercentOntime
,avg(datediff(hh,Create_Time,resolved_time)/24.) as AvgInterval
,getdate()as Entered

from RemedyHelpDeskDataMart
WHERE Create_Time>='2008-07-01 00:00:00' and Resolved_Time is not null
group by substring(CONVERT(VARCHAR(11), Resolved_Time, 106),4,11),region,category,type,item
order by category,item,substring(CONVERT(VARCHAR(11), Resolved_Time, 106),4,11)

the above produces a record per month, per agency, per application, per item....

ReportPeriod region category type item OnTimeTickets TotalTickets PercentOntime AvgInterval Entered
Aug 2008 HFS-HEALTH AND FAMILY SERVICES Agency Application HFS Web Apps MEDI System 244 246 0.991869 0.263550 2009-08-06 17:46:27.073

then the tri-mean would be one more field that would give me the tri-mean of the 246 orders

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources