Hello All
I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.
So I've decided the best way to handle this is to assign a value to each day of the week like so:
MON = 1
TUE = 2
WED = 4
THU = 8
FRI = 16
SAT = 32
SUN = 64
So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.
So the values would be:
@.begin_date = '12/01/2004'
@.begin_time = '10:00 AM'
@.end_date = '02/27/2005'
@.end_time = '10:30 AM'
@.recur_days = 10
Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things
1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)
2. What is the best way to loop through the date range and create a record for each day?
Regards
RussI would probably create another table that decodes the possible values that you would come up with, for example
Create table decode (
TtlValue int,
PtValue int
)
Then have a row for each separate value like for 10
it would be
insert decode(TtlValue, PtValue)
values(10, 8)
insert decode(TtlValue, PtValue)
values(10, 2)
after you did that your proc could just "walk the table" looking for values
that equalled your sum value.
Example:
declare @.ttlvalue int,
@.x int
select @.ttlvalue = the value of your total sums
select @.x = min(PtValue)
from decode
where TtlValue = @.ttlvalue
while @.x is not null
BEGIN
do whatever you need in here then when you're finished, move to the next row
select @.x = min(PtValue)
from decode
where TtlValue = @.ttlvalue
and PtValue > @.x
END
hope that might help?
Nick|||Use of bitwise AND operator:
Declare @.TestDate int
Declare @.TestBitwise int
set @.TestDate = 2 --Tuesday
set @.TestBitwise = 10 --Tuesday and Thursday = 2 + 8
--Check for Tuesday:
if @.TestDate & @.TestBitwise = @.TestDate
select 'Yes, Tuesday'
else select 'No, not Tuesday'
set @.TestBitwise = 9 --Monday and Thursday = 1 + 8
--Check for Tuesday:
if @.TestDate & @.TestBitwise = @.TestDate
select 'Yes, Tuesday'
else select 'No, not Tuesday'
