How to put contraints on dates and attributes?

  • From the query below, I wanted to pull out just two attributes of the students (sgrsatt_atts_code) - NMS and NMF. And I also wanted to just choose the maximum effective date (sgrsatt_term_code_eff) before a point in time (stcyt). However, the query also gave me other attibutes that I did not want. Do you know how to limit the attributes to just the two I mentioned above (NMS and NMF)? Thank you very much.

    SELECT DISTINCT A.stpidm, A.stcyt

    , B.sgrsatt_term_code_eff

    , B.sgrsatt_atts_code

    from work.students A

    left outer join saturn.sgrsatt B

    on A.stpidm = B.sgrsatt_pidm

    and B.sgrsatt_atts_code in ('NMS','NMF')

    and A.stcyt >= B.sgrsatt_term_code_eff

    and B.sgrsatt_term_code_eff =

    (select max(X.sgrsatt_term_code_eff)

    from saturn.sgrsatt X

    where B.sgrsatt_pidm = X.sgrsatt_pidm)

  • todinhkhoi (9/9/2011)


    From the query below, I wanted to pull out just two attributes of the students (sgrsatt_atts_code) - NMS and NMF. And I also wanted to just choose the maximum effective date (sgrsatt_term_code_eff) before a point in time (stcyt). However, the query also gave me other attibutes that I did not want. Do you know how to limit the attributes to just the two I mentioned above (NMS and NMF)? Thank you very much.

    SELECT DISTINCT A.stpidm, A.stcyt

    , B.sgrsatt_term_code_eff

    , B.sgrsatt_atts_code

    from work.students A

    left outer join saturn.sgrsatt B

    on A.stpidm = B.sgrsatt_pidm

    and B.sgrsatt_atts_code in ('NMS','NMF')

    and A.stcyt >= B.sgrsatt_term_code_eff

    and B.sgrsatt_term_code_eff =

    (select max(X.sgrsatt_term_code_eff)

    from saturn.sgrsatt X

    where B.sgrsatt_pidm = X.sgrsatt_pidm)

    If i can interpret what you are saying I think you just want to move your check to the where clause and not part of your left join condition. I am guessing you want to move more to the where clause but I don't know what you are trying to achieve.

    select....

    where B.sgrsatt_atts_code in ('NMS','NMF')

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Dear Sean Lange

    Thank you for your help. I really appreciate your idea. I finally figured out 2 ways to get the job done:

    Way 1:

    SELECT DISTINCT A.stpidm, A.end_term, A.stlevel, A.stclass

    , A.progcode

    , max(B.sgrsatt_term_code_eff) as latest_eff_term

    , B.sgrsatt_atts_code as attr

    from work.students A

    left outer join saturn.sgrsatt B

    on A.stpidm = B.sgrsatt_pidm

    and B.sgrsatt_atts_code in ('NMS','NMF')

    and A.end_term >= B.sgrsatt_term_code_eff

    group by A.stpidm, A.end_term, A.stlevel, A.stclass

    , A.progcode

    , B.sgrsatt_atts_code

    Way 2:

    SELECT DISTINCT A.stpidm, A.end_term, A.stlevel, A.stclass, A.progcode

    , B.sgrsatt_term_code_eff

    , B.sgrsatt_atts_code

    from work.students A

    left outer join saturn.sgrsatt B

    on A.stpidm = B.sgrsatt_pidm

    and B.sgrsatt_atts_code in ('NMS','NMF')

    and A.end_term >= B.sgrsatt_term_code_eff

    and B.sgrsatt_term_code_eff = (select max(X.sgrsatt_term_code_eff)

    from saturn.sgrsatt X

    where B.sgrsatt_pidm = X.sgrsatt_pidm

    and B.sgrsatt_atts_code = X.sgrsatt_atts_code

    and A.end_term >= X.sgrsatt_term_code_eff)

Viewing 3 posts - 1 through 2 (of 2 total)

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