To get a range of values

Question

My table called TimeList with 2 columns SlotID(int identity) and SlotTime(varchar) in database is like this.

SlotID   SlotTime
1        8:00AM-8:15AM
2        8:15AM-8:30AM
3        8:30AM-8:45AM
4        8:45AM-9AM
5        9AM-9:30AM

likewise up to 6:45PM-7:00PM.

if i pass 2 parameters starttime and endtime as 8:00AM and endtime as 9AM,I want to retrieve first 4 rows in the above given table.Can anybody help to have such a stored procedure.

Answer 1

Would it be possible to refactor the table to look like this:

SlotID   SlotStart   SlotEnd
----------------------------
1        8:00am      8:15am
2        8:15am      8:30am
...

If you split the times into separate columns, it will be easier to query the date ranges. The query would look something like this:

@StartTime = '8:00am'
@EndTime = '9:00am'

select SlotID, SlotStart, SlotEnd
from Slots
where SlotStart >= @StartTime
and SlotEnd <= @EndTime

Answer 2

Your data is not properly normalized, so it will be hard to query. A field should only contain a single value, so you should have the starting and ending time for the slot in separate fields:

SlotID   StartTime   EndTime
1        8:00AM      8:15AM
2        8:15AM      8:30AM
3        8:30AM      8:45AM
4        8:45AM      9:00AM
5        9:00AM      9:30AM

This also allows you to use a datetime type for the fields instead of a textual data type, so that you can easily query the table:

select SlotId, StartTime, EndTime
from TimeList
where StartTime >= '8:00AM' and EndTime <= '9:00AM'

With your original table design, you would have to use string operations to split the values in the field, and convert the values to make it comparable. If you get a lot of data in the table, this will be a killer for performance, as the query can't make use of indexes.

Answer 3

The problem is that your table is not normalized. Please read up on that at http://en.wikipedia.org/wiki/Database_normalization , it can greatly improve the quality of the systems you design.

In your current case, please follow Andy's advice and separate SlotStart and SlotEnd. Your time format is not good either. Use a DateTime format (or whatever your database offers you as its time type) or a numerical type like INT to store your values (e.g. 1800 instead of 6:00PM).

Then you can easily use

SELECT FROM TimeList WHERE SlotStart>=... AND SlotEnd<=...

and select whatever you like from your table.