Sample Widget - Enrollment by Day

-- Enrollment by day
 
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 = DateAdd (Month, -1, getDate())
set @stopDt = @DATE
 
-- The following is the SQL string that uses the above 3 parameters
declare @curDt smalldatetime
declare @result table (TDAY smalldatetime, ENRCNT int, DOW 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))
if (DATEPART(dw, @curDt) <> 1 and DATEPART(dw, @curDt) <> 7)
  insert into @result values(@curDt, @curEnr, DATEPART(dw, @curDt));
set @curDt = dateadd(day, 1, @curDt)
 
end;
 
select replace (str (month(TDAY)) + '/' + str (day(TDAY)) + '/' + str (year(TDAY)), ' ', '') as 'TDAY', ENRCNT from @result