While loop in T-SQL

Question

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?

Answer 1

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.