Sql Server Date Range

Question

I am working on a hotel room booking project. I have a table in which i create a discount promotion. In this table I have PromotionStartDate and PromotionEndDate. For example I have a promotion which is starting from 03-aug-14 to 07-Aug-14. If a person search availability of room from 1-Aug-14 to 10-Aug-14, so the query should show the result that discount will be applicable on 3,4,5,6 and 7 of August. The result which I want should be like :

Dates       :     DiscountStatus 
01-Aug-14   :          No
02-Aug-14   :          No
03-Aug-14   :          Yes
04-Aug-14   :          Yes
05-Aug-14   :          Yes
06-Aug-14   :          Yes
07-Aug-14   :          Yes
09-Aug-14   :          No
10-Aug-14   :          No

Answer 1

You can try this query. The idea is to generate sequence of dates between the booking start date and end date. Then check each date falls within the promotion date range or not.

DECLARE @startDate Datetime = '2014-08-01' -- search start date
DECLARE @endDate DateTime = '2014-08-01' -- search end date
;WITH Dates([Date]) --Generate sequence of dates
AS
(
    SELECT @startDate 
    UNION ALL
    SELECT DATEADD(DD,1,Dates.[Date]) 
    FROM Dates
    WHERE Dates.[Date] < @endDate
)
SELECT [Date],CASE WHEN Promotion.PromotionStartDate  IS NULL THEN 'No'
                   ELSE 'Yes' END AS DiscountStatus 
FROM Dates LEFT JOIN Promotion 
        ON Dates.Date  BETWEEN Promotion.PromotionStartDate 
        AND Promotion.PromotionEndDate;
GO

Answer 2

SELECT PromotionStarDate, CASE WHEN PromotionStarDate 
BETWEEN '2014-08-03' AND '2014-08-07'
THEN 'Yes' ELSE 'No'END AS DiscountStatus
FROM table_name