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 
