Ok, I can kind of see why you would need the data designed this way but it may be worth your time to see if it could be designed differently.
However, you came for help so that is what we all should be doing instead of making judgement. We have all worked on DBs that have not been designed in the best way and had to live with it for whatever reason. (Stepping off my soap box..)
So I think you may want to put this as a stored proc that you pass in the dates and the show time and return a true false.
The conditions you need to check for are:
Is this during existing hold date period AND --Will assume this period can be null
Is this during existing hold time. -- will assume this has to be inputted. If all day would have a time like 00:00 - 24:59
I came up with the below script:
IF EXISTS (SELECT * FROM sys.tables where tables.name like '#myTable%')
begin
drop table #myTable
end;
CREATE TABLE #myTable
(
date_from date NULL,
date_till date NULL,
Time_showed varchar(20)
);
INSERT INTO #myTable
VALUES
('2017-06-05', '2017-07-05', '17:00 - 17:05'),
('2017-06-05', '2017-06-05', '08:00 - 08:05');
--test data
DECLARE
@test_from date,
@test_till as date,
@test_time_showed as varchar(20);
--SELECT
-- @test_from = '2017-06-12',
-- @test_till = '2017-07-05',
-- @test_time_showed = '17:02 - 17:06';--1
SELECT
@test_from = '2017-06-05',
@test_till = '2017-06-05',
@test_time_showed = '08:00 - 08:00';--0
--SELECT
-- @test_from = '2017-06-05',
-- @test_till = '2017-06-05',
-- @test_time_showed = '07:59 - 08:00';--0
--SELECT
-- @test_from = '2017-06-12',
-- @test_till = '2017-07-05',
-- @test_time_showed = '17:07 - 17:16';--0
--SPLIT
DECLARE @start_time time = CONVERT(TIME,RTRIM(SUBSTRING(@test_time_showed,0,CHARINDEX('-',@test_time_showed))));--you should turn this into a function (GetStartTime)
DECLARE @end_time time = CONVERT(TIME,LTRIM(SUBSTRING(@test_time_showed,CHARINDEX('-',@test_time_showed) + 1,len(@test_time_showed))));--you should turn this into a function (GetEndTime)
----checking values CAN BE DELETED
--SELECT @start_time,@end_time;
--SELECT date_from, date_till,
-- CONVERT(TIME,RTRIM(SUBSTRING(Time_showed,0,CHARINDEX('-',Time_showed)))) AS START_T,
-- CONVERT(TIME,LTRIM(SUBSTRING(Time_showed,CHARINDEX('-',Time_showed) + 1,len(Time_showed)))) AS END_T
--FROM #MYTABLE
--VALIDATION
--TODO: You need to add some validation to insure that you are getting the correct data.
--CHECK
-- IS
SELECT CASE
WHEN EXISTS ( SELECT date_from
FROM #myTable
WHERE
--check if any records on file is for any date in span
((date_from BETWEEN @test_from AND @test_till) OR
(date_till BETWEEN @test_from AND @test_till) ) AND
--if a record is for any date in span, check if times overlap (THE NASTY STUFF)
(
@start_time > CONVERT(TIME,RTRIM(SUBSTRING(Time_showed,0,CHARINDEX('-',Time_showed))))--you should turn this into a function (GetStartTime)
AND
@start_time < CONVERT(TIME,LTRIM(SUBSTRING(Time_showed,CHARINDEX('-',Time_showed) + 1,len(Time_showed))))--you should turn this into a function (GetEndTime)
) OR
(
@end_time > CONVERT(TIME,RTRIM(SUBSTRING(Time_showed,0,CHARINDEX('-',Time_showed))))--you should turn this into a function (GetStartTime)
AND
@end_time < CONVERT(TIME,LTRIM(SUBSTRING(Time_showed,CHARINDEX('-',Time_showed) + 1,len(Time_showed))))--you should turn this into a function (GetEndTime)
)
)
THEN 1 --TIME PERIOD ALREADY USED
ELSE 0 --TIME PERIOD NOT USED
END;
Note: this is just a start script and there are some notes where I think you need to add some additional functionality.
Also, you would want to turn this into a stored procedure as I mentioned earlier and create two helper functions to handle the splitting and conversion to time as I can see you will need this other places.
I didn't use between because as you stated the actual time taken is between the 'time_showed' (...time between 17:02 and 17:06 is taken (17:03-17:05)... ). 'BETWEEN' will match to '08:00 - 08:05' and '08:05 - 09:45'.
