Decimal to Binary Conversion for event scheduling

,

This procedure was written to create an event schedule
    where the schedule may have non-consecutive days.
    The input for the procedure is START DATE, END DATE, and DAYMAP
    The DAYMAP input parameter is a decimal number whose binary representation
    describes the schedule days over potentially a 4 week period (read from right to left).
    For example: 0000000000000001101100110110 Decimal 6966
    represents a schedule of (week#-Day): 1-Mon 1-Tue 1-Thu 1-Fri 2-Mon 2-Tue 2-Thu 2-Fri
    This examples is partitioned into 4 sessions (at least one day is skipped)
   
    This procedure determines the days of week for the schedule,
    session dates, and duration of each session.
    There are several print statements in the procedure so you can see some
    intermediate results.

-- Decimal to Binary conversion used for event scheduling
-- the following is example data which of course can be parameters to a stored procedure	
declare 
	@daymap int,
	@begindt datetime,
	@enddt datetime

select @daymap = 6966
select @begindt = 'May 30 2002  9:00AM'
select @enddt = 'Jun 3 2002  5:00PM'

-- end of example data

-- create temp table to hold session information

CREATE TABLE #sessions (
	s_SessionStart datetime,
	s_SessionEnds datetime,
	s_TeachingDays money
	)


declare	@t int,
	@thold int, 
	@quotient int, 
	@remainder int, 
	@length int,
	@tchar varchar(1),
	@binchar varchar(28), 
	@classdays varchar(255),
	@dayscharmap varchar(28),
	@daysmapindex int,
	@dayliteral varchar(3),
	@dayadder int,
	@firstday int,
	@weeknum int,
	@sessions int,
	@sessionstart datetime,
	@sessionend datetime,
	@sessionduration int,
	@classindex int

declare @outline varchar(50)


select @t = @daymap
select @binchar = ""
select @dayscharmap = '7654321765432176543217654321'


/***** Convert daymap decimal number to binary representation *****/

while (@t >=1)

begin

select @quotient = floor(@t/2)
select @remainder = @t - @quotient*2
select @t = @quotient
select @binchar = convert(varchar(1), @remainder) + @binchar
select @outline =  "Q " + convert(varchar(1), @quotient) + " R " + convert(varchar(1), @remainder) + " BC " + @binchar
--print @outline

end

select @binchar = right('0000000000000000000000000000' + rtrim(@binchar), 28)


/***** Determine Session Information *****/


select @length = datalength(@binchar)
select @t = @length 

select @firstday = 0
select @classdays = 'Class begins on ' + convert(varchar(20), @begindt, 1) + ' Last day of class is ' + convert(varchar(20), @enddt, 1) + ' Class meets on '
select @sessions = 1
select @classindex = 0

while @t > 0
begin
	select @tchar = substring(@binchar, @t, 1)
	if @tchar = '1'
	begin
		select @daysmapindex = convert(int, substring(@dayscharmap, @t , 1))
		if @firstday = 0  
		BEGIN
			select @firstday = @t 
			select @thold = @t
			select @dayadder = 0 
			select @outline = '-----------------' + convert(varchar(2), @sessions) + '-------------------------------'
			print @outline
			select @sessionstart = dateadd(day, @dayadder, @begindt)
			select @sessionstart = convert(datetime, convert(varchar(10), @sessionstart, 101))
			select @outline = 'Session Start ' + convert(varchar(20), @sessionstart, 0)
			print @outline
		END
		select @dayliteral = case @daysmapindex
					when 1 then "Sun"
					when 2 then "Mon"
					when 3 then "Tue"
					when 4 then "Wed"
					when 5 then "Thu"
					when 6 then "Fri"
					when 7 then "Sat"
				end
		select @weeknum = case
					when @t > 21  then 1
					when @t > 14 and @t < 21 then 2
					when @t > 7 and @t < 14 then 3
					else 4
				end
		select @dayadder = @firstday - @t
		select @classdays = @classdays + " " + convert(varchar(1), @weeknum) + "-" + @dayliteral + "(" + convert(varchar(2), @dayadder) + ")"
		if @thold - @t > 1 
		BEGIN	
			select @classindex = @firstday - @thold
			--select @sessionend = dateadd(day, @dayadder - 1, @begindt)
			select @sessionend = dateadd(day, @classindex, @begindt)
			select @sessionend = convert(datetime, convert(varchar(10), @sessionend, 101))
			select @outline = 'Session End ' + convert(varchar(20), @sessionend, 0)
			print @outline
			select @sessionduration = datediff(day, @sessionstart, @sessionend) + 1
			select @outline = 'Session Duration ' + convert(varchar(5), @sessionduration)
			print @outline

			/***** Insert Session Row *****/
			insert into #sessions values (
				@sessionstart,
				@sessionend,
				@sessionduration
				)
				
			
			select @sessions = @sessions + 1
			select @outline = '-----------------' + convert(varchar(2), @sessions) + '-------------------------------'
			print @outline

			select @sessionstart = dateadd(day, @dayadder, @begindt)
			select @sessionstart = convert(datetime, convert(varchar(10), @sessionstart, 101))
			select @outline = 'Session Start ' + convert(varchar(20), @sessionstart, 0) 
			print @outline
			
		END
		select @thold = @t
		
	end 
	select @t = @t - 1
end
select @classindex = @firstday - @thold
select @sessionend = dateadd(day, @classindex, @begindt)
select @sessionend = convert(datetime, convert(varchar(10), @sessionend, 101))
select @outline = 'Session End ' + convert(varchar(20), @sessionend, 0)
print @outline
select @sessionduration = datediff(day, @sessionstart, @sessionend) + 1
select @outline = 'Session Duration ' + convert(varchar(5), @sessionduration)
print @outline
select @outline = '------------------------------------------------'
print @outline

/***** Insert Session Row *****/
			insert into #sessions values (
				@sessionstart,
				@sessionend,
				@sessionduration
				)

select 
	binstring = @binchar,
	sessions = @sessions,
	classdays = @classdays

select * from #sessions
drop table #sessions

Rate

Share

Share

Rate