CODEWITHSUNDEEP
phpmysql
Shahid Ahmed
Shahid Ahmed

Reputation: 494

Combined sequential dates

I have the following data in my table

table data

on the basis of shift_id I want to combined the shift dates.

in short I want the following output : 

**output format : FromDate To ToDate (Shift_id)**

2012-12-01 To 2012-12-02 (2)

2012-12-03 (1)

2012-12-04 (2)

2012-12-18 To 2012-12-20 (1)

2012-12-21 To 2012-12-22 (2)

2012-12-23 To 2012-12-24 (1)

2012-12-25 To 2012-12-30 (2)

I tried couple of things but no luck. any one please help/guide me to solve this.

I wrote this code 

    while($row_shift = mysql_fetch_object($result_shift)) {
    $new_date = $row_shift->shift_date;
    $new_shift = $row_shift->shift_id;
    if($new_shift !== $old_shift) {
        $shift_name = $new_shift;
        $shift_date_1 =  date('D d M',strtotime($new_date))." (".$shift_name.")";               
    } else {
        $shift_name = $old_shift;
        $shift_date_1 =  date('D d M',strtotime($old_date))." (".$shift_name.")";
        $diff = abs(floor( (strtotime($new_date) - strtotime($old_date)) / 3600 / 24));
        if($diff == 1) {                    
            $to_part = ' To ';
            $shift_date_2 = date('D d M',strtotime($new_date))." (".$shift_name.")";

        } else {
            $to_part = ' To ';
            $shift_date_2 = date('D d M',strtotime($old_date))." (".$shift_name.")";    
        }
    }

    $s_d_2 = $to_part.$shift_date_2;

    $shift_date[] = $shift_date_1.$s_d_2;

    $old_date = $row_shift->shift_date;
    $old_shift = $row_shift->shift_id;

}
echo implode("<br/>", $shift_date);

and output of this code is 

Output : 
Sat 01 Dec (2)
Sat 01 Dec (2) To Sun 02 Dec (2)
Mon 03 Dec (1)Sun 02 Dec (2)
Tue 04 Dec (2)Sun 02 Dec (2)
Tue 18 Dec (1)Sun 02 Dec (2)
Tue 18 Dec (1) To Wed 19 Dec (1)
Wed 19 Dec (1) To Thu 20 Dec (1)
Fri 21 Dec (2)Thu 20 Dec (1)
Fri 21 Dec (2) To Sat 22 Dec (2)
Sun 23 Dec (1)Sat 22 Dec (2)
Sun 23 Dec (1) To Mon 24 Dec (1)
Tue 25 Dec (2)Mon 24 Dec (1)
Tue 25 Dec (2) To Wed 26 Dec (2)
Wed 26 Dec (2) To Thu 27 Dec (2)
Thu 27 Dec (2) To Fri 28 Dec (2)
Fri 28 Dec (2) To Sat 29 Dec (2)
Sat 29 Dec (2) To Sun 30 Dec (2)

Thanks.

Upvotes: 4

Views: 83

Answers (1)

valex
valex

Reputation: 24144

Here is a SQLFiddle demo

select min(shift_date) start_data,max(shift_date) end_date,
          min(Shift_id) Shift_id
from
(
select t.*,if(shift_id<>@CurShift,@i:=@i+1,@i) Group_num,
       @CurShift:=shift_id 
from t, (select @CurShift:=0,@i:=0) t1 
order by shift_date
 ) b

group by Group_num
order by Group_num

Upvotes: 3

Related Questions