Sample Widget - Enrollment by Week

The SQL Query used in this widget is:

-- Enrollment by week

declare @yearGu uniqueidentifier
declare @startDt smalldatetime, @stopDt smalldatetime
set @yearGu = (select YEAR_GU from REV_YEAR where EXTENSION = 'R' and SCHOOL_YEAR = @FOCUS_YEAR)
set @startDt = '09/09/2008'
set @stopDt = @DATE
 
-- The following is the SQL string that uses the above 3 parameters
declare @curDt smalldatetime
declare @result table (WEEK smalldatetime, ENRCNT int)
declare @curEnr int
 
set @curDt = @startDt
 
while @curDt < @stopDt
begin
  set @curEnr = (select count(*) NumStu from EPC_STU_SCH_YR ssy
                 inner join REV_ORGANIZATION_YEAR oyr on (oyr.ORGANIZATION_YEAR_GU = ssy.ORGANIZATION_YEAR_GU)
where oyr.YEAR_GU = @yearGu
and ssy.ENTER_DATE <= @curDt
and ssy.ORGANIZATION_YEAR_GU = @FOCUS_ORG_YEAR_GU
and (ssy.LEAVE_DATE is null or ssy.LEAVE_DATE >= @curDt))
  insert into @result values(@curDt, @curEnr)
  set @curDt = dateadd(week, 1, @curDt)
end;
 
select replace (str (month(WEEK)) + '/' + str (day(WEEK)) + '/' + str (year(WEEK)), ' ', '') as 'WEEK', ENRCNT from @result