Inner Quartile Range with ASP?

Thread: Inner Quartile Range with ASP?

1. WoG
Junior Member
Join Date
Dec 1969
Posts
14

Inner Quartile Range with ASP?

I&#039;m querying to get an array of numbers. I need to find the "Middle %50" of that array of numbers. Is there a way to find the Inner Quartile Range using an ASP function? I cannot find any math functions that would do this.<BR><BR>Could I do it manually? Sort the values, split the array up, split it again to find the "Middle %50"?<BR><BR>Thanks,<BR>Jesse

2. Senior Member
Join Date
Dec 1969
Posts
3,195

Can't you write your query

to only the results you really want? In other words only return the &#039;Inner Quartile Range&#039; as your resultset. ; )<BR><BR>Are we missing something here?

3. WoG
Junior Member
Join Date
Dec 1969
Posts
14

RE: Can't you write your query

Well, I can query for the whole array of numbers I want, but TSQL doesnt have any Inner Quartile Range functions, I guess I could sort the array through the SQL call, but I still would need to divide the array in half, and divide both halves in half and get the midpoint to those quartiles...<BR><BR>I don&#039;t think you can call that directly...unless I misunderstand you...

4. Senior Member
Join Date
Dec 1969
Posts
3,195

Can you show us an clear example

of what you are doing with some sample SQL?<BR><BR>I have a pretty good guess, but I&#039;d like to see it. ; )

5. WoG
Junior Member
Join Date
Dec 1969
Posts
14

RE: Can you show us an clear example

I guess I&#039;m not making myself clear enough...<BR><BR>I am trying to come up with the statistical function for returning both the upper and lower boundaries of the Inner 2 quartiles of an array filled with numbers. In effect, I want two values which represent a range that is the middle 50% of the overall range of values.<BR><BR>I don&#039;t have sample SQL, because I don&#039;t have any idea how to do this. I don&#039;t think TSQL has any functions that would be this complex, you would need to be able to split a result set up by index numbers in order to get the midpoints of the upper and lower halves.<BR><BR>My question was if anyone knew of a Function in ASP that when given an array, can give me the Inner quartile range (2 "range" values). I&#039;ve pretty much left out the possibility of doing it with any query.<BR><BR>Thanks- hopes this clears some things up.<BR>Jesse

6. Senior Member
Join Date
Dec 1969
Posts
3,195

Ok one last question

Are you ultimately getting the data for your &#039;array filled with numbers&#039; from a database table? If yes, then just show us the SQL for getting the entire set of numbers.<BR><BR>If not, where is it coming from? Input by a user?<BR><BR>Obviously you can write a script function to do this, however I don&#039;t want to steer you down the wrong path here. If its coming from the database, you can write your own database functions. There are some built-in statistical functions in T-SQL, however you can easily write your own ones too.

7. WoG
Junior Member
Join Date
Dec 1969
Posts
14

RE: Ok one last question

Yes, I am getting the data from a table.<BR><BR>Select * from table where (class = 1) AND (status = 1) AND (date = today)<BR><BR>I&#039;ve got a TSQL language refernce guide, and am not finding anything close to what I need, so I guess I would have to write my own. I guess I&#039;m just confused on how you would get SQL to order the result set, split it in half, find the midpoint, and split it in half, and remember what values are at each upper and lower boundary for each quartile...<BR><BR>Thanks for all your help.<BR>Jesse

8. Senior Member
Join Date
Dec 1969
Posts
3,195

That actually did not help, however I

think this might be a great place for you to start:<BR><BR>http://www.sqlteam.com/item.asp?ItemID=16480<BR><BR>Hope that helps.<BR>Pete

9. Senior Member
Join Date
Dec 1969
Posts
96,118

No...inappropriate to this <eom>

.

10. Senior Member
Join Date
Dec 1969
Posts
96,118

*** EASY SOLUTION *** ... if...

*IF* your array isn&#039;t too big.<BR><BR>Indeed, use ORDER BY in your SQL to get the numbers in order (much faster than sorting in VBScript).<BR><BR>Then convert the entire schmear to an array with GetRows and use simple math from there on.<BR><BR>&#060;%<BR>SQL = "SELECT myNumber, other, fields FROM myTable ORDER BY myNumber"<BR>Set RS = conn.Execute( SQL )<BR>rows = RS.GetRows( )<BR>RS.Close<BR><BR>CONST COL_NUM = 0 &#039; the myNumber column<BR>CONST COL_XXX = 1 &#039; other columns<BR>CONST COL_YYY = 2 &#039; name as appropriate for you<BR><BR>count = UBound( rows, 2 ) + 1 &#039; this is how many elements in the array<BR><BR>startAt = Int( count * 0.25 ) &#039; 25%<BR>endAt = Int( count * 0.75 ) &#039; 75%<BR><BR>For row = startAt To endAt<BR> Response.Write "Number: " & rows( COL_NUM, row ) & "&#060;br/&#062;"<BR>Next<BR>%&#062;<BR><BR>Presto.<BR><BR>Y ou may want to use ROUND instead of INT in getting the 25% and 75% points. Or use one more than the INT() value. Or or... But that&#039;s just icing on the cake. You play with it.<BR><BR>If your array is thousands and thousands of elements in size, then you&#039;ll have to find some more clever way to do this, presumably in SQL.<BR><BR><BR><BR>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•