SQL string filtering

Question

Table layout

ID     Activate_Date                InActivate_Date
1      2009-12-18 10:25:19.470  2010-01-13 08:32:30.130
2      2009-12-18 10:25:19.470  2010-01-13 08:32:30.253
3      2009-12-18 10:25:19.470  2010-01-13 08:32:30.363
4      2009-12-18 10:25:19.470  2010-01-13 08:32:30.583
5      2009-12-18 10:25:19.470  2010-01-13 08:32:30.473
6      2010-01-13 14:46:53.880  2010-01-13 14:50:45.443
7      2010-01-13 08:32:30.600  2010-01-13 14:46:23.833
8      2010-01-13 08:32:30.600  2010-01-13 14:46:23.833
9      2010-01-13 08:32:30.600  2010-01-13 14:46:23.833
10     2010-01-13 08:32:30.600  2010-01-13 14:46:23.833
11     2010-01-13 14:46:53.880  2010-01-13 14:50:45.443
12     2010-01-13 14:44:56.397  2010-01-13 14:46:23.833
13     2010-01-13 12:42:59.113  2010-01-13 14:46:23.833
14     2010-01-13 12:42:59.113  2010-01-13 14:46:23.833
15     2010-01-13 12:42:59.113  2010-01-13 14:46:23.833
16     2010-01-13 12:42:59.113  2010-01-13 14:46:23.833

Input parameters:

Declare @StartDate DateTime
Declare @EndDate DateTime

Declare @FirstShiftStartTime varchar(8)
Declare @FirstShiftEndTime varchar(8)

Set @StartDate = '1/01/2010'
Set @EndDate = '1/03/2010'

SET @FirstShiftStartTime = '15:00:00'
SET @FirstShiftEndTime = '17:00:00'

Output:

The output should consist of all lines so that the temporary filter is applied every day as a shift. Therefore, if we use these parameters, then the first output should consist of the first 5 lines from ID 1 to 5.

Therefore, the output filter should be applied as:

1/01/2010 from 15:00:00 to 17:00:00
2/02/2010 from 15:00:00 to 17:00:00
3/03/2010 from 15:00:00 to 17:00:00

I hope I made my clear:

I tried using:

((DatePart(Table.Activate_Date) <= @StartDate AND DatePart(Table.INActivate_Date) > @StartDate) OR
     (DatePart(Table.Activate_Date) >= @StartDate AND DatePart(Table.InActivate_Date) < @EndDate)
    )

AND ((TimePart(Table.Activate_Date) >=@FirstShiftStartTime ) AND (TimePart(Table.INActivate_Date) < @FirstShiftEndTime))

But this will not work if the activation date and inactivation date span several days.

Another way I could find is to create a temporary table with a loop every day, and then fill the temp table with filters, and then apply the method described above.

I hope there should be something simple, I think.

Any help would be greatly appreciated.

Answer 1

, CTE, DateTime , CTE .

. :

dr1.EndDate >= dr2.StartDate dr1.StartDate <= dr2.EndDate

Answer 2

, . , 1500 1700 02/02/2010, 01/01 03/03, 02/02, / .

(02/02), .

, - :

   (ActivateDate < @StartDate OR ( 
        ActivateDate = @StartDate And ActivateTime <= @ShiftStartTime )
    )
and (InActivateDate > @EndDate OR ( 
        InActivateDate = @EndDate And InActivateTime >= @ShiftEndTime )
    )