Select where 2 or more consecutive dates

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)

Select where 2 or more consecutive dates

Author

Topic

cgkitab
Starting Member

6 Posts

Posted - 2006-12-14 : 18:09:28

I have a table called MachineDtl (in SQL Server 2000)with the following fileds and data:

Date Machine# Value
1/1/06 100001 50
1/8/06 100001 -90
1/9/06 100001 -60
1/10/06 100001 -900
1/1/06 100008 10
1/2/06 100008 12
1/25/06 100008 -1
1/26/06 100008 -1

Is there a way to select only the records where a machine has a negative value on 2 or more consecutive dates?

For example I would only want the following returned from the sample above:

Date Machine# Value

1/8/06 100001 -90
1/9/06 100001 -60
1/10/06 100001 -900

1/25/06 100008 -1
1/26/06 100008 -1

Thanks!

Allan

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-14 : 20:00:32

do you have a pk column in the table? it would be an easier query to write if you did.

http://www.elsasoft.org

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-12-15 : 08:05:27

Hi cgkitab

Something like this?

--data
set dateformat mdy
create table MachineDtl (Date datetime, Machine# int, Value int)
create clustered index ix_MachineDtl_Date on MachineDtl(Date)
insert MachineDtl
select '1/1/06', 100001, 50
union all select '1/8/06', 100001, -90
union all select '1/9/06', 100001, -60
union all select '1/10/06', 100001, -900
union all select '1/1/06', 100008, 10
union all select '1/2/06', 100008, 12
union all select '1/25/06', 100008, -1
union all select '1/26/06', 100008, -1

--calculation
select distinct a.*
from MachineDtl a
inner join MachineDtl b on a.date in (dateadd(day, 1, b.date), dateadd(day, -1, b.date))
where a.value < 0 and b.value < 0

/*results
Date Machine# Value
----------------------- ----------- -----------
2006-01-08 00:00:00.000 100001 -90
2006-01-09 00:00:00.000 100001 -60
2006-01-10 00:00:00.000 100001 -900
2006-01-25 00:00:00.000 100008 -1
2006-01-26 00:00:00.000 100008 -1
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 10:44:38

Randall, where were you for long time?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-12-17 : 13:44:14

It's Mr Randall to you, Madivanan