Find 90th Percentile Scores For Each Student
Sep 15, 2006
I am stumped on a set-based approach for this one.
A cursor approach is straightforward enough, but i want to avoid that.
Here's my table:
create table StudentScores
(
id int primary key identity(1,1),
student_id int not null,
score int not null
)
with some sample data:
insert into StudentScores (student_id, score)
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 88 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 98
What I want is, for each student, what is their 90th percentile score?
For a given single student, one possibility would be:
declare @studentid int
set @studentid = 2
select top 1 @studentid as student_id, a.score as [90th percentile score]
from
(
select top 90 percent score from StudentScores
where student_id = @studentid order by score asc
) as a
order by a.score desc
But I want this for all students, and not use a cursor.
Any ideas?
Thanks!
View 6 Replies
ADVERTISEMENT
Sep 1, 2006
I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.
The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.
For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.
Match 1 Match 2
----------------- -----------------
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0
_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332
In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.
Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?
Fernando Tubio
View 2 Replies
View Related
Mar 3, 2015
I need to find patients(MRN) who are in the 95th percentile for BMI.
create table dbo.TEST
(
MRN varchar(10),
ResultValue varchar(10)
)
insert into dbo.TEST(MRN, BMI) values( '611193','25.63')
insert into dbo.TEST(MRN, BMI) values( '128845','16.93')
[Code] .....
View 9 Replies
View Related
Aug 4, 2013
I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
View 3 Replies
View Related
Oct 9, 2007
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
View 5 Replies
View Related
Jul 12, 2004
I have a database that holds student details in one table and their scores in particular subjects in another. The tables are linked through ID. The subjects table looks like this. (example)
ID Sub1 Sub2 Sub3 Sub4 etc
1 2 4 2 6
2 5 5 1 3
3 7 4 2 5
etc
What I need to do is return a students details followed by their top 10 subjects in ascending order.
Student 1's results would look like this...
Student 1
details
Sub1 2
Sub3 2
Sub2 4
Sub4 6
This is driving me insane. Please help.
View 1 Replies
View Related
Oct 16, 2007
I have surveys that I need to add weights to and was wondering if there was a way to convert the contents of a column.
select empid, ans_for_ql,
(if ans_for_ql = A then 0, B then 3, C then 5) as weightscore,
ans_for_q2,
(if ans_for_q2 = A then 8, B then 4, C then 2, D then 1, E then 0) as weightscore
Here is what the table looks like:
empid | Ans_for_Q1 | Ans_for_Q2
1001 A C
1002 B E
And these are the possible answers and what they need to be converted to:
Weights for answers to Question1
Q1_A = 0
Q1_B = 3
Q1_C = 5
Weights for answers to Question2
Q2_A = 8
Q2_B = 4
Q2_C = 2
Q2_D = 1
Q2_E = 0
View 3 Replies
View Related
Aug 4, 2006
Dear all here:
I have a question about the cluster mining result.
In the discrimination tab shows the different between group 1 and group 2.
I just wonder how to calculate the discremination scores.
Do any expertise can answer the question?
Regards!
Jerry
View 1 Replies
View Related
Sep 30, 2014
I am trying to build a query where I want to extract the sum of the scores for each code MCC and get the percentage over the sum of all the scores over the last 90 days
select MCC, sum(score) as total from scores
(select Datediff(day, creationdate, getdate()) as Q from scores
where Datediff(day, creationdate, getdate()) <90)
group by MCC
TABLE
ID creationdate score MCC
1 2014-08-02 30 7422
. . . .
. . . .
. . . .
View 4 Replies
View Related
Jan 21, 2004
I have a table and am usuing ASP to query the database, the connection is to a MS Access table at the moment but am working to convert to SQL Server.
Question:
I need to select the last 20 records, by a date field, then from those 20 records select the 10 lowest scores.
Example is a member logs on an that member has say 80 total records in the table. Then I need to select the last 20 records entered by the date field then select the lowest 10 scores out of those 20.
I am new to more compex SQL Statements any help would be mostly appreciated!
table = HC_ID
date field = date
member_id = member
score = ScoreHC
View 14 Replies
View Related
Dec 14, 2007
How to create Percentile with MDX??
i got this function but i'm not success to follow up.
Percentile ( set, numeric_value_expr, percentile )
(set -> The set from which to get a tuple value.
numeric_value_expr -> A numeric value or an expression that returns a numeric value.
percentile -> A percentile. Must be between 0 and 100. )
Thank you
View 5 Replies
View Related
Apr 28, 2006
Hi, l would like to calculate 80% and 95% percentile of a column from my table in SQL server 2005. Does SQL server 2005 provides such built-in function, if not, how should l do? Any suggestion? (Suggest to MS : Please include percentile as a built-in function on the next services pack). Thanks and happy labor day!
View 6 Replies
View Related
Jan 13, 2008
SCORE ID PERCENTILE
0.500 1 ?
0.500 2 ?
0.450 3 ?
0.290 4 ?
0.540 5 ?
0.460 6 ?
I have SCORE and ID how can I calculate the percentile?
SCORE ID PERCENTILE
0.500 1 ?
0.500 2 ?
0.450 3 ?
0.290 4 0
0.540 5 100
0.460 6 ?
This table has a large number of rows
View 1 Replies
View Related
Oct 11, 2005
Hi All,
I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?
For example
How to calculate 25th Percentile on Salary of these 1000 records?
Hope I am clear with my question.
Thanks in advance.
Regards,
qA
View 7 Replies
View Related
Jun 20, 2007
Hello All,
Does anyone know how to do an expression that calculate percentrank and percentiles? I have a table with a list of transactions and their response time. I need to find the 75th, 90th, 95th, and 98th percentile as well as the percentrank of <=5 seconds, 10 seconds, 30 seconds and 60 seconds.
Any help is appreciated!
View 8 Replies
View Related
Dec 29, 2014
I am using sql server 2008 to calculate the percentile value for each column.
--Script
CREATE TABLE #Table (Score Varchar(4),Percentile int)
INSERT INTO #Table (Score)
VALUES
('80'),('55'),('125'),('99'),('75'),('130'),('37'),('73'),('151')
select * from #Table
I would like the result to be shown in 25,50,75,100 percentile values . Here is the example
Score | percentile
80 | 75
37 | 25
151 | 100
75 | 50
View 9 Replies
View Related
Mar 22, 2007
I'm trying to create a calculated field that gives me the avg 75 percentile.
Right now I get this value by doing the following:
Create data set:
Select top 75 percent <field>
from <table>
Then I create the following calculated field
Avg(Fields!<field>.value,"<data_set_name>")
But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.
Is it possible?
Thanks!
View 9 Replies
View Related
Jan 10, 2005
i'm currently a student in computer information sciences, and i wanted to give my studying some more focus thus choosing dba. now i'd like to dba but be as broad as possible when i first start out my career. i know some programming languages and recently finished a C# book for doing .net development and a ASP.net book for scripting. but where should i go from here? should i just start studying ms sql server? what about a microsoft certification? i'm very curious to know where the next step in my studying should be, so anyone who was in my shoes, i'd love to hear any information you have to offer. TIA
View 14 Replies
View Related
Jul 23, 2005
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies
View Related
Oct 18, 2007
I am starting a class on SQL Server, and my school wants me to builddb's either on campus or remotely at my home connecting to their SQLServer Enterprise.Will I be okay to use Developer Edition at home to connect with anEnterprise-licensed SQL Server on campus, to build databases,sprocs, .net apps that connect to the database stored on my school'sserver?I would rather avoid the Express edition.thanks
View 1 Replies
View Related
Jul 26, 2015
I am writing some scripts to calculate mean (avg) stdevs count and percentile_cont for some data..I am partitioning over multiple columns e.g. partition by year,month, day order by value..I performing unnecessary overhead as I throw away all but the last (or max) value in each window. (I use cte then select max from cte).That is I only care about the avg of the window in its entirety not specific subwindows.
View 5 Replies
View Related
Sep 15, 2015
I have process and people want to know percentile rank (on scale of 100), I'm bit lost as I have cases when I have let say 5 observations, so if use code below I can not got above number of observations:
Is there any way to get that numbers like RankWanted ??
; WITH cte AS (
SELECT 100 Cust_ID , 0.338129 Perc, 20 RankWanted union
SELECT 300 Cust_ID , 0.487179 Perc, 40 RankWanted union
SELECT 300 Cust_ID, 0.500000 Perc, 60 RankWanted union
SELECT 400 Cust_ID, 0.541666 Perc, 80 RankWanted union
SELECT 500 Cust_ID, 0.548780 Perc, 100 RankWanted )
SELECT Cust_ID, Perc,
[code].....
View 2 Replies
View Related
Sep 16, 2015
Any good way to add a percentile calculation to a table in sql server 2008 r2?
I've got 4 fields and I'd like to add a 5th labeling the records according to the percentile they fall in for one of the fields.
View 4 Replies
View Related
Jan 18, 2008
I'm a kid and I'd like to know what kind of work a database administrator does. It would be great to learn from a real database administrator how you spend your typical day at the office. What are some of your main duties? Thanks so much in advance.
View 1 Replies
View Related
Feb 12, 2013
I want to calculate average of grades of each student and get the highest one with SQL command.
I have 2 tables:
Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________
I need to calculate average of each student and then get the highest.
My try:
Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade
LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
View 5 Replies
View Related
Jun 9, 2015
We have SQL database now a days we are planing to update our students ID Records but i don't know the exact query for this. Table format is below
StudentID Name
500132253/Prep Aslam
112344883/Prep Ali
451132537/Prep Ahmed
Now i want to update only the grade from prep to KG-1 without update the registration number only prep to kg-1. in one column there is student register number and after slash the grade.
View 6 Replies
View Related
Jan 17, 2008
My working table:
CREATE TABLE [dbo].[Name_ID4](
[id_num] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[student_last_name] [varchar](30) NULL,
[student_first_name] [varchar](30) NULL,
[student_middle_init] [varchar](1) NULL
[local_student_id] [bigint] NULL,
I need to identify only the students with more than one local_student_id and the associated local_student_id's.
I can identify the students with two id's but not the associated id's
SELECT COUNT(*) AS Dup_Num,
rtrim(student_last_name),
rtrim(student_first_name),
rtrim(student_middle_init),
from Name_ID4
group by
student_last_name,
student_first_name,
student_middle_init,
having (count(*) > 1)
order by student_last_name, student_first_name, student_middle_init
Thanks
View 4 Replies
View Related
May 25, 2015
I have a table which shows data in below format
Now I need that only the max date record should be shown for every student as shown below...
View 8 Replies
View Related
Feb 20, 2012
I am trying to find out the the Average number of contact hours per student. in Reporting Services 2005. The contact hours is the in the Totaltime field
Is this formula correct
=Sum(Fields!TotalTime.Value)/Avg(Fields!TotalTime.Value) is in the =Fields!StateServices.Value Group
My groups are
=Fields!Student_ID.Value
=Fields!StateCategory.Value
=Fields!StateServices.Value
Code:
SELECT Student_ind.[Student ID], ParticipantActivity.ActivityDate, School_tbl.[Studentschool Id], School_tbl.schoolID, ParticipantActivity.TotalTime,
ParticipantActivity.Services, ParticipantActivity.Activity, Student_ind.SSID, ParticipantActivity.StateCategory, ParticipantActivity.StateServices
FROM ParticipantActivity INNER JOIN
School_tbl INNER JOIN
Student_ind ON School_tbl.[Student ID] = Student_ind.[Student ID] ON ParticipantActivity.[Student ID] = Student_ind.[Student ID]
[code]....
View 6 Replies
View Related
Apr 4, 2006
Dose MS sell SQlSerever with student rate? If so, which website do Ibuy from?
View 10 Replies
View Related
Apr 5, 2012
I have a csv that contains attendance records that I get daily from a 3rd party grade book solution. I need to import directly into the attend table in our student database.
Code:
Attend File-"1112","0021","404550","20120402","ABU","2300000","06","05"
The file is setup as follows, School Year, school number, student_id, absence date, absence code, course number, section number.
I need to check the student schedule to see if they are scheduled for that class when the import runs. So if they had a schedule change in the middle of the day it won't post attend to a dropped class.
I have done something similar to this before with the way I export teachers out to our grade book. I have it check the master schedule to see if the teacher is teaching at least one class, that way it won't export tutors and office staff to the grade book. I used the script below to do that but not sure who to apply it to a bulk insert.
Code:
Script Used to export teachers note last four lines, checks master
USE [GSchool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Secondly I need to check the date of the record and overwrite a record if one already exists for that exact course and section for that student, I need this because if they make changes to a previous day from absence un excused to excused I need to get rid of the unexcused by overwriting it.
One more thing that would be nice but is optional, is there a way to send log of errors on the import via email?
View 3 Replies
View Related
Nov 21, 2015
i am having problem putting this query to calculate students grade using the condition and legend bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
[code]...
View 7 Replies
View Related
May 14, 2015
In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master
ID Student_Name
1 Arindam
2 Brenda
3 Callie
(ClassHistory)
ID Class Year
1 4 2003
1 5 2004
1 6 2005
2 4 2003
2 5 2004
3 4 2003
DESIRED RESULTSET
ID Student_Name Class Record
1 Arindam 6
2 Brenda 5
3 Callie 4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
View 7 Replies
View Related