I am working with a column used by the application that is bit mapped. It is defined as VARBINARY and can be up to 288 bits long (though in reality never will be).
- Each bit represents a five-minute interval from a given start time.
- There are exactly enough bits to represent each five-minute interval from the defined start time to a defined end time.
- The default value is all ones. Thus a period of 8:00 AM to 10:00 AM would be represented by '111111111111111111111111'
- If the time-slice of 8:00 AM to 8:15 Am is allocated, the column would be updated to the value of '000111111111111111111111'
- If the time-slice of 9:30 AM to 10:00 AM were also allocated, the column would be updated to the value of '000111111111111111000000'
- If a time-slice is unallocated, the appropriate bits need to be reset back to zeroes.
I would like to create a function that allows me to pass the start time, end time, the current bit-map, a starting time for the time slice, length of time and a bit to indicate I am setting or clearing the value and have it return the appropriate mask.
In the above example, if I wanted to set the 8:00 to 8:15 time-slice I would pass:
SELECT dbo.SetBitMap(0800, 1000, 0800, currentbitmap, 15, 1) -- bitmap defines the time from 0800 (first parameter) to 1000 (second parameter); the current value is currentbitmap (third parameter); I want to deal with a slice that begins at 0800 (fourth parameter); and set a 15 minute block (fifth parameter); the sixth parameter (1) indicates I want to set the bitmap.
Error checking would:
1) make sure the currentbitmap value is sufficient to hold values for start time through end time (e.g. 12 bits for each hour).
2) make sure timeslice start time (fourth parameter) and length (5th parameter) are valid for the bitmap (e.g. I cannot set a block from 9:45 for 20 minutes because that extends past 10:00, the end time defined for the bitmap).
3) if I am trying to set a map, make sure that no bits within the range are already set (e.g. if 9:00 to 9:20 are set and I try to set 8:50 to 9:10, that would be an error--overlapping times).
I am not so concerned about clearing a time-slice and checking to make sure the bits are set prior to clearing them, as I cannot see that having negative ramifications.
I am also not certain how to return an error condition since the return value can be anything from a 1 to 288 bit value, but if someone knows how to do it I would appreciate knowing how. Perhaps I just return the same value that was passed and if they are equal I know an error most likely occurred.
Thanks for your assistance.