show until the condition match

[kadriirdak]

Hi
I have a query and I like to see the result until the condition match.
I am trying to find if the end date(+1day) is equal to next row start date if so list it.
I have the below query does the job but it also list whole table which i dont want that as i have gaps between the list.
you can see the dates are continuing until row 5 as the 6th row end date is 2014-12-21 and the next one is 2015-01-12 So i like to stop the query until i find the continuing dates.. How can I do this?

SELECT distinct t.start_date, t.end_date
FROM table t
INNER JOIN table ts  on  t.user_id = ts.user_id 
WHERE  t.start_date = (ts.end_date + INTERVAL '1' DAY) 
ORDER BY t.start_date DESC

listing;

Start date     End date
"2015-02-09";"2015-02-15"
"2015-02-02";"2015-02-08"
"2015-01-26";"2015-02-01"
"2015-01-19";"2015-01-25"
"2015-01-12";"2015-01-18" <- Stop here as the bellow row is not continuing..

"2014-12-15";"2014-12-21"
"2014-12-08";"2014-12-14"
"2014-12-01";"2014-12-07"

"2014-11-24";"2014-11-30"
"2014-11-17";"2014-11-23"
"2014-11-10";"2014-11-16"

[diafol]

I can't think of a way to do this with a straight query, but you can probably do it with a STORED PROCEDURE.

A stored procedure will allow you to LOOP until a certain IF condition is reached where you LEAVE the loop. However, I've never had cause to do this, so I'm just guessing a little bit.

[kadriirdak]

Thank you Diafol. I ended up doing with php..

[diafol]

Ok, probably easier, but you still end up retrieving the full table.
Stored procedures, while more succinct with regard to what data they retrieve can be quite slow, so even retrieving the full table and whittling it down may be faster than using a SP. It all depends on how quickly you reach the 'LEAVE' point.

ANyhow, any chance of you showing your code as this may be of use to others reading this thread?

[kadriirdak]

I see thanks for that.

here is what i did at the end;

while ($row = pg_fetch_row($sql)) {
        $res[] = $row;
       }



foreach($res AS $index=>$res1){

  $one_day_extra = date('Y-m-d',strtotime(@$res[$index+1][1] . "+1 days"));
  if ($res1[0] != $one_day_extra){ // stop the result if no more continuing rows
  break;
 }
}