Table Schema
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:
Output should be all the rows so that time filter should be applied on each day in form of a shift. So if we use these parameters then the out put should first 5 rows from ID 1 to 5.
So in 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 have made my self 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 Activate date and Inactivate date spans multiple days.
Another way i could find is to create a temp table with looping each day and then fill the temp table with filters and then apply the above method.
I am hoping there should be something simple then i am thinking.
Any help will be highly appreciated.