0

I have a table Emp_UnAvailablity_ID`, `PersonID`, `Is_UnAvailable`, `From_Date`, `To_Date` FROM `emp_unavailablity. I want to get list of all dates between From_Date and To_Date. I have tried this query SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity its output is in the attached image Query Output

Here is my table data Table Data

I have tried some php code to get list of dates but not worked for me.

$connection = Yii::app()->db;
    $sql = " 
        SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity ";
    $command = $connection->createCommand($sql);
    $dataReader = $command->queryAll();

    $newValues = array();
    foreach($dataReader as $key => $value) 
    {
        $newValues[$key] = $value;
        if($key == 'F') 
        {
            for ($i=0; $i < $key['D']; $i++) { 
                $xx = $key['F'] + $i;     //f + i
                $newValues = $xx;
            }
        }
    }
    print_r($newValues);

Please help me to solve this problem. Thanks

closed as unclear what you're asking by James Anderson, mustaccio, Tom V, Julien Vavasseur, Mark Sinkinson Jul 11 '16 at 15:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

Based on the accepted answer on Stack Overflow here, you can adapt it to fit your requirements:

select * 
from emp_unavailablity, 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datelist from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) as v
where datelist>from_date and datelist <to_date;

Basically we build an inline view with a list of all dates for the next few thousand years, and use that as a list to select from.

Sample SQL Fiddle here.

Not the answer you're looking for? Browse other questions tagged or ask your own question.