September 9, 2011 at 9:16 am
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)
September 13, 2011 at 7:57 am
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/
September 13, 2011 at 2:49 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy