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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply