Getting Most Recent Date

  • What is your expected result out of the sample data you provide. I think i may have solution for it, but need to see your desired result visually..

  • I'm not sure if my approach is waht you're lloking for, but I think the ROW_NUMBER approach will help here:

    ;

    WITH cte AS

    (

    SELECT

    Student_ID,

    Exam_ID,

    Admission_Exam_Code,

    Admission_Exam,

    Admission_Exam_Type,

    Admission_Exam_Date,

    Admission_Exam_Score,

    ROW_NUMBER() OVER(PARTITION BY Student_ID,Exam_ID ORDER BY Admission_Exam_Date desc ) pos

    FROM @Admission_Exam_Test

    )

    SELECT *

    FROM cte

    WHERE pos = 1



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • pietlinden (2/13/2012)


    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;

    What I used to do was select the latest test date for each student, then using the latest test date and student id for each student, select the rest of the data. Obviously you'd have to disambiguate the test if multiple tests per date.

    -- something like:

    CREATE TABLE ZTMP_TEST_TABLE

    (

    Student_ID VARCHAR(10),

    Ap_Expt_Date DATETIME,

    Test_Score VARCHAR(10)

    )

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-01-01','10')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-04-01','13')

    SELECT student_id_sel, LatestDate, ZTMP_TEST_TABLE.* FROM

    (

    SELECT Max(AP_Ex.Ap_Expt_Date) As LatestDate, AP_Ex.Student_ID as student_id_sel

    FROM ZTMP_TEST_TABLE AP_Ex

    GROUP BY AP_Ex.Student_ID

    ) SELECTED

    JOIN

    ZTMP_TEST_TABLE

    ON

    student_id_sel = Student_ID AND

    LatestDate = Ap_Expt_Date

  • Thanks for the reply. The results should be something like this: (I apologize for the format - I can't for the life of me figure how to make it look clean)

    Student_IDExam_IDAdmission_Exam_CodeAdmission_ExamAdmission_Exam_TypeMost_Recent_Exam_Date Admission_Exam_Score

    240043 10 MCATBIO MCAT Biology Score Score 2011-08-01 09

    240043 2 MCATTOT MCAT Total Score 2011-08-01 26

    240043 7 MCATVRBL MCAT Verbal Score Score 2011-08-01 07

    240043 8 MCATPHYS MCAT Physical Science Score 2011-08-01 10

    440643 10 MCATBIO MCAT Biology Score Score 2011-09-01 04

    440643 2 MCATTOT MCAT Total Score 2011-09-01 30

    440643 7 MCATVRBL MCAT Verbal Score Score 2011-09-01 08

    440643 8 MCATPHYS MCAT Physical Science Score 2011-09-01 12

  • Did you try the code I posted?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Just to expand on my other post, I used to get the latest instance of whatever I wanted, then use that latest instance as sort of a lookup key back into the same table. So if I had test types also, I'd just include that into the group by clause to find the latest instance, then also use it in the join.

    CREATE TABLE ZTMP_TEST_TABLE

    (

    Student_ID VARCHAR(10),

    Test_ID VARCHAR(10),

    Ap_Expt_Date DATETIME,

    Test_Score VARCHAR(10)

    )

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-01-01','10')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-01-01','13')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-02-01','14')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-03-01','15')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-01-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-02-01','13')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-04-01','13')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-04-01','13')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-02-01','11')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-03-01','12')

    INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-04-01','13')

    SELECT student_id_sel, test_id_sel, LatestDate, ZTMP_TEST_TABLE.* FROM

    (

    SELECT Max(Ap_Expt_Date) As LatestDate, Student_ID as student_id_sel, Test_ID as test_id_sel

    FROM ZTMP_TEST_TABLE

    GROUP BY Student_ID, Test_ID

    ) SELECTED

    JOIN

    ZTMP_TEST_TABLE

    ON

    student_id_sel = Student_ID AND

    test_id_sel = Test_ID AND

    LatestDate = Ap_Expt_Date

    I still tend to use this approach because I have so many 2000 level servers to support, but its worth noting that the over() clause works nicely too, I just haven't used it as much.

  • Lutz,

    Yes, I was working through the replies in order. You nailed it! I know you're probably busy, but would you mind explaining how it works? I don't understand 'WITH cte AS' or 'WHERE pos = 1'. I'll look for an article on ROW_NUMBER() - I've never seen that used before. Either way, that's the result set I'm looking for.

    Mason

  • Here are some resources for cte:

    BooksOnLine and a article[/url] here at SSC.

    and ROW_NUMBER

    BooksOnLine and a related article



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks to everyone for the help!!

  • One more question on this. My row_number doesn't seem to be working in a few instances. Here's what I have:

    WITH cte AS

    (

    SELECT

    'CVPA~SIS~AD~Ad_Exam~' + CAST(Ad_Exam.Admission_Exam_ID AS NVARCHAR) AS PKEY_CVPA_SIS_AD_Admission_Exam

    ,'CVPA~SIS~GI~Student~' + CAST(Ad_Exam.Student_ID AS NVARCHAR) AS FKEY_CVPA_SIS_GI_Student

    ,Ad_Exam.Admission_Exam_ID

    ,Ad_Exam.Student_ID AS Student_ID_1

    ,Ad_Exam.Enrollment_ID

    ,Ad_Exam.Exam_ID

    ,Ad_Exam.Admission_Exam_Code

    ,Ad_Exam.Admission_Exam

    ,Ad_Exam.Admission_Exam_Type

    ,Ad_Exam.Student_Exam_Required_Ind

    ,Admission_Exam_Date =

    CASE WHEN Ad_Exam.Admission_Exam_Date IS NULL THEN '1/1/2006'

    ELSE Ad_Exam.Admission_Exam_Date END

    ,Ad_Exam.Admission_Exam_Minimum_Score

    ,Ad_Exam.Admission_Exam_Score

    ,Ad_Exam.Admission_Exam_Scheduled_Date

    ,Ad_Exam.Admission_Exam_Fee_Amount

    ,Ad_Exam.Admission_Exam_Fee_Paid_By_Student_Ind

    ,Ad_Exam.Admission_Exam_Location_Code

    ,Ad_Exam.Admission_Exam_Location

    ,Ad_Exam.Admission_Exam_Result_Date

    ,Ad_Exam.Admission_Exam_Number_Result

    ,Ad_Exam.Admission_Exam_Text_Result

    ,Ad_Exam.Parent_Exam_ID

    ,Ad_Exam.CVue_Record_Added_Date

    ,Ad_Exam.CVue_Record_Modified_DateTime

    ,Ad_Exam.DW_Modified_DateTime

    FROM Admission_Exam AS Ad_Exam

    /*WHERE added because result set was too large*/

    WHERE

    Admission_Exam = 'Cum GPA' OR

    Admission_Exam = 'DAT A Academic Average Score' OR

    Admission_Exam = 'DMD - Cum GPA' OR

    Admission_Exam = 'DMD - Science GPA' OR

    Admission_Exam = 'DO - Cum GPA' OR

    Admission_Exam = 'DO - Science GPA' OR

    Admission_Exam = 'DOR - Cum GPA' OR

    Admission_Exam = 'EPAC Overall Score' OR

    Admission_Exam = 'GRE Analytical Score' OR

    Admission_Exam = 'GRE Quantitative Score' OR

    Admission_Exam = 'GRE Verbal Score' OR

    Admission_Exam = 'MCAT Total' OR

    Admission_Exam = 'OT - Cum GPA' OR

    Admission_Exam = 'OT - Science GPA' OR

    Admission_Exam = 'PA - Cum GPA' OR

    Admission_Exam = 'PA - Science GPA' OR

    Admission_Exam = 'PT - Cum GPA' OR

    Admission_Exam = 'PT - Prereq GPA' OR

    Admission_Exam = 'Science GPA'

    SELECT

    cte.Student_ID_1 AS Student_ID_2

    ,cte.Exam_ID

    ,cte.Admission_Exam_Code

    ,cte.Admission_Exam

    ,cte.Admission_Exam_Type

    ,cte.Admission_Exam_Date

    ,CONVERT(decimal(4,2),cte.Admission_Exam_Score) AS Admission_Exam_Score

    ,ROW_NUMBER() OVER(PARTITION BY cte.Student_ID_1,cte.Exam_ID

    ORDER BY cte.Admission_Exam_Score desc ) pos

    FROM cte

    Here is a sample of results:

    Student_ID_2Exam_ID Admission_Exam_Code Admission_ExamAdmission_Exam_TypeAdmission_Exam_DateAdmission_Exam_Scorepos

    634456781MCATTOT MCAT Total Score 2011-01-01 28.00 1

    634456781MCATTOT MCAT Total Score 2011-07-01 22.00 2

    6344567859DOCUMGPA DO - Cum GPA Score 2006-01-01 3.48 1

    6344567860DOSCIGPA DO - Science GPAScore 2006-01-01 3.16 1

    569477641MCATTOT MCAT Total Score 2011-05-01 24.00 1

    569477641MCATTOT MCAT Total Score 2010-06-01 29.00 2

    569477641MCATTOT MCAT Total Score 2009-06-01 24.00 3

    5694776459DOCUMGPA DO - Cum GPA Score 2006-01-01 3.84 1

    141353 60DOSCIGPA DO - Science GPAScore 2006-01-01 3.19 1

    Notice that (for the first Student_ID_2) has an Admission_Exam_Date of 2011-07-01 in pos 2. However, I thought the desc syntax would give me the most recent date, which should put 2011-07-01 in pos 1. (Note that for the second Student_ID_2 set, it did work properly). I'm probably making a newbie error somewhere in here, but maybe someone can help me out.

    By the way, does anyone have a link on an easy way to post a result set in the forum? The above is probably the most hacked together result set to ever grace this forum 🙂

  • According to your post you have:

    ...

    ORDER BY cte.Admission_Exam_Score desc ) pos

    which looks like its not the date 😉

  • patrickmcginnis59 (3/27/2012)


    According to your post you have:

    ...

    ORDER BY cte.Admission_Exam_Score desc ) pos

    which looks like its not the date 😉

    As I was saying... 🙂

    Thanks so much for the help. I'll get the hang of this yet.

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply