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.

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
Kieran
Kidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
> ) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >= @.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:
>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>sql

Find Consecutive Occurrences

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
DineshProbably, Please read the hint link in the sticky at the top of the forum to help us with more information|||Brett,

In my table I have details as below

CustID DateofPurchase PurchaseDetails
------------------
1 01/05/2006 Item1, Item2, Item3
2 01/06/2006 Item2, Item5, Item1, Item7
1 01/06/2006 Item4, Item5, Item6
3 01/06/2006 Item4, Item5, Item6
1 01/07/2006 Item3, Item4, Item5
3 01/07/2006 Item1, Item2, Item3
1 01/08/2006 Item1, Item2, Item3
2 01/08/2006 Item4, Item5, Item6
3 01/08/2006 Item4, Item5, Item6
2 01/09/2006 Item2, Item5, Item1, Item7
2 01/10/2006 Item1, Item2, Item3, Item4
1 01/10/2006 Item1, Item2, Item3, Item4
1 01/11/2006 Item4, Item5, Item6

I need the query which will return say Cust ID 1 has come 4 days in a row, or Cust ID 2 has come 3 days in a Row.
I want the below output

Cust ID No of Consecutive Days
1 4
2 3
3 3
2 2
1 2

Would this help. Please let me know.