February 13, 2012 at 10:15 am
I'm needing to get the most recent AP_Ex.Ap_Expt_Date for each AP_Ex.Student_ID. However, running the query below still includes the other dates the exam was taken when I include Ap_Expt_Date. Any thoughts on how I can get the most recent exam for each student? Thanks you very much for any help! - Mason
SELECT
'CRN~SIS~AD~AP_Ex~' + CAST(AP_Ex.Ap_Expt_ID AS NVARCHAR) AS PKEY_CRN_SIS_AD_Ap_Expt
,'CRN~SIS~GI~Student~' + CAST(AP_Ex.Student_ID AS NVARCHAR) AS FKEY_CRN_SIS_GI_Student
,AP_Ex.Ap_Expt_ID AS CRN_SIS_AD_AP_Ex_Ap_Expt_ID
,AP_Ex.Student_ID AS CRN_SIS_AD_AP_Ex_Student_ID
,AP_Ex.Enrollment_ID AS CRN_SIS_AD_AP_Ex_Enrollment_ID
,AP_Ex.Exam_ID AS CRN_SIS_AD_AP_Ex_Exam_ID
,AP_Ex.Ap_Expt_Code AS CRN_SIS_AD_AP_Ex_Ap_Expt_Code
,AP_Ex.Ap_Expt AS CRN_SIS_AD_AP_Ex_Ap_Expt
,AP_Ex.Ap_Expt_Type AS CRN_SIS_AD_AP_Ex_Ap_Expt_Type
,AP_Ex.Student_Exam_Required_Ind AS CRN_SIS_AD_AP_Ex_Student_Exam_Required_Ind
,MAX(AP_Ex.Ap_Expt_Date) AS CRN_SIS_AD_AP_Ex_Ap_Expt_Date
,AP_Ex.Ap_Expt_Minimum_Score AS CRN_SIS_AD_AP_Ex_Ap_Expt_Minimum_Score
,AP_Ex.Ap_Expt_Score AS CRN_SIS_AD_AP_Ex_Ap_Expt_Score
,AP_Ex.Ap_Expt_Scheduled_Date AS CRN_SIS_AD_AP_Ex_Ap_Expt_Scheduled_Date
,AP_Ex.Ap_Expt_Fee_Amount AS CRN_SIS_AD_AP_Ex_Ap_Expt_Fee_Amount
,AP_Ex.Ap_Expt_Fee_Paid_By_Student_Ind AS CRN_SIS_AD_AP_Ex_Ap_Expt_Fee_Paid_By_Student_Ind
,AP_Ex.Ap_Expt_Location_Code AS CRN_SIS_AD_AP_Ex_Ap_Expt_Location_Code
,AP_Ex.Ap_Expt_Location AS CRN_SIS_AD_AP_Ex_Ap_Expt_Location
,AP_Ex.Ap_Expt_Result_Date AS CRN_SIS_AD_AP_Ex_Ap_Expt_Result_Date
,AP_Ex.Ap_Expt_Number_Result AS CRN_SIS_AD_AP_Ex_Ap_Expt_Number_Result
,AP_Ex.Ap_Expt_Text_Result AS CRN_SIS_AD_AP_Ex_Ap_Expt_Text_Result
,AP_Ex.Parent_Exam_ID AS CRN_SIS_AD_AP_Ex_Parent_Exam_ID
,AP_Ex.CVue_Record_Added_Date AS CRN_SIS_AD_AP_Ex_CVue_Record_Added_Date
,AP_Ex.CVue_Record_Modified_DateTime AS CRN_SIS_AD_AP_Ex_CVue_Record_Modified_DateTime
,AP_Ex.DW_Modified_DateTime AS CRN_SIS_AD_AP_Ex_DW_Modified_DateTime
FROM Ap_Expt AS AP_Ex
WHERE AP_Ex.Ap_Expt = 'MRAT Total'
GROUP BY
AP_Ex.Ap_Expt_ID
,AP_Ex.Student_ID
,AP_Ex.Enrollment_ID
,AP_Ex.Ap_Expt_Code
,AP_Ex.Ap_Expt
,AP_Ex.Exam_ID
,AP_Ex.Ap_Expt_Type
,AP_Ex.Student_Exam_Required_Ind
,AP_Ex.Ap_Expt_Minimum_Score
,AP_Ex.Ap_Expt_Score
,AP_Ex.Ap_Expt_Scheduled_Date
,AP_Ex.Ap_Expt_Fee_Amount
,AP_Ex.Ap_Expt_Fee_Paid_By_Student_Ind
,AP_Ex.Ap_Expt_Location_Code
,AP_Ex.Ap_Expt_Location
,AP_Ex.Ap_Expt_Result_Date
,AP_Ex.Ap_Expt_Number_Result
,AP_Ex.Ap_Expt_Text_Result
,AP_Ex.Parent_Exam_ID
,AP_Ex.CVue_Record_Added_Date
,AP_Ex.CVue_Record_Modified_DateTime
,AP_Ex.DW_Modified_DateTime
February 13, 2012 at 10:33 am
I'm needing to get the most recent AP_Ex.Ap_Expt_Date for each AP_Ex.Student_ID
SELECT Max(AP_Ex.Ap_Expt_Date) As LatestDate, AP_Ex.Student_ID
FROM...
GROUP BY AP_Ex.Student_ID;
February 13, 2012 at 12:46 pm
Thanks for your reply. Can I only get a good result set if I take all of the other fields out of the select query? Those contain information I'm going to use.
February 13, 2012 at 12:55 pm
Your query should return only one row per AP_Ex.Student_ID only when all other values except AP_Ex.Ap_Expt_Date are equal.
Check the column values per AP_Ex.Student_ID. At least one more column will show different values.
February 13, 2012 at 1:28 pm
Thanks, Lutz. I think I understand - One result set per Student_ID as long as the other rows match in every column except AP_Ex.Ap_Expt_Date. If you don't mind another question - What should I do if I need data that will cause the columns to differ (e.g. AP_Ex_AP_Expt_Score)? I posted a result set below - I thought that might help. Thanks again for taking a look.
[In the result set below, I added WHERE AP_Ex.Ap_Expt_Date = 'MRAT Total' AND AP_Ex.Student_ID = '102024']
FKEY_CRN_SIS_GI_StudentCRN_SIS_AD_AP_Ex_AP_Expt_IDCRN_SIS_AD_AP_Ex_Student_IDCRN_SIS_AD_AP_Ex_Exam_IDCRN_SIS_AD_AP_Ex_AP_Expt_CodeCRN_SIS_AD_AP_Ex_AP_ExptCRN_SIS_AD_AP_Ex_AP_Expt_TypeCRN_SIS_AD_Ad_MAX_Exam_AP_Expt_DateCRN_SIS_AD_AP_Ex_AP_Expt_Score
102042441779221020241MRATTOT MRAT TotalScore8/1/200729
102042460796021020241MRATTOT MRAT TotalScore4/1/200724
102042476965321020241MRATTOT MRAT TotalScore4/1/201129
February 13, 2012 at 1:34 pm
masonvann (2/13/2012)
Thanks, Lutz. I think I understand - One result set per Student_ID as long as the other rows match in every column except AP_Ex.Ap_Expt_Date. If you don't mind another question - What should I do if I need data that will cause the columns to differ (e.g. AP_Ex_AP_Expt_Score)? I posted a result set below - I thought that might help. Thanks again for taking a look.[In the result set below, I added WHERE AP_Ex.Ap_Expt_Date = 'MRAT Total' AND AP_Ex.Student_ID = '102024']
...
It depends what you're looking for:
a) you need to see all different values: accept the duplicates
b) you're looking for one row only and still need the duplicate column: use MIN() or MAX() to reduce it to a single value per student
c) you're looking for one row only and don't really need the duplicate column: remove it from your query.
February 13, 2012 at 1:41 pm
Hi masonavann
do you know how to script out "create table" statement and provide "insert data" scripts ?
there are several ways to do this quickly.
if you could please provide this then I am sure that you will have a tried and tested answer very quickly.
if you are not sure how to do this, then please post back.
Lutz has one method is his sig http://www.sqlservercentral.com/articles/Best+Practices/61537/
edit typo....apologies Lutz 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2012 at 1:49 pm
February 13, 2012 at 1:50 pm
What exactly do you mean by none method?
Probably just a contraction of Number One ! 😛
February 13, 2012 at 1:55 pm
LutzM (2/13/2012)
J Livingston SQL (2/13/2012)
...Lutz has none method is his sig http://www.sqlservercentral.com/articles/Best+Practices/61537/
What exactly do you mean by none method? :unsure:
typo edited in original.....sorry Lutz :blush:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2012 at 1:57 pm
LutzM (2/13/2012)
J Livingston SQL (2/13/2012)
...Lutz has none method is his sig http://www.sqlservercentral.com/articles/Best+Practices/61537/
What exactly do you mean by none method? :unsure:
typo edited in original.....sorry Lutz :blush:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2012 at 2:01 pm
It's just been a typo. No reason to apologize twice. 😀
February 13, 2012 at 2:07 pm
Would the windowing version of the MAX function be useful here?
Replace the line
MAX(AP_Ex.Ap_Expt_Date) AS CRN_SIS_AD_AP_Ex_Ap_Expt_Date
with
MAX(AP_Ex.Ap_Expt_Date) OVER (PARTITION BY AP_Ex.Student_ID) AS CRN_SIS_AD_AP_Ex_Ap_Expt_Date
and remove the group by clause
An explanation of the OVER clause can be found at http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx"> http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx
Dave
February 13, 2012 at 2:18 pm
Dave Brooking (2/13/2012)
Would the windowing version of the MAX function be useful here?Replace the line
MAX(AP_Ex.Ap_Expt_Date) AS CRN_SIS_AD_AP_Ex_Ap_Expt_Datewith
MAX(AP_Ex.Ap_Expt_Date) OVER (PARTITION BY AP_Ex.Student_ID) AS CRN_SIS_AD_AP_Ex_Ap_Expt_Dateand remove the group by clause
An explanation of the OVER clause can be found at http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx"> http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx
Dave
This would still return all rows, since there's no aggregation performed. Let's see what the final result should look like 🙂
March 20, 2012 at 1:31 pm
O.k., I've been working on this. I've created a test scenario below. I really apologize if it's helpful or too much, but I thought it might give the clearest explanation of what I'm trying to accomplish. I'll do more work if needed - just let me know! Thanks again for the help.
/*Create the table*/
CREATE TABLE Admission_Exam_Test
(
Student_IDCHAR(10)NOT NULL,
Exam_IDCHAR(10)NOT NULL,
Admission_Exam_CodeCHAR(15)NOT NULL,
Admission_ExamCHAR(25)NOT NULL,
Admission_Exam_TypeCHAR(15)NOT NULL,
Admission_Exam_DateDateNOT NULL,
Admission_Exam_ScoreCHAR(6)NOT NULL
)
;
/*Putting data in the table to create the test enviroment*/
INSERT INTO Admission_Exam_Test
(
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
Admission_Exam_Date,
Admission_Exam_Score
)
Values
('240043', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2009-06-01', '08'),
('240043', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2011-08-01', '09'),
('240043', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2010-06-01', '11'),
('240043', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2009-06-01', '05'),
('240043', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2010-06-01', '09'),
('240043', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2011-08-01', '10'),
('240043', '2', 'MCATTOT', 'MCAT Total', 'Score', '2009-06-01', '18'),
('240043', '2', 'MCATTOT', 'MCAT Total', 'Score', '2011-08-01', '26'),
('240043', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2009-06-01', '05'),
('240043', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2010-06-01', '06'),
('240043', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2011-08-01', '07'),
('440643', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2009-07-01', '12'),
('440643', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2011-09-01', '04'),
('440643', '10', 'MCATBIO', 'MCAT Biology Score', 'Score', '2011-06-01', '13'),
('440643', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2009-07-01', '06'),
('440643', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2011-06-01', '10'),
('440643', '8', 'MCATPHYS', 'MCAT Physical Science', 'Score', '2011-09-01', '12'),
('440643', '2', 'MCATTOT', 'MCAT Total', 'Score', '2009-07-01', '20'),
('440643', '2', 'MCATTOT', 'MCAT Total', 'Score', '2011-09-01', '30'),
('440643', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2009-07-01', '07'),
('440643', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2011-09-01', '08'),
('440643', '7', 'MCATVRBL', 'MCAT Verbal Score', 'Score', '2011-06-01', '09')
;
/*This statement selects all of the data*/
SELECT
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
Admission_Exam_Date,
Admission_Exam_Score
FROM Admission_Exam_Test
;
/*This statement doesn't only give a single row per Most_Recent_Exam_Date because multiple scores exist */
SELECT
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
MAX(Admission_Exam_Date) AS Most_Recent_Exam_Date,
Admission_Exam_Score
FROM Admission_Exam_Test
GROUP BY
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
Admission_Exam_Score
/*This statement does return a single row per Most_Recent_Exam_Date. However, I need the score included in the result set.*/
SELECT
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
MAX(Admission_Exam_Date) AS Most_Recent_Exam_Date
FROM Admission_Exam_Test
GROUP BY
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type
/*Our current way of getting the desired results is to build a view. Depending on how the data is structured, we would build multiple views to summarize the information. For example, to get the desired results above, we would build a view that gives us a single row per Most_Recent_Exam_Date, and the write a statement that joins everything in the view with the table that contains the score. This gets messy when the data needs to be summarized a few times. In this case, multiple views must be created. So here's my question - Is there a way to get Most_Recent_Exam_Date and Admission_Exam_Score without creating views (in other words, in a single SQL statement?*/
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply