December 5, 2013 at 5:15 am
Hi there i am having problems getting my last revision number out when i am trying to use inner joins along with the row_number over partition
I am using 2 tables, tbl_acyear_lookup & tbl_targets
tbl_acyear_lookup columns = (pk)- academic_year_id, academic_year
looks like this:
1, 2010/2011
2, 2011/2012
3, 2012/2013
tbl_targets columns = targetID, Academic_Year_ID,Course_Mode,UK_ENROL, INT_ENROL, Notes, Revision_Number
I have one stored proc that uses the Row_number over partition that looks like this:
Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV where (RV.RevNum=1)
Now the next store proc needs to use the above but i need to add the Academic_year from the tbl_acyear_lookup table also add filter the target_year ='year 1'
Any help Please?
December 5, 2013 at 5:39 am
Hello and welcome to the forums. To make it easier for everyone to help, could you please post the following?
1. DDL to create the tables and DML to populate some sample data.
2. A description of your requirements.
3. Your expected results.
The second link in my signature describes best practices on how to post questions.
December 5, 2013 at 5:41 am
i hope i understand it right,
you need to join the table "tbl_acyear_lookup" with the query to
Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number
, ACY.academic_year
from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV
Inner join tbl_acyear_lookup AS ACY on RV.Academic_Year_id = ACY.Academic_Year_id
where (RV.RevNum=1)
what i didnt understand at all is the following
"add filter the target_year ='year 1'"
what do you mean why saying adding the filter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply