A seemingly simple query to construct yet not so much so ...

[rz2026]

Given a following table with two columns

date value
------------------------
01/01/2007 0
01/02/2007 1
01/05/2007 1
01/06/2007 1
01/07/2007 1
01/08/2007 -1
01/09/2007 -1
01/12/2007 1
01/13/2007 1
01/14/2007 1
01/15/2007 1
01/16/2007 0
01/17/2007 1


I'm having a tough time trying to construct a query to return all the dates for which it's value >=0 for the week before.

can this be done easily? (so i was told) yet my limited knowledge of sql doesn't lend me much luck here.


The expected results for the above table should be:

Jan 7 2007
Jan 16 2007
Jan 17 2007

since between 01/01/07 - 01/07/07 value >=0
and in the week prior to Jan 16th, (01/09/07 - 01/16/07) value>=0 even the data for Jan 10 and 11 were missing, it still satisfies the condition value >=0


Thanks a lot for your help.

[pbmods]

Heya, RZ.

To do this, you'll need to either use a couple of subqueries, a stored procedure, or probably the best way to do it would be to load the data into a processing language such as PHP and go from there.

To minimize the amount of work you have to do as time goes on, be sure to index your calculations, since I'm assuming that the values for dates in the past aren't likely to change.

[kaioshin00]

Not sure if this will work ... but you can try this:

SELECT    FROM_DAYS(TO_DAYS(t.date)+7) 
FROM    table_name t
WHERE    (t.value >= 0) 
 

If this does what I think it does, it takes dates where value >=0 and adds 7 to that, and spits that out as a date.

[rz2026]

Heya, RZ.

To do this, you'll need to either use a couple of subqueries, a stored procedure, or probably the best way to do it would be to load the data into a processing language such as PHP and go from there.

To minimize the amount of work you have to do as time goes on, be sure to index your calculations, since I'm assuming that the values for dates in the past aren't likely to change.

Hi, pbmods
Thanks for the suggestion ...

[rz2026]

Not sure if this will work ... but you can try this:

SELECT    FROM_DAYS(TO_DAYS(t.date)+7) 
FROM    table_name t
WHERE    (t.value >= 0) 
 

If this does what I think it does, it takes dates where value >=0 and adds 7 to that, and spits that out as a date.

kaioshin
understand what u r saying, not sure what exact syntax to use in sybase to test ... i'd be pleasantly surprised if one could get this done with just few lines.

[rz2026]

Hi. Got it working using stored precedure in mysql, a bit long than i expected but it does what i intent to do. Here is the code in case someone is interested.

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
 
create table t1(c1 date,c2 int);
create table t2(c1 date,c2 int);
create table t3(c1 date,c2 int);
 
insert into t1 values ('07/01/01',0),
('07/01/02',1),
('07/01/03',1),
('07/01/04',1),
('07/01/05',-1),
('07/01/06',1),
('07/01/07',1),
('07/01/08',1),
('07/01/09',1),
('07/01/10',1),
('07/01/11',1),
('07/01/12',1);
 
 
drop procedure if exists test1;
 
delimiter //
 
create procedure test1(IN period INT)
Begin
 DECLARE done INT DEFAULT 0;
 declare ctr int; 
 declare d1 date;
 declare v1 int;
 declare flag int;
 declare cur1 cursor for select c1,c2 from  t1;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 open cur1;
 delete from t2 where 1=1;
 delete from t3 where 1=1;
 set ctr=0;
 
 REPEAT
  fetch cur1 into  d1, v1;
  set ctr=ctr+1; 
  if  ctr > period  then 
  if NOT done THEN
    insert into t2 select * from t1 where t1.c1 between date_add(d1,interval -period day) and d1;
    select count(*) into flag from t2 where t2.c2 = -1;
    IF flag = 0 THEN
     insert into t3 select * from t1 where t1.c1 = d1;
    END IF;
    delete from t2 where 1=1;
   END IF;
  END IF;
  UNTIL done END REPEAT;
  close cur1;
  select c1 as date, c2 as value from t3;
END;
//
 
delimiter ;
 
select c1 as date, c2 as value from t1;
system echo "Return dates when last 5 days in a roll where value != -1"
call test1(4);
 
system echo 
update t1 set c2='-1' where c1='2007-01-09'; 
select c1 as date, c2 as value from t1;
 
system echo "Return dates when last 4 days in a roll where value != -1"
call test1(3);
 

Here is the results:

date           value
2007-01-01      0
2007-01-02      1
2007-01-03      1
2007-01-04      1
2007-01-05      -1
2007-01-06      1
2007-01-07      1
2007-01-08      1
2007-01-09      1
2007-01-10      1
2007-01-11      1
2007-01-12      1
Return dates when last 5 days in a roll where value != -1
date          value
2007-01-10      1
2007-01-11      1
2007-01-12      1
 
date           value
2007-01-01      0
2007-01-02      1
2007-01-03      1
2007-01-04      1
2007-01-05      -1
2007-01-06      1
2007-01-07      1
2007-01-08      1
2007-01-09      -1
2007-01-10      1
2007-01-11      1
2007-01-12      1
Return dates when last 4 days in a roll where value != -1
date          value
2007-01-04      1
 

[pbmods]

Heya, RZ.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

[rz2026]

Heya, RZ.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

Pbmods,

thanks for resetting the code type to mysql in my previous post,
the code reads much clearer.

great site!