How to write the SQL Server query for this?

SELECT 
   [travel], [service], [bus_type], [travelid], [fro_m], [t_o], 
   [dep], [arr], [booking_closed], [discount], [faresleeper], 
   [rating], [seats], [s_no], 
   s1from, s1to, s1rate, 
   s2from, s2to, s2rate, 
   s3from, s3to, s3rate,
   [fare] = CASE 
               WHEN @date BETWEEN s1from AND s1to THEN s1rate 
               WHEN @date BETWEEN s2from AND s2to THEN s2rate 
               WHEN @date BETWEEN s3from AND s3to THEN s3rate 
               ELSE fare 
   END 
FROM 
   a1_volvo 
WHERE  
   ( fro_m = @fro_m ) AND ( t_o = @t_o ) 

the query mentioned above will show the different fare on between different dates ...

fare = textbox1.text   
date = label1.text (shows todays dates)
zero =label2.text

Date Slot 1

s1from = textbox2.text (choose date here 01-May-2011 )
s1to=textbox3.text ( choose date here 30-May-2011)
s1rate = textbox4.text ( rate between s1from & s1to ) say 400

Date Slot2

s2from = textbox5.text (choose date here 01-jun-2011 )
s3to=textbox5.text ( choose date here 30-Jun-2011)
s2rate = textbox7.text ( rate between s2from & s2to ) say 500

Date Slot 3

s3from = textbox8.text (choose date here 01-Jul-2011 )
s3to=textbox9.text ( choose date here 30-Jul-2011)
s3rate = textbox10.text ( rate between s3from & s3to ) say 600

if user select date between s1from - s1to then fare= s1rate
if user select date between s2from - s2to then fare= s2rate
if user select date between s3from - s3to then fare= s3rate

• If any user enter the date before s1from date then show actual fare
• If any user enter the date after s3to date then label2.text开发者_高级运维 will be shown

How to write the select query in this case ..

SELECT
    [travel], [service], [bus_type], [travelid], [fro_m], [t_o],
    [dep], [arr], [booking_closed], [discount], [faresleeper],
    [rating], [seats], [s_no], s1from, s1to,
    s1rate, s2from, s2to, s2rate, s3from, s3to, s3rate,
    CASE
        WHEN @date < s1from THEN fare
        WHEN @date BETWEEN s1from AND s1to THEN s1rate
        WHEN @date BETWEEN s2from AND s2to THEN s2rate
        WHEN @date BETWEEN s3from AND s3to THEN s3rate
        ELSE NULL
    END AS [fare]
FROM a1_volvo
WHERE
    ( fro_m = @fro_m ) AND ( t_o = @t_o ) 

It's not clear what you want to return in case the date is after s3to, I just put NULL because it's easy to test for. Also if you do the case logic in SQL then you probably don't need to return all the values involved in that logic (s1from, s1to, s1rate, s2from, s2to, s2rate, s3from, s3to, s3rate)