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.
| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-08-19 : 18:46:22
|
| I have some code that will duplicate the excel trimmean function...and I have some code that produces an aggregate table...How do I blend these together to provide a trimmean? is there a way to put this into a function or 'subroutine' and just call it?...*****This is the trimmean code:Select SUM(k) / 4.0 as TriMeanFROM (SELECT AVG(esrdaystoresolve) AS kFROM (SELECT TOP 1 WITH TIES esrdaystoresolve FROM RemedyChangeTaskDataMart ORDER BY ABS(10. - 100. * ROW_NUMBER() OVER (ORDER BY esrdaystoresolve) / COUNT(*) OVER ()))AS dUNION ALLSELECT AVG(esrdaystoresolve) * 2. FROM (SELECT TOP 1 WITH TIES esrdaystoresolve FROM RemedyChangeTaskDataMartORDER BY ABS(50. - 100. * ROW_NUMBER() OVER (ORDER BY esrdaystoresolve) / COUNT(*) OVER ()))AS dUNION ALLSELECT AVG(esrdaystoresolve)FROM (SELECT TOP 1 WITH TIES esrdaystoresolve FROM RemedyChangeTaskDataMartORDER BY ABS(90. - 100. * ROW_NUMBER() OVER (ORDER BY esrdaystoresolve) / COUNT(*) OVER ())) AS d)AS e******This is the aggregate code:select substring(CONVERT(VARCHAR(11), CHG_Actual_End_Date, 106),4,11)as ReportPeriod, CHG_category,CHG_item,sum(case when datediff(hh,CHG_Create_Date,CHG_Actual_End_Date)/24.<=30 then 1 else 0 end) as OnTimeTickets,count(*)as TotalTickets,(sum(case when datediff(hh,CHG_Create_Date,CHG_Actual_End_Date)/24.<=30 then 1. else 0. end))/count(*) as PercentOntime,avg(datediff(hh,CHG_Create_Date,CHG_Actual_End_Date)/24.) as AvgInterval,getdate()as EnteredFrom RemedyChangeTaskDataMart WHERE CHG_Create_Date>='2008-07-01 00:00:00' and CHG_Actual_End_Date is not nullgroup by substring(CONVERT(VARCHAR(11), CHG_Actual_End_Date, 106),4,11),CHG_category,CHG_itemorder by CHG_category,CHG_item,substring(CONVERT(VARCHAR(11), CHG_Actual_End_Date, 106),4,11) |
|
|
|
|
|
|
|
Doing custom Aggregates..?..