Finding Consecutive Negative Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding Consecutive Negative Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-24 : 07:56:08

Michael writes "I am really stumped here and I hope someone can help.

I need to find all occurances of 3 consecutive negative values in a data set. So if the reading is consistantly negative for 3 days, I want the data from the 3rd day. (location, product, date)

The following example shows 5 days of values, and 2 occurances of 3 day negative values.

M -1
T -5
W -3 <--gimme
Th -9 <--gimme
F +2

So I need the following returned (wednesday and thursdays data);


LOCATION    PROD  READDATE      
----------- ----- ----------- 
1           1     03/16/2005 
1           1     03/17/2005

Here is some sample data;

DECLARE @myTable table (
Location int,
Prod int,
ReadDate smalldatetime,
Value int )

insert into @myTable values(3,1,'12/11/2004',-29)
insert into @myTable values(3,1,'12/12/2003',163)
insert into @myTable values(3,1,'12/13/2003',-603)
insert into @myTable values(3,1,'12/14/2003',-553)
insert into @myTable values(3,1,'12/15/2003',-73)
insert into @myTable values(3,1,'12/16/2003',-476)
insert into @myTable values(3,2,'12/11/2003',-39)
insert into @myTable values(3,2,'12/12/2003',76)
insert into @myTable values(3,2,'12/13/2003',98)
insert into @myTable values(3,2,'12/14/2003',-4)
insert into @myTable values(3,2,'12/15/2003',-515)
insert into @myTable values(3,2,'12/16/2003',-23)
insert into @myTable values(6,1,'12/11/2003',904)
insert into @myTable values(6,1,'12/12/2003',-621)
insert into @myTable values(6,1,'12/13/2003',-466)
insert into @myTable values(6,1,'12/14/2003',-314)
insert into @myTable values(6,1,'12/15/2003',338)
insert into @myTable values(6,1,'12/16/2003',37)

Which should give me;


LOCATION    PROD  READDATE      
----------- ----- ----------- 
3           1     12/15/2003 
3           1     12/16/2003 
3           2     12/16/2003 
6           1     12/14/2003

Thanks in advance."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-03-24 : 08:31:29

select * from mytable a
inner join mytable b on a.location = b.location and a.prod = b.prod and a.readdate = dateadd(d,1,b.readdate)
inner join mytable c on b.location = c.location and b.prod = c.prod and b.readdate = dateadd(d,1,c.readdate)
where a.value <0 and b.value <0 and c.value<0

should be close.

however you do need to 'clarify' your definition of consecutative...because normally that an order.

What happens when there is no reading on a day...ie a weekend in the middle of a sequence of negative readings from friday and monday??

tinkerman
Starting Member

13 Posts

Posted - 2005-03-24 : 14:43:05

Wow, I didn't expect it to be that simple. Thanks!

By consecutive I meant, 3 consecutive days where the value is negative.

I check for missing days before I run this query using a crossjoin, but I guess I could nest it in this one as well.

tinkerman
Starting Member

13 Posts

Posted - 2005-06-14 : 09:39:34

So the requirements have changed and now there could be no reading in the middle of a sequence. How do I modify the query to get consecutive readings instead of consecutive dates?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-14 : 10:35:36

This might help you out:

http://www.sqlteam.com/item.asp?ItemID=12654

- Jeff

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-15 : 05:50:07

The last section on this may also help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51079

tinkerman
Starting Member

13 Posts

Posted - 2005-06-22 : 14:09:14

I am having trouble seeing the relevance of those posts to my problem. Can you post an example relating to my data?