Advanced MySQl query to fetch date specific records within the same table

Question

Hi, I need to findout a better and quick way to query MySQL table to get specific data for each day in a given date range, where the table has two date columns specifying start and end date.

Table example: Promotions columns:

ID   startDate     EndDate     Discount     CategoryID  
=============================================================
1    2010/08/01    2010/08/10     10.00        A
2    2010/08/01    2010/08/10     15.00        B
3    2010/08/11    2010/08/15     05.00        A
4    2010/08/11    2010/08/15     10.00        B

I know I can grab the match promotion from the following query when two date ranges are given

SELECT * FROM Promotions WHERE (startDate <= "2010/08/12" AND endDate >= "2010/08/15") AND CategoryID = A

The result for the above query will fetch the row with ID 3

However if the two date ranges in the above query was changed to something like the following

SELECT * FROM Promotions WHERE (startDate <= "2010/08/12" AND endDate >= "2010/08/18") AND CategoryID = A

I am not getting any matched result, as I can understand the logic do not match any of the stored data in the table.

That is why, I need a better solution where I can get the result matched even if the end date exceeds more than the end date of a promotion. However changing the logic will not give me the best answer, suppose I can get the result if I use the following query but this also doesn't solve me whole problem if I need to find discounts valid of each and every day in the date range.

SELECT * FROM Promotions WHERE (startDate <= "2010/08/12" AND CategoryID = A

The true result I need is a way to convert this query into a loop or using a temporary MySQL table in the memmory to fetch results for each day as given below.

Result I need to find for the Date Range 2010/08/12 to 2010/08/18

===================================================================
Date           Discount    
=========================
2010/08/12      05.00
2010/08/13      05.00
2010/08/14      05.00
2010/08/15      05.00
2010/08/16      null
2010/08/18      null

I hope my question and problem is well explained and if you need any further clarifications regarding this problem, please let me know.

Hope to see a great answer for this.

Answer 1

To query for items that are IN the date range, use:

SELECT * 
FROM Promotions 
WHERE (startDate >= "2010/08/12" AND endDate <= "2010/08/15") 
AND CategoryID = A

you want the start date the be greater than or equal, and the end date to be less than or equal.

Answer 2

You need a calendar table of dates that looks something like:

Create Table Calendar ( [Date] DateTime Not Null Primary Key )

This table would be static and would need only be populated once. It would contain one row for each day covering any date range on which you would want to query. With that, your query is simpler:

Select C.Date, P.Discount
From Calendar As C
    Left Join Promotions As P
        On C.Date Between P.StartDate And P.EndDate
            And P.Category = 'A'

Answer 3

I guess I have found the solution for my problem by doing some research. I found a small query from the answer linked here which can generate dates for given range virtually.

So here is the single query which I have been able to get the result I need.

Select dr.Day, P.Discount

from
(
select a.Date as Day
from (
    select curdate() + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 

union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all 

select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all 

select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date >= '2010-08-12' AND a.Date < '2010-08-18') dr LEFT JOIN

Promotions AS P ON dr.Day BETWEEN P.startDate AND P.EndDate
WHERE P.Category = 'A'

This have given me the result as what I wanted. Since I found the solution, I am presenting it here for others like me who might have similar question in the future.

I do thank you all that who have tried to help me solve this problem.