T-SQL. How to get a row having 2 date ranges that will be mapped to two date columns.

Question

The following are table data:

CREATE TABLE #tbl_period ( period_no INT, period_from SMALLDATETIME, period_to SMALLDATETIME ) INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (1, '2004-11-01 00:00:00', '2005-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (2, '2005-11-01 00:00:00', '2006-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (3, '2006-11-01 00:00:00', '2007-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (4, '2007-11-01 00:00:00', '2008-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (5, '2008-11-01 00:00:00', '2009-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (6, '2009-11-01 00:00:00', '2010-10-31 00:00:00') INSERT INTO #tbl_period (period_no, period_from, period_to) VALUES (7, '2010-11-01 00:00:00', '2011-10-31 00:00:00') SELECT * FROM #tbl_period DROP TABLE #tbl_period 

Now the goal is to get period_no, which has 2 given dates that will be compared with periods period_from and period_to.

 Example1: date1 = '2010-11-01 00:00:00' date2 = '2011-10-31 00:00:00' 

This will return 7 as period_no

 Example2: date1 = '2005-11-01 00:00:00' date2 = '2007-10-31 00:00:00' 

It should not return period_no, since the specified date range is not in the range period_from and period_to in the column in the row. Overlap dates are not allowed.

The rule is to get period_no if the 2 given date has a match or is in the range period_from and period_to a column in a row.

How to make this query in T-SQL?

Thanks in advance!

Answer 1

I believe this is what you need:

 SELECT * FROM #tbl_period WHERE @date1 >= period_from and @date2 <= period_to 

Tests:

 declare @date1 datetime = '2010-11-01 00:00:00' declare @date2 datetime = '2011-10-31 00:00:00' SELECT * FROM #tbl_period WHERE @date1 >= period_from and @date2 <= period_to 

returns

 period_no period_from period_to 7 2010-11-01 00:00:00 2011-10-31 00:00:00 

and

 set @date1 = '2005-11-01 00:00:00' set @date2 = '2007-10-31 00:00:00' SELECT * FROM #tbl_period WHERE @date1 >= period_from and @date2 <= period_to 

returns

 period_no period_from period_to