views:

59

answers:

2
+3  Q: 

Filtering SQL Rows

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.

A: 

I don't think you can consider the date and time separately. If you want to know what was active between 1500 and 1700 on the 02/02/2010, and the row has an Active date of 01/01 and the Inactive date of 03/03, then it was active the entire day on 02/02, so the start/end time in the DB isn't relevant.

If the Active or Inactive date occurs on the date in question (02/02) then you need to consider the time.

I think you want something like this pseudo-code:

   (ActivateDate < @StartDate OR ( 
        ActivateDate = @StartDate And ActivateTime <= @ShiftStartTime )
    )
and (InActivateDate > @EndDate OR ( 
        InActivateDate = @EndDate And InActivateTime >= @ShiftEndTime )
    )
MikeW
MikeW thanks for the reply, but i need to slice time in each day. i.e if we take above example then the entry with Activate_Date 1/02/2010 18:00:00 with Inactive_Date 1/02/2010 20:00:00 should not be included.
Nitin Midha
A: 

Finally, I created a CTE which contains DateTime filter for each day in input date range and then joined that CTE with table and apply distinct to get the results.

Update: For simple Date range check please use:

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

Nitin Midha