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 |
|
sillylady
Starting Member
13 Posts |
 Posted - 2007-05-29 : 08:48:12
|
| I need to calculate Median on each calculated result from the query below. There is one Median function available in SQL2K but it is not working. Can anyone help me in this regard.------------------------------------------------------------------------SELECT INS.Code As [code],INS.FinYr as [YEAR], ' ' As [FIRE BUSINESS], (INSRev.FI_NetPremLessIns / INSRev.FI_GrPremium) * 100 As [Rention Ratio],(INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100 As [Claim Ratio], ((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpenses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100 As [Expense Ratio],((INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100)+(((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpe nses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100) As [Combine Ratio],(INSRev.FI_ClosingBal/INSRev.FI_NetClaimPaid) As [Unexpired Risk Reserve to Net Claim(x)],(INSRev.FI_MgmtExpenses/INSRev.FI_AdjNetPremium)*100 As [Management Expenses to Adj. Net Premium],(INSRev.FI_AgencyCommPaid/INSRev.FI_AdjNetPremium)*100 As [Agency Commissioned to Adj. Net Premium] FROM(InsuranceGen As INS LEFT JOIN INSURANCEGen As INS1 ON (INS1.FinYr=INS.FinYr-1 AND INS.Code=INS1.Code)) LEFT JOIN INSRevGen as INSRev ON (INS.Code=INSRev.Code AND INS.FinYr=INSRev.FinYr) WHERE INS.Code IN ('ABC1','ABC2','ABC3') AND INS.FinYr=2005 ORDER BY INS.Code, INS.FinYr ----------------------------------------------------------------------Thanks |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-29 : 09:48:26
|
| Are you using SQL 2000 or 2005?Also, what do you mean by this:>>There is one Median function available in SQL2K but it is not working. ???? What "on median function" are you talking about, and what do you mean by "it is not working" ??- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-29 : 09:52:21
|
| This may help you: [url]http://www.sqlmag.com/Article/ArticleID/49827/49827.html[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
sillylady
Starting Member
13 Posts |
Posted - 2007-05-29 : 23:56:06
|
quote:
Originally posted by jsmith8858
Are you using SQL 2000 or 2005?Also, what do you mean by this:>>There is one Median function available in SQL2K but it is not working. ???? What "on median function" are you talking about, and what do you mean by "it is not working" ??- Jeffhttp://weblogs.sqlteam.com/JeffS
I am using SQL2000 and query in my 1st post is passed to ADO recordset. In SQL2000 help I saw median function and when i tried it, but it's not working. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-30 : 00:03:52
|
| In SQL 2000, the Median function is available only for Analysis Services and not for regular T-SQL.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-31 : 03:55:44
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76113Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Calculate Median!!!