CODEWITHSUNDEEP
mysql
midtownguru
midtownguru

Reputation: 2723

MySQL: how to count 1 week from a certain date

I have the following table:

create table my_table
(
 SubjectID int,
 Date Date,
 Test_Value int
);

insert into my_table(SubjectID, Date, Test_Value)
values
(1, '2014-01-01', 55),
(1, '2014-01-05', 170),
(1, '2014-01-30', 160),
(2, '2014-01-02', 175),
(2, '2014-01-15', 40),
(2, '2014-01-18', 30),
(2, '2014-01-20', 166),
(3, '2014-01-05', 100),
(3, '2014-01-07', 75),
(3, '2014-01-11', 180),
(3, '2014-01-21',90)

I am trying to identify SubjectIDs who had Test_Value >=160 and had 2 or more tests within 7 days from the time s/he scored 160. For example, SubjectID 1 does not meet this condition. When he scored 160 on '2014-1-30', this two prior tests were taken more than one week ago. SubjectID 2 satisfies this condition because he scored 166 on '2014-01-20,' the two prior tests were taken within a week. SubjectID 3 also meets the condition by scoring 180 on '2014-01-11' and having two prior tests within a week.

Just to clarify, the test scores taken twice or more prior to scoring 160 do not matters. They have to be within a week from the date when subjects score 160 in order to satisfy the condition.

So far, I have written this code:

set @test = 0, @id=0, @count=0;


select 
 @count := if(SubjectID = @id, @count+1, 0) as count,
 @test :=  if(Test_Value >= 160, 1,  0 ) as Test_Index,
 @id := SubjectID as id
from my_table

I know this code does not give me the answer, but if I could incorporate relative time frame of one week into this code, the problem could be solved. I am not sure the code above is a good starting point. Could you add something to this code or write a completely different set of codes? Thank you for your help in advance.

Upvotes: 1

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think this does what you want, without variables:

select s160.*
from my_table s160 join
     my_table sprev
     on s160.test_value >= 160 and
        s160.SubjectId = sprev.SubjectId and
        sprev.Date between s160.Date - interval 7 day and s160.Date
group by s160.SubjectId, s160.Date, s160.Test_Value
having count(*) >= 3;

The count(*) >= 3 is because the count includes the test with the higher score as well. It would be helpful if this table had an actual id that was unique on all the rows.

Upvotes: 1

Related Questions