USP_SIMPLEDATALIST_CHARGESCHOOLS

            CREATE procedure dbo.USP_SIMPLEDATALIST_CHARGESCHOOLS
            (
                @STUDENTID uniqueidentifier,
                @CHARGEDATE date  = null,
                @BILLINGITEMID uniqueidentifier
            )
            as
                declare @COUNT int;
                declare @TYPECODE tinyint;


                --Check billing item price type 

                select @TYPECODE=PRICETYPECODE from dbo.BILLINGITEM where ID=@BILLINGITEMID

                --If vary by school retrieve the valid student progression school or the enrollment school

                if (@TYPECODE = 3)
                 begin
                    (select 
                        SCHOOL.ID as VALUE,
                        CONSTITUENT.KEYNAME as LABEL
                    from 
                        dbo.EDUCATIONALHISTORY as EDUCATION 
                    inner join 
                        dbo.SCHOOL on EDUCATION.EDUCATIONALINSTITUTIONID = SCHOOL.ID
                    inner join 
                        dbo.CONSTITUENT on SCHOOL.ID = CONSTITUENT.ID
                    inner join 
                        dbo.ACADEMICYEAR AY on SCHOOL.ID = AY.SCHOOLID 
                    where (@CHARGEDATE IS NOT NULL) and
                        (EDUCATION.CONSTITUENTID = @STUDENTID) and
                        (dbo.UFN_DATE_FROMFUZZYDATE(EDUCATION.STARTDATE) <= @CHARGEDATE) and
                        ((dbo.UFN_DATE_FROMFUZZYDATE(EDUCATION.DATELEFT) >= @CHARGEDATE) or (EDUCATION.DATELEFT = '00000000')) and
                        (AY.BILLINGSTARTDATE <= @CHARGEDATE) and
                        (AY.BILLINGENDDATE >= @CHARGEDATE)    
                    group by SCHOOL.ID, CONSTITUENT.KEYNAME)
                    UNION
                    (select
                        SG.SCHOOLID as VALUE,
                        CONSTITUENT.KEYNAME as LABEL
                    from 
                        dbo.ACADEMICYEAR as AY
                    inner join 
                        dbo.STUDENTPROGRESSION as SP on (AY.STARTDATE = SP.STARTDATE) and (AY.ENDDATE = SP.ENDDATE) 
                    inner join
                        dbo.EDUCATIONALHISTORY as ENROLLMENT on SP.ENROLLMENTID = ENROLLMENT.ID
                    inner join 
                        dbo.SCHOOLGRADELEVEL as SG on (SP.SCHOOLGRADELEVELID = SG.ID) and (AY.SCHOOLID = SG.SCHOOLID)
                    inner join 
                        dbo.CONSTITUENT on SG.SCHOOLID = CONSTITUENT.ID
                    where (@CHARGEDATE IS NOT NULL) 
                        and (AY.BILLINGSTARTDATE<=@CHARGEDATE) 
                        and (AY.BILLINGENDDATE>=@CHARGEDATE) 
                        and (ENROLLMENT.CONSTITUENTID = @STUDENTID) 
                    group by SG.SCHOOLID, CONSTITUENT.KEYNAME)
                 end
                --All other types retrieve the valid progression schools

                else
                 begin
                    select
                        SG.SCHOOLID as VALUE,
                        CONSTITUENT.KEYNAME as LABEL
                    from 
                        dbo.ACADEMICYEAR as AY
                    inner join 
                        dbo.STUDENTPROGRESSION as SP on (AY.STARTDATE = SP.STARTDATE) and (AY.ENDDATE = SP.ENDDATE) 
                    inner join
                        dbo.EDUCATIONALHISTORY as ENROLLMENT on SP.ENROLLMENTID = ENROLLMENT.ID
                    inner join 
                        dbo.SCHOOLGRADELEVEL as SG on (SP.SCHOOLGRADELEVELID = SG.ID) and (AY.SCHOOLID = SG.SCHOOLID)
                    inner join 
                        dbo.CONSTITUENT on SG.SCHOOLID = CONSTITUENT.ID
                    where (@CHARGEDATE IS NOT NULL) 
                        and (AY.BILLINGSTARTDATE<=@CHARGEDATE) 
                        and (AY.BILLINGENDDATE>=@CHARGEDATE) 
                        and (ENROLLMENT.CONSTITUENTID = @STUDENTID) 
                    group by SG.SCHOOLID, CONSTITUENT.KEYNAME
                 end