CODEWITHSUNDEEP
mysql
VladN
VladN

Reputation: 739

MySQL Select, join, comparing dates fails

I have these two tables:

DateRanges

some_id   start_date    end_date
---------------------------------
1         2012-12-01   2012-12-15
1         2013-01-01   2013-01-15
3         2013-01-03   2013-01-10


Items

id      name
----------------
1     Some name
2     Other name
3     So on...

What I try to achieve is to get, for each element in Items table, the biggest start_date (ignoring the smaller dates/date ranges for that Item) and check if the current date is in that range, like in the next table (let's say today's 02 January 2013):

id       name          TodayIsInTheRange
---------------------------------------------
1      Some name             true
2      Other name            false
3      So on...              false

I have tried to obtain the 3rd table with the next query:

SELECT A.*, (B.`start_date` <= CURRENT_DATE AND CURRENT_DATE <= B.`end_date`) AS `TodayIsInTheRange`                                            
FROM `Items` as A                                                
LEFT JOIN `DateRanges` as B ON                                            
A.id = B.some_id
SORT BY B.`end_date` DESC

But with this query my items repeat themselves because I have two records in DateRanges for the same item.

Upvotes: 0

Views: 95

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT 
  a.*, 
  ( b.start_date <= CURRENT_DATE 
  AND CURRENT_DATE <= b.end_date ) AS TodayIsInTheRange 
FROM 
    Items AS a 
  LEFT JOIN 
    ( SELECT some_id, MAX(start_date) AS start_date
      FROM DateRanges
      GROUP BY some_id
    ) AS m
    JOIN 
      DateRanges AS b 
        ON b.some_id = m.some_id
      ON a.id = m.some_id
ORDER BY b.end_date DESC ;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

I use SQL Server, but I think something like this should be pretty close:

SELECT 
    I.Id, 
    I.Name,
    (DR.start_date <= CURRENT_DATE AND CURRENT_DATE <= DR.end_date) AS `TodayIsInTheRange`
FROM `Items` AS I
    LEFT JOIN 
        (SELECT Some_Id, MAX(Start_Date) as MaxStartDate 
        FROM `DateRanges`
        GROUP BY Some_ID) AS HDR ON I.Id = HDR.Some_Id
    LEFT JOIN `DateRanges` AS DR ON HDR.Some_Id = DR.Some_Id AND HDR.MaxStartDate = DR.Start_Date

Upvotes: 2

Luca Rainone
Luca Rainone

Reputation: 16458

try to use GROUP BY and MAX function. The first provide you only one row for each item.id, the second tell you if there is at least one date in your range

SELECT A.*, MAX(B.`start_date` <= CURRENT_DATE AND CURRENT_DATE <= B.`end_date`) AS `TodayIsInTheRange`                                            
FROM `Items` as A                                                
LEFT JOIN `DateRanges` as B ON                                            
A.id = B.some_id
GROUP BY A.id
ORDER BY B.`end_date` DESC

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

select * from items join date_ranges dr0 on items.id = dr0.some_id
  where start_date = 
       (select max(start_date) from date_ranges dr1 where dr0.some_id = dr1.some_id);

Upvotes: 1

Related Questions