Sample Widget - ENROLLMENT DAILY DELTA – DISTRICT

The SQL Query used in this widget is:

-- Enrollment by day

declare @yearGu uniqueidentifier
declare @startDt smalldatetime, @stopDt smalldatetime
set @yearGu = @FOCUS_YEAR_GU
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 (EDAY smalldatetime, ENRCNT int, DOW int, DELTA int)
declare @curEnr int
declare @lastVal int
declare @delta int
 
set @curDt = @startDt
set @lastVal = 0
 
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.LEAVE_DATE is null or ssy.LEAVE_DATE >= @curDt))
 
set @delta = @curEnr - @lastVal
if (@lastVal = 0)
    set @delta = 0
set @lastVal = @curEnr
  
if (DATEPART(dw, @curDt) <> 1 and DATEPART(dw, @curDt) <> 7)
  insert into @result values(@curDt, @curEnr, DATEPART(dw, @curDt), @delta);
set @curDt = dateadd(day, 1, @curDt)
 
end;
 
select replace (str (month(EDAY)) + '/' + str (day(EDAY)) + '/' + str (year(EDAY)), ' ', '') as 'DAY',
 ENRCNT, DELTA from @result