Finding Consecutive Values

I'm stuck in trying to find out how to solve this.
Within a record set, I need to know when a id has been a certain value
for x consecutive years. For example,
Create table #Test (
tid int NOT NULL,
yr int NOT NULL,
value int NOT NULL)
insert into #Test values (100, 1998, 0)
insert into #Test values (100, 1999, 0)
insert into #Test values (100, 2000, 0)
insert into #Test values (100, 2001, 0)
insert into #Test values (100, 2002, 1)
insert into #Test values (100, 2003, 0)
insert into #Test values (100, 2004, 0)
insert into #Test values (100, 2005, 0)
insert into #Test values (100, 2006, 0)
insert into #Test values (100, 2007, 0)
insert into #Test values (100, 2008, 1)
insert into #Test values (200, 1999, 0)
insert into #Test values (200, 2001, 0)
insert into #Test values (200, 2002, 0)
insert into #Test values (300, 2001, 0)
insert into #Test values (300, 2002, 0)
insert into #Test values (300, 2003, 0)
insert into #Test values (300, 2004, 0)
insert into #Test values (300, 2005, 0)
FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
months.
RESULTS would yield
tid startdate enddate years
100 1998 2001 4
100 2003 2007 5
300 2001 2005 5
TIAselect t_from.tid, t_from.yr start_year, t_to.yr end_year, t_from.value
from
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr+1))
)t_from
join
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr-1))
)t_to
on t_from.tid=t_to.tid and t_from.value=t_to.value and
(t_from.yr+3)<=t_to.yr
and t_from.yr + (select count(*) from Test t where t_from.tid=t.tid
and t_from.yr<t.yr and t.yr<t_to.yr and t_from.value=t.value) + 1 =
t_to.yr
order by t_from.tid, t_from.yr
tid start_year end_year value
-- -- -- --
100 1998 2001 0
100 2003 2007 0
300 2001 2005 0
(3 row(s) affected)|||select tid, min(yr) as start, maxyr as [end], count(*) as yrs
from (
select *,
isnull(
(select max(yr)
from #test t2
where t2.tid=t1.tid
and t2.yr>=t1.yr
and t2.value=0
and t2.yr<=(select min(yr) from #test where tid=t2.tid and
yr>=t1.yr and value=1)
),
(select max(yr)
from #test t3
where t3.tid=t1.tid
)
) as maxyr
from #test t1
where value=0
) t4
group by tid, maxyr
having count(*)>=4
order by tid, start
carmaboy@.gmail.com wrote:
> I'm stuck in trying to find out how to solve this.
> Within a record set, I need to know when a id has been a certain value
> for x consecutive years. For example,
> Create table #Test (
> tid int NOT NULL,
> yr int NOT NULL,
> value int NOT NULL)
> insert into #Test values (100, 1998, 0)
> insert into #Test values (100, 1999, 0)
> insert into #Test values (100, 2000, 0)
> insert into #Test values (100, 2001, 0)
> insert into #Test values (100, 2002, 1)
> insert into #Test values (100, 2003, 0)
> insert into #Test values (100, 2004, 0)
> insert into #Test values (100, 2005, 0)
> insert into #Test values (100, 2006, 0)
> insert into #Test values (100, 2007, 0)
> insert into #Test values (100, 2008, 1)
> insert into #Test values (200, 1999, 0)
> insert into #Test values (200, 2001, 0)
> insert into #Test values (200, 2002, 0)
> insert into #Test values (300, 2001, 0)
> insert into #Test values (300, 2002, 0)
> insert into #Test values (300, 2003, 0)
> insert into #Test values (300, 2004, 0)
> insert into #Test values (300, 2005, 0)
>
> FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
> months.
> RESULTS would yield
> tid startdate enddate years
> 100 1998 2001 4
> 100 2003 2007 5
> 300 2001 2005 5
> TIA
>|||A different, shorter version:
DROP TABLE Foobar;
CREATE TABLE Foobar
(tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL);
INSERT INTO Foobar VALUES (100, 1998, 0);
INSERT INTO Foobar VALUES (100, 1999, 0);
INSERT INTO Foobar VALUES (100, 2000, 0);
INSERT INTO Foobar VALUES (100, 2001, 0);
INSERT INTO Foobar VALUES (100, 2002, 1);
INSERT INTO Foobar VALUES (100, 2003, 0);
INSERT INTO Foobar VALUES (100, 2004, 0);
INSERT INTO Foobar VALUES (100, 2005, 0);
INSERT INTO Foobar VALUES (100, 2006, 0);
INSERT INTO Foobar VALUES (100, 2007, 0);
INSERT INTO Foobar VALUES (100, 2008, 1);
INSERT INTO Foobar VALUES (200, 1999, 0);
INSERT INTO Foobar VALUES (200, 2001, 0);
INSERT INTO Foobar VALUES (200, 2002, 0);
INSERT INTO Foobar VALUES (300, 2001, 0);
INSERT INTO Foobar VALUES (300, 2002, 0);
INSERT INTO Foobar VALUES (300, 2003, 0);
INSERT INTO Foobar VALUES (300, 2004, 0);
INSERT INTO Foobar VALUES (300, 2005, 0);
SELECT X.tid, MIN(X.yr), X.end_yr, (X.end_yr - MIN(X.yr)) AS duration
FROM
(SELECT F1.tid, F1.yr, MAX(F2.yr) AS end_yr
FROM Foobar AS F1, Foobar AS F2
WHERE F1.yr < F2.yr
AND F1.tid = F2.tid
AND 0
= ALL (SELECT value
FROM Foobar AS F3
WHERE F1.tid = F3.tid
AND F3.yr BETWEEN F1.yr AND F2.yr)
AND (F2.yr - F1.yr +1)
= (SELECT COUNT(*)
FROM Foobar AS F4
WHERE F1.tid = F4.tid
AND F4.yr BETWEEN F1.yr AND F2.yr)
GROUP BY F1.tid, F1.yr) AS X(tid, yr, end_yr)
GROUP BY X.tid, X.end_yr
HAVING (X.end_yr - MIN(X.yr)) > 1;|||A thoughful solution that leaves one to ponder the rationality
of the methodology and of the environment that spawned it :)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e5k25kf9FHA.2844@.TK2MSFTNGP10.phx.gbl...
> select tid, min(yr) as start, maxyr as [end], count(*) as yrs
> from (
> select *,
> isnull(
> (select max(yr)
> from #test t2
> where t2.tid=t1.tid
> and t2.yr>=t1.yr
> and t2.value=0
> and t2.yr<=(select min(yr) from #test where tid=t2.tid and
> yr>=t1.yr and value=1)
> ),
> (select max(yr)
> from #test t3
> where t3.tid=t1.tid
> )
> ) as maxyr
> from #test t1
> where value=0
> ) t4
> group by tid, maxyr
> having count(*)>=4
> order by tid, start
>
> carmaboy@.gmail.com wrote:|||this problem would be solved like that? ;)
CREATE TABLE Puzzles..YearVals(
tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (tid, yr));
INSERT INTO Puzzles..YearVals VALUES (100, 1998, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 1999, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2000, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2002, 1);
INSERT INTO Puzzles..YearVals VALUES (100, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2005, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2006, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2007, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2008, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 1999, 0);
--INSERT INTO Puzzles..YearVals VALUES (200, 2000, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (200, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2005, 0);
SELECT tid, MIN(yr) AS beg_yr, MAX(yr) AS fin_yr, COUNT(*) AS tally
FROM
(SELECT B.tid,B.yr,B.yr-COUNT(*)
FROM Puzzles..YearVals AS B
JOIN Puzzles..YearVals AS L
ON L.tid = B.tid AND L.yr <= B.yr AND L.value=B.value
WHERE B.value = 0
GROUP BY B.tid,B.yr) AS G(tid,yr,grp)
GROUP BY grp,tid
HAVING COUNT(*) >= 4|||rrr... i did not remove a reference on my local db
sorry|||Thanks to everyone for taking the time to help. I should have all I
need to get my results.