I asked this question earlier and accepted an answer that proposed a CLR function as a solution. However that solution turns out to be unacceptable, as our function will be delpoyed on several client servers offsite and we are not able to guarentee the security settings will allow us to run a CLR function.
The database has a VARBINARY(288) column (lets call it Availability). This column represents the availability of a time period within a span of time. Each byte represents a five-minute interval, and the length of the value is reflected by the start and end time of the time span. Thus, each one-hour span is represented by 12 bytes. When the record is initially created, the Availability column is populated with all ones. Thus, for a two hour time span, the value will initially be 0x111111111111111111111111.
When a time period is taken, the corresponding bytes are changed to 0. The high-order byte represents the start time of the time span, thus, in the above example, if the two hour slot was from 8 AM to 10 AM, the first byte represents 8 AM, the second 8:05, the third 8:10 and so on. Thus, if a 15-minute appointment to be shown from 8:30 to 8:45 in the above example, the value would be changed to 0x111111000111111111111111.
The application is an appointment system. My role is data conversion to get the client's existing data into our database. Once in, going forward, the application will handle the settings, but for data conversion, I have to make sure everything is set properly in the database as I am doing conversion. As a result, I need three functions to accomplish various tasks:
1) Passing a 'base' start time, an appointment start and end time, and the VARBINARY value, I need a function to return either true or false if the requested time slot is available (all bytes are 1s).
2) Passing the same parameters, I need to return an updated VARBINARY value that represents the value after marking a slot as taken (setting the appropriate bytes to 0)
3) Passing the same parameters, I need to return an updated VARBINARY value that represents the value after marking a slot as available (setting the appropriate bytes to 1), which would happen if I convert a record that shows an appointment, but a subsequent record indicates the appointment was cancelled or rescheduled.
Using the same example above, if I wanted to set an appointment from 8:30 to 8:45 in a slot that represented from 8 AM to 10 AM, I would pass 8:00 AM, 8:30 AM, 8:45 AM and the VARBINARY value.
Assistance GREATLY appreciated.