CODEWITHSUNDEEP
c#asp.netsqlsql-serverado.net
Shiv
Shiv

Reputation: 77

Compare between two dates in SQL query

I have a table, roomtype, with the following data:

Hotel_ID    Name     Rate   Season    StartSeason   EndSeason
   1      Deluxe/Ac  2700   New Year  2013-12-20    2014-01-10
   1      Deluxe/Ac  3100   31 Dec    2013-12-31    2012-12-31
   1      Deluxe/Ac  1700   Diwali    2013-11-01    2013-11-15
   1      Deluxe/Ac  800    Normal    NULL          NULL

For a given date (ie, 2013-11-09), I want to return the rate for the matching season, where the given date falls within the StartSeason and EndSeason dates.

ie, If my date is 2013-11-09, then the rate should be 1700. If my date is 2012-09-09, then the rate should be 800.

How do I create a stored procedure or write the SQL query?

Upvotes: 1

Views: 7688

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

To improve the hard work of the query optimizer you can help him uses the UNION ALL operator inside subquery

SELECT TOP 1 *
FROM (SELECT TOP 1 *
      FROM Table1
      WHERE @date BETWEEN StartSeason AND EndSeason
      UNION ALL
      SELECT TOP 1 *
      FROM Table1
      WHERE StartSeason IS NULL
      ) x
ORDER BY StartSeason DESC

Of course, in addition to this query, it would be great provide the correct index

CREATE INDEX x ON Table1(StartSeason)
  INCLUDE(EndSeason,Hotel_ID,Name,Rate,Season)

Plan Diagram

enter image description here

See demo on SQLFiddle

Upvotes: 1

ljs.dev
ljs.dev

Reputation: 4483

This should supply you with just the rate matching the season for the date in your query:

SELECT rate FROM roomtype 
   WHERE StartSeason >= '2013-11-09' AND EndSeason <= '2013-11-09';

Upvotes: 0

juergen d
juergen d

Reputation: 204746

select top 1 hotel_id, rate
from roomtype r
where '2013-11-09' between startseason and endseason
or startseason is null and endseason is null
order by case when startseason is null and endseason is null then 2 else 1 end

Upvotes: 2

Related Questions