0

I am trying to write a query for selecting records based on:

  1. They should belong to particular category

  2. They should be between Start date and End date.

    I have records like:

    EventId| EventName| Event_Category| Event_StartDate| Event_EndDate|
   1   |  aaa     |     4         |  2014-06-10    |  2014-06-15  |
   2   |  bbb     |     5         |  2014-06-10    |  2014-06-15  |
   3   |  ccc     |     6         |  2014-06-10    |  2014-07-11  |
   4   |  ddd     |     5         |  2014-06-01    |  2014-07-10  |
   5   |  eee     |     5         |  2014-06-10    |  2014-06-014 |

    Stored procudure:

    create proc usp_filterevnts @Categoryid int, @StartDate date, @EndDate date
as
begin 
select * from Events Where Event_Category=@Categoryid  and ( Event_Startdate>@StartDate and Event_Enddate<@EndDate )
end

    If i execute the sp as

      exec usp_filterevnts 5,'2014-06-09','2014-06-16'

    It should display records of Category 5Th records , whose start date and end date between '2014-06-09' and '2014-06-16 ..ie 2nd record, 5 th record..

    Is my stored procedure correct for this??

Tripp Kinetics
  • 5,178
  • 2
  • 23
  • 37
user3640046
  • 101
  • 1
  • 13

2 Answers2

0
select * from Events Where Event_Category=@Categoryid and (Event_Startdate between @StartDate and @EndDate) and (Event_Enddate between @StartDate and @EndDate)
Vaibhav Vidhate
  • 186
  • 1
  • 5
-1

(depending on table size, and whether the date field has an index...)

Your original syntax, but with >= and <= to be inclusive

Between causes a table scan, which won't matter too much if your table is small.

Bigpa
  • 1
  • 1
  • The operators themselves do *not* cause a table scan. A table scan occurs only if there is no index on the date column or the table is so small that using the index won't help. Actually, both `BETWEEN` and `>= <=` result in the same inclusive range query – Panagiotis Kanavos Jun 30 '14 at 15:16