SQL - Show offers between range

Question

I am currently working on constructing a SQL query around offers.

Basically an offer has a begin and end date.

Scenario 1: The user only specifies a date from.

Solution 1: All offers are display which start on or past that day.

Scenario 2: The user only specifies a TO date.

Solution 2: All offers are display which end on or before that given date.

Scenario 3: The user specifies both a TO and FROM date to search.

The issue with solution 3 is as follows.

OFFER - from 01-01-2012 to 03-03-2012

SEARCH - from 01-01-2012 to the 02-02-2012

The offer should return in the query as it falls between the two search values.

My current query is below, however its not working as required.

CREATE PROCEDURE [dbo].[GetAllOffers]
    @retailer_id        BIGINT,
    @opt_in             BIGINT,
    @use_once           BIGINT,
    @from_date          DATETIME,
    @to_date            DATETIME
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT      retr.Name,
                reco.Headline,
                reco.isOptIn,
                reco.isUseOnce,
                reco.DateValidFrom,
                reco.DateExpires,
                reco.Id                     AS OfferId

    FROM        RetailerCoupon              reco

    INNER JOIN  Retailer                    retr
    ON          reco.RetailerId             = retr.Id

    WHERE       (reco.RetailerId            = @retailer_id
    OR          @retailer_id                IS NULL)
    AND         (reco.isOptIn               = @opt_in
    OR          @opt_in                     IS NULL)
    AND         (reco.isUseOnce             = @use_once
    OR          @use_once                   IS NULL)
    AND         (reco.DateValidFrom         >= @from_date
    OR          @from_date                  IS NULL)
    AND         (reco.DateExpires           <= @to_date
    OR          @to_date                    IS NULL)

    ORDER BY    retr.Name   

END
GO

Please note scenarios 1 & 2 are covered by the query above its 3 which is causing a problem.

Steven

Check this discussion for how to select rows with a date range within a date range: http://stackoverflow.com/questions/730722/how-to-determine-if-a-date-range-occurs-any-time-within-another-date-range — PatrikAkerstrand, Apr 17 '12 at 09:45
Hi Patrik, the problem with that solution is I can have search criteria which only has a start date OR only has an end date OR has a time period. — swade1987, Apr 17 '12 at 09:48
So then you need to explain what should happen for each of these cases given different search criteria. — PatrikAkerstrand, Apr 17 '12 at 09:51

score 0 · Answer 1 · answered Apr 17 '12 at 11:46

I'm still testing this and trying to improve it, but does this follow your needs:

Some test data

declare @tmp table (offer nvarchar(30), startTime datetime, endTime datetime)
insert @tmp values ('Offer_1','2012-04-01','2012-04-10'),('Offer_2','2012-04-05','2012-04-15'),('Offer_3','2012-04-10','2012-04-20'),('Offer_!!!','2012-01-01','2012-03-03')

Offer_!!! is your example above

select  * 
from    @tmp 
where   
    -- Scenario1
    (@from_date <= startTime AND @to_date IS NULL)
OR  -- Scenario2
    (@from_date IS NULL AND @to_date <= endTime)
OR  -- Scenario3
    ((@from_date BETWEEN startTime AND endTime)
        AND
        (@to_date BETWEEN startTime AND endTime))

Gives:

-- Scenario 1
EXEC GetAllOffers @from_date = '2012-04-01'
    ,   @to_Date = null

offer                          startTime               endTime
------------------------------ ----------------------- -----------------------
Offer_1                        2012-04-01 00:00:00.000 2012-04-10 00:00:00.000
Offer_2                        2012-04-05 00:00:00.000 2012-04-15 00:00:00.000
Offer_3                        2012-04-10 00:00:00.000 2012-04-20 00:00:00.000


-- Scenario 2
EXEC GetAllOffers @from_date = null
    ,   @to_Date = '2012-01-09'

offer                          startTime               endTime
------------------------------ ----------------------- -----------------------
Offer_1                        2012-04-01 00:00:00.000 2012-04-10 00:00:00.000
Offer_2                        2012-04-05 00:00:00.000 2012-04-15 00:00:00.000
Offer_3                        2012-04-10 00:00:00.000 2012-04-20 00:00:00.000
Offer_!!!                      2012-01-01 00:00:00.000 2012-03-03 00:00:00.000


-- Scenario 3
EXEC GetAllOffers @from_date = '2012-01-01'
    ,   @to_Date = '2012-02-02'

offer                          startTime               endTime
------------------------------ ----------------------- -----------------------
Offer_!!!                      2012-01-01 00:00:00.000 2012-03-03 00:00:00.000

In my mind, there are some very large holes in your requirements, but this appears to do what you have asked in your scenarios

SQL - Show offers between range

1 Answers1