June 18, 2014 at 3:07 pm
I am trying to create a schedule/calendar. Table A shows my slots. Table B shows start/end times for when those slots are closed. I need to write an SQL statement that opens those slots in Table A which do not fall into the times that are closed in Table B.
To complicate matters, I also need to take into account an appointment duration. For example, my schedule starts from 12:00AM to 6:00AM. It would appear that I could update the open_closed column to 'OPEN' where the schedule_time = '2014-06-17 00:00:00.000' since the slot is not closed until beginning at '2014-06-17 01:30:00.000'. However, what if my appointment duration is 2 hours? That would mean I could not update the '2014-06-17 00:00:00.000' row on Table A because the slot is closed beginning at 1:30.
For this example, each row on table A represents an hour. Row 1 is from 00:00 to 00:59.
Row 2 is from 1:00 to 1:59.
I hope this is all understandable.
create table #temp_table_A
(schedule_time datetime, open_closed varchar(10))
Insert Into #temp_table_A
Values ('2014-06-17 00:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 01:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 02:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 03:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 04:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 05:00:00.000', 'CLOSED')
Insert Into #temp_table_A
Values ('2014-06-17 06:00:00.000', 'CLOSED')
create table #temp_table_B
(close_start_dt datetime, close_end_dt datetime)
Insert Into #temp_table_B
Values ('2014-06-17 01:30:00.000','2014-06-17 01:45:00.000')
Insert Into #temp_table_B
Values ('2014-06-17 02:30:00.000','2014-06-17 02:45:00.000')
Insert Into #temp_table_B
Values ('2014-06-17 03:00:00.000','2014-06-17 03:30:00.000')
Insert Into #temp_table_B
Values ('2014-06-17 04:00:00.000','2014-06-17 04:15:00.000')
Insert Into #temp_table_B
Values ('2014-06-17 08:00:00.000','2014-06-17 09:00:00.000')
June 18, 2014 at 8:54 pm
I think I understand what you're after. If I'm interpreting it correctly, it sounds like a classical gaps and islands problem. There are a multiple approaches to it. A short forum discussion that led the OP to a solution using dates is located at http://qa.sqlservercentral.com/Forums/Topic572931-392-1.aspx. If that doesn't meet your needs, search this site for the phrase "gaps" and you're bound to find something that works for you.
Sorry I don't have code for you, but I'm at home and really don't want to remote in to work.
HTH
June 19, 2014 at 2:38 am
Is it something along these lines that you are after?
😎
SELECT
TBO.schedule_time
,'OPEN' AS open_closed
FROM
(
SELECT
TA.schedule_time
FROM #temp_table_A TA
EXCEPT
SELECT
TA.schedule_time
FROM #temp_table_A TA
OUTER APPLY #temp_table_B TB
WHERE TA.schedule_time >= TB.close_start_dt
AND TA.schedule_time <= TB.close_end_dt
) AS TBO
Results
schedule_time open_closed
----------------------- -----------
2014-06-17 00:00:00.000 OPEN
2014-06-17 01:00:00.000 OPEN
2014-06-17 02:00:00.000 OPEN
2014-06-17 05:00:00.000 OPEN
2014-06-17 06:00:00.000 OPEN
June 19, 2014 at 8:12 am
I will give it a try thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply