0

Is there a way to loop an inner join based on specific conditions. I am returning data that looks as follows:

 Term EnrollmentTotal Retained Fall 2011 X X Fall 2012 X X Fall 2013 X X

Actual Code:

DECLARE @Version int = 2 ,@TermSourceKey varchar(40)= '1109,1119,1129' ,@NextTerm varchar(40) = '1119,1129,1139' /* ******** TERMSOURCKEY PARAMETER ********** */ if OBJECT_ID('Tempdb..#TermSourceKeyTable','U') is not null drop table #TermSourceKeyTable CREATE TABLE #TermSourceKeyTable (Terms varchar(5) ) INSERT INTO #TermSourceKeyTable SELECT value from HEA.ParseList(@TermSourceKey,',') if OBJECT_ID('Tempdb..#NextTermParam','U') is not null drop table #NextTermParam CREATE TABLE #NextTermParam (ID INT Identity(1,1) ,val varchar(20) ,NTerm varchar(5) ) INSERT INTO #NextTermParam SELECT 0 ,value from HEA.ParseList(@NextTerm,',') /* ******** NEXT TERM PARAMETER ********** */ if OBJECT_ID('Tempdb..#EnrollmentTypes','U') is not null drop table #EnrollmentTypes Create Table #EnrollmentTypes ( EmployeeID Varchar(12) ,PlanOwnerKey int ,Term varchar(25) ,NumberEnrolled int --,BaseID varchar(12) ,RetainedInDept int) Insert Into #EnrollmentTypes SELECT Distinct fst.EmployeeID --,fst.EmployeeID ,dpo.PlanOwnerKey ,dt.Description ,0 ,0 --Count(et.NumberEnrolled) as 'Total Enrolled' ,0 --RetainedInDept FROM Final.FactStudentTerm fst JOIN CustomFinal.FactStudentPlanSimple fsps on fst.EmployeeID = fsps.EmployeeID and fst.AcademicCareer = fsps.AcademicCareer and fst.TermSourceKey = fsps.TermSourceKey and fst.VersionKey = fsps.VersionKey and fst.Institution = fsps.Institution and fsps.AcademicCareer = 'UGRD' Join CustomFinal.FactPlanOwnership fpo on fsps.AcademicPlan = fpo.AcademicPlan and fsps.PlanKey = fpo.PlanKey and fsps.Institution = fpo.Institution JOIN CustomFinal.DimPlanOwner dpo on fpo.PlanOwnerKey = dpo.PlanOwnerKey and dpo.CollegeOrgCode = @CollegeOrgCode and dpo.PlanOwnerKey = @PlanOwnerKey /* Biological Sciences */ JOIN CustomFinal.DimProgramStatusForPlan dpsp on dpsp.ProgramStatusForPlanKey = fsps.ProgramStatusForPlanKey /* This table returns: only students active in their program*/ and dpsp.ProgramStatusSourceKey = 'AC' JOIN Final.DimProgram dprog on fst.ProgramKey = dprog.ProgramKey /* Check to see if these should be included: Associate Degree Program (ASSOC), Undergraduate Second Degree (UG2D), Undergraduate Degree Seeking (UGDEG) */ and dprog.SourceKey in ('UGDEG','UG2D') JOIN Final.DimPlan dp on fsps.PlanKey = dp.PlanKey and dp.PlanType in ('MAJ','CRT') JOIN Final.DimTerm dt on fst.TermKey = dt.TermKey JOIN Final.DimStudent ds on fst.EmployeeID =ds.SourceKey Join #TermSourceKeyTable tskt on tskt.Terms = fst.TermSourceKey WHERE 1 = 1 AND fst.VersionKey = @Version AND fst.EnrolledIndicatorKey in ('11','12') GROUP BY dpo.PlanOwnerKey , fst.EmployeeID, dt.Description /* END OF INSERT INTO SQL: Returns NumberEnrolled */ /* ************* ENROLLED, BUT SWITCHED DEPARTMENT ************ */ UPDATE #EnrollmentTypes SET EnrollButSwitchedDept = EnrollButSwitchedDept.EnrollButSwitchedDept FROM #EnrollmentTypes et outer apply (SELECT CASE WHEN et.PlanOwnerKey != dpo1.PlanOwnerKey then 1 else 0 end as EnrollButSwitchedDept FROM Final.FactStudentTerm fst1 JOIN CustomFinal.FactStudentPlanSimple fsps on fst1.EmployeeID = fsps.EmployeeID and fst1.AcademicCareer = fsps.AcademicCareer and fst1.TermSourceKey = fsps.TermSourceKey and fst1.VersionKey = fsps.VersionKey and fst1.Institution = fsps.Institution and fsps.AcademicCareer = 'UGRD' JOIN CustomFinal.FactPlanOwnership fpo on fsps.AcademicPlan = fpo.AcademicPlan and fsps.PlanKey = fpo.PlanKey and fsps.Institution = fpo.Institution JOIN CustomFinal.DimPlanOwner dpo1 on fpo.PlanOwnerKey = dpo1.PlanOwnerKey JOIN CustomFinal.DimProgramStatusForPlan dpsp on dpsp.ProgramStatusForPlanKey = fsps.ProgramStatusForPlanKey and dpsp.ProgramStatusSourceKey = 'AC' JOIN Final.DimProgram dprog on fst1.ProgramKey = dprog.ProgramKey and dprog.SourceKey in ('UGDEG','UG2D') JOIN Final.DimPlan dp on fsps.PlanCount = dp.PlanKey and dp.PlanType in ('MAJ','CRT') JOIN Final.DimTerm dt on fst1.TermKey = dt.TermKey JOIN #NextTermParam NTP on fst1.TermSourceKey = NTP.NTerm /*comparing 2010 to 11, 12, & 13*/ and NTP.ID = 1 --+1 --JOIN Final.DimTerm dtEnrolled -- on dtEnrolled.NextYearTermSourceKey = fst1.TermSourceKey -- and dtEnrolled.NextYearTermSourceKey = dt.NextYearTermSourceKey --JOIN #TermSourceKeyTable tsk -- on tsk.Terms = fst1.termsourcekey WHERE 1 = 1 AND fst1.EmployeeID = et.EmployeeID AND fst1.VersionKey = @Version --AND fst1.TermSourceKey = @NextTerm AND fst1.EnrolledIndicatorKey in ('11','12') ) EnrollButSwitchedDept /* Main Query */ SELECT et.Term ,COUNT(et.EmployeeID) as 'Enrollment Total' ,SUM(et.EnrollButSwitchedDept) as 'EnrollButSwitched' FROM #EnrollmentTypes et WHERE 1 = 1 --AND et.EnrollButSwitchedDept is not null GROUP BY et.Term ORDER BY et.Term

The main issue that I'm having is that the Fall of 2011 term is being used to calculate the Retained for all of the years. Therefore, I figure I need to loop the join so that it will pull the @NextTerm for the Current Term being calculated. Any suggestions?

Improve this question
3
  • 2
    Could you provide sample data and expected output? Commented Apr 20, 2015 at 16:40
  • What do mean by sample data? Commented Apr 20, 2015 at 16:45
  • 3
    @SQLUser44 - sample data means the actual values for the rows and columns in the database and the expected results. Commented Apr 20, 2015 at 16:55

1 Answer 1

Reset to default
2

You never want to do a loop in SQL. SQL uses sets and operations on sets. So for example you want to count the rows for a given term you use function called COUNT(). Your query might look something like this:

Select .... subselect.cnt as Next_Term_Enrollment from .... join ( select term, count(*) as cnt from tmptable group by term ) as subselect on (subselect.term-1) = term

since you give VERY LITTLE details about your data model I can't be more specific that this... but I hope this gets you in the right direction.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I am doing a Count. Essentially I created a main temp table which holds the term, Total Enrolled & retained. I'm updating the Total enrolled & retained columns via a cross apply. (The join showed in the original post is within the cross apply.) I would have added that but it seemed as though that would be to much include.
describing queries with English does not work. Please add the actual queries you are using to the question -- I can't possibly understand what you are saying.
I've added the actual code that I'm using in the original post. Perhaps that will be enough info help me solve this and hopefully it's not too much information.
great - now it seems your question does not have a question -- can you state a question with this code?
The question is right below the main SQL code. But I'm basically wondering if I can if its possible to loop a join. In particular temp table join within my 2nd outer apply. Do I need to move this into the original post?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.