Search between two dates and sort by months

Question

I am new with search records between two dates and show records sorted by months (from January through December).

I have table like this.

Employee  |  Salary  |  Date from  |  Date To  
John A.   |   15000  | 2013-05-26  |  2013-06-10  
Mark      |   15000  | 2013-05-26  |  2013-06-10  
John A.   |   15000  | 2013-06-11  |  2013-06-25 
Mark      |   20000  | 2013-06-11  |  2013-06-25

I want the report to be shown something like this.

Employee  |   26 May - June 10   |  11 June - 25 June   | So on..  
John A.   |          15000       |         15000 
Mark      |          15000       |         20000

Please see my codes. this will only search the records between two dates

SELECT * 
FROM payroll
WHERE datefrom >= '2013-01-01' 
AND dateto < '2013-12-31'

Please give me an idea how to resolve the situation.

What you're looking for is called a pivot table. Take a look at [this thread](http://stackoverflow.com/q/12598120/2088851) — Voitcus, Jun 27 '13 at 07:30
my only problem is the sorting of months. @Let'sCode this grouping is like my annual summary of records. — Jason, Jun 27 '13 at 07:33
Do you mean actual sorting or presenting date periods as columns using SQL? Your sample output and comments are confusing. — peterm, Jun 27 '13 at 07:38
Sorry for the confusion @peterm let's summarize it, I need to filter records of salary by dates, assumed from January to December, and I want the records to be show sorted from January to December. — Jason, Jun 27 '13 at 07:46
Unfortunately your summary didn't clarify a bit. Let me rephrase the question. Do you simply need to order the resultset from your table (4 columns as it is), assuming that you build the pivot representation in php, or you need to get the result set in the form that you presented in your question where you have columns for time periods as in Stepans' answer? — peterm, Jun 27 '13 at 07:52

score 0 · Answer 1 · answered Jun 27 '13 at 07:36

You need ti pivot the data, unfortunately in mysql the pivot is static so you need to write it for each case (but you can do that with a script) , bellow you have a sample just for the sample data but it can be continued.

Try this:

SELECT  
    employee,
    SUM(IF(datefrom='2013-05-26' AND dateto='2013-06-10',Salary,0)) as `26 May - June 10`,
    SUM(IF(datefrom='2013-06-11' AND dateto='2013-06-25',Salary,0)) as `11 June - 25 June`
FROM 
    payroll
WHERE 
    datefrom >= '2013-01-01' 
    AND dateto < '2013-12-31' 
GROUP BY
    employee

score 0 · Answer 2 · answered Jun 27 '13 at 09:51

Try it using php

  <?php

// connection
$dns = "mysql:host=localhost;dbname=***";
$dbh = new PDO($dns, 'user', '***');

// sql
$query = "SELECT DISTINCT (CONCAT(`datefrom` ,' ', `dateto` )) as `formated date`
          FROM empdetail";

$stmt   = $dbh->prepare($query);
$stmt->execute();

$case_string = '';

while($r = $stmt->fetch(PDO::FETCH_ASSOC))
{
   list($fromdate,$todate) = explode(' ',$r['formated date']);
   $from                   = date('d-M',strtotime($fromdate));
   $to                     = date('d-M',strtotime($todate));
   $case_string.= "SUM(IF(datefrom='{$fromdate}' AND dateto='{$todate}',Salary,0)) as `{$from} to {$to}`,";

}

$case_string  = rtrim($case_string,',');


$sql = "SELECT employee, {$case_string}
        FROM empdetail 
        GROUP BY employee";

$stmt1 = $dbh->prepare($sql);
$stmt1->execute();
while($r = $stmt1->fetch(PDO::FETCH_ASSOC))
{
   // do whatever you want
}
?>

OUTPUT WHEN FINAL SQL RUN IN PHYMYADMIN

╔════════════╦═══════════════════╦══════════════════╗
║  employee  ║ 26-May to 10-Jun  ║ 11-Jun to 25-Jun ║
╠════════════╬═══════════════════╬══════════════════╣
║ john       ║            15000  ║            15000 ║
║ Mark       ║            15000  ║            20000 ║
╚════════════╩═══════════════════╩══════════════════╝

Search between two dates and sort by months

2 Answers2