CODEWITHSUNDEEP
mysqlsqljoin
PMa
PMa

Reputation: 1771

Getting Events Between Two Dates using MySql

I have this table: http://sqlfiddle.com/#!2/b4060/2

I then created two views as follow:

-- stack 1: hire
create view HS1H as
select a.* , min(a.`Effective_Date`)
from `Table1` a
left join `Table1` b on a.`Employee_ID` = b.`Employee_ID`
and a.`Effective_Date` > b.`Effective_Date` 
where a.`Event_Type` = "1_Hire"
group by a.`Employee_ID`;

select * from `hs1h`;

-- stack 1: termination
create view HS1T as
select a.* , min(a.`Effective_Date`)
from `Table1` a
left join `Table1` b on a.`Employee_ID` = b.`Employee_ID`
and a.`Effective_Date` > b.`Effective_Date` 
where a.`Event_Type` = "5_Term"
group by a.`Employee_ID`;

select * from `hs1t`;

I want to get the events that happen between first Hire date and first Term date. I used the qry below but returned no results:

select a.* 
from `Table1` a
join `hs1h` b on a.`Employee_ID` = b.`Employee_ID`
join `hs1t` c on a.`Employee_ID` = c.`Employee_ID`
where a.`Effective_Date` between b.`Effective_Date` and c.`Effective_Date`;

I am not sure what went wrong. I was able run the following two qrys. One returned the events after first hire date, and the other returned the events before first term date. But when I combine them like the one above, it didn't work.

select a.* 
from `Table1` a
join `hs1h` b on a.`Employee_ID` = b.`Employee_ID`
join `hs1t` c on a.`Employee_ID` = c.`Employee_ID`
where a.`Effective_Date` > b.`Effective_Date`;

select a.* 
from `Table1` a
join `hs1h` b on a.`Employee_ID` = b.`Employee_ID`
join `hs1t` c on a.`Employee_ID` = c.`Employee_ID`
where a.`Effective_Date` < c.`Effective_Date`;

Upvotes: 1

Views: 105

Answers (1)

Strawberry
Strawberry

Reputation: 33945

SELECT * 
FROM table1 
WHERE `effective date` 
BETWEEN (select MIN(`effective date`) from `Table1` WHERE `event type` = '1_Hire') 
AND
(select MIN(`effective date`) FROM table1 WHERE `event type` = '5_Term')

For the 2nd or 3rd 'hire', things get a little more complicated, but something like this should work...

SELECT a.* 
  FROM TH_Sample_Data a
  JOIN (
  SELECT x.*
     , MIN(y.effective_date) end
     , @i := @i+1 rank
  FROM TH_Sample_Data x
  JOIN TH_Sample_Data y
    ON y.effective_date >= x.effective_date
   AND y.event_type = '5_Term'
     , (SELECT @i:=1) vars
  WHERE x.event_type = '1_Hire'
  GROUP
     BY x.id
    ) b
   ON a.effective_date BETWEEN b.effective_date and b.end
WHERE b.rank = 2;

Upvotes: 2

Related Questions