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),
@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 @outline = '-----------------' + convert(varchar(2), @sessions) + '-------------------------------'
print @outline
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, @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 = 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

You rated this post out of 5. Change rating

Rate

You rated this post out of 5. Change rating