CREATE FUNCTION blockdaynum(v_block_id int(10), v_school_date date) 
	RETURNS int(10)
	DETERMINISTIC

BEGIN

DECLARE v_num_days_in_block, v_block_day_offset, v_school_days, v_blocks_completed, v_test, v_current_block_day int(10);

DECLARE v_semester_start date;

select block_num_days into v_num_days_in_block from sis_course_block where block_id = v_block_id;

select semester_start, block_day_offset INTO v_semester_start, v_block_day_offset from sis_course_semester where v_school_date between semester_start and semester_end;

select workdaydiff(v_semester_start, v_school_date) into v_school_days;

set v_blocks_completed = floor(v_school_days / v_num_days_in_block);

set v_current_block_day = v_school_days - (v_num_days_in_block * v_blocks_completed);

IF v_current_block_day = 0 then

	set v_current_block_day = v_num_days_in_block;

END IF;

RETURN  v_current_block_day;

END