Count consecutive numbers

**Kenneth Downs** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

ChrisD wrote:
[color=blue]
>
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001
> for 2, etc. Ideally I'd feed it a single year to lookup
>[/color]

This works in Postgres, you'll have to change the "limit 1" to mssql TOP 1
syntax. Also note the hardcoded year on line 4, replace that with a
parameter.

Ironically, this only works if you specify the year. Without the year you
get spurious rows.

select a.yearno,b.year no,(a.yearno - b.yearno) + 1 as "years"
from customers a join customers b on a.custno = b.custno
where a.yearno > b.yearno
AND a.yearno = 2006
AND a.isCust = 'Y' and b.isCust = 'Y'
and not exists
(
select yearno
FROM customers x
WHERE x.custno = a.custno
AND x.yearno between b.yearno AND a.yearno
AND x.isCust = 'N'
)
order by b.yearno
limit 1

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

**Theo Peterbroers** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

"ChrisD" <spambucket@hot mail.com> wrote in message news:<Yfn2e.840 846$Xk.593396@p d7tw3no>...[color=blue]
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
>
> For example (simplified):
>
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
>
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
>
> SELECT * FROM #Customers
>[/color]
8<------ Obvious result omitted[color=blue]
>
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
>
> I'm resisting the urge to create cursor here -- anyone have any hints?
>
> ...Chris.[/color]

The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Just two hints ...

**Tzvika Barenholz** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

how about this:

select top 1 max(a.yearno)+1 as from_ ,b.yearno as to_ , b.yearno -(
max(a.yearno)+1 ) as consecutive_tim e from #customers a join #Customers
b on a.custno = b.custno and
a.iscust='N' and b.iscust='Y' and a.yearno < b.yearno
group by b.yearno
order by consecutive_tim e desc

i.e. get the max diff between an 'N' and the 'Y' after it

**strider5** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

create view cust as
select custno, yearno, isCust from Customers
union
select custno, min(yearno) - 1, 'N'
from Customers group by custno
go

select custno,yearno, iscust,
case iscust
when 'N' THEN 0
ELSE 1+(select count(*)
from cust a where a.custno = b.custno and
a.yearno < b.yearno and
(a.yearno >
(select max(yearno) from cust c where iscust = 'N' and yearno <
b.yearno and custno = b.custno))
) end as active_for
from cust b
where yearno >= (select min(yearno) from customers x where x.custno =
b.custno )
order by custno, yearno

**John Gilson** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

"ChrisD" <spambucket@hot mail.com> wrote in message news:Yfn2e.8408 46$Xk.593396@pd 7tw3no...[color=blue]
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
>
> For example (simplified):
>
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
>
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
>
> SELECT * FROM #Customers
>
> CustNo YearNo IsCust
> ----------- ----------- ------
> 999 2006 Y
> 999 2005 Y
> 999 2004 Y
> 999 2003 N
> 999 2002 N
> 999 2001 Y
> 999 2000 Y
>
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
>
> I'm resisting the urge to create cursor here -- anyone have any hints?
>
> ...Chris.[/color]

SELECT C.CustNo AS CustNo,
C.YearNo AS YearNo,
C.YearNo - MAX(FY.YearNo) + 1 AS YearTally
FROM #Customers AS C
INNER JOIN
(SELECT C1.CustNo, C1.YearNo
FROM #Customers AS C1
LEFT OUTER JOIN
#Customers AS C2
ON C1.CustNo = C2.CustNo AND
C2.YearNo = C1.YearNo - 1 AND
C2.IsCust = 'Y'
WHERE C1.IsCust = 'Y' AND C2.CustNo IS NULL) AS FY -- 1st year
ON FY.CustNo = C.CustNo AND
C.IsCust = 'Y' AND
FY.YearNo <= C.YearNo
GROUP BY C.CustNo, C.YearNo
ORDER BY CustNo, YearNo

--
JAG

**Theo Peterbroers** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

"ChrisD" <spambucket@hot mail.com> wrote in message news:<Yfn2e.840 846$Xk.593396@p d7tw3no>...[color=blue]
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
>
> For example (simplified):
>
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )[/color]
8<-----------Big snip[color=blue]
>
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
>
> I'm resisting the urge to create cursor here -- anyone have any hints?
>
> ...Chris.[/color]

As I said in my previous posting:
The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Chris wanted hints, not complete solutions.
The solutions offered are likely to fail (I didn't test this)
if there is an 'active' year without any 'inactive' predecessor.

**John Gilson** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

"Theo Peterbroers" <peterbroers@fl oron.leidenuniv .nl> wrote in message
news:39bb2c10.0 503300659.231f1 c7c@posting.goo gle.com...[color=blue]
> "ChrisD" <spambucket@hot mail.com> wrote in message news:<Yfn2e.840 846$Xk.593396@p d7tw3no>...[color=green]
> > I'm trying extract a count of consecutive numbers, or "unbroken" years in
> > this case, at any particular given time.
> >
> > For example (simplified):
> >
> > CREATE TABLE #Customers
> > (
> > CustNo INT,
> > YearNo INT,
> > IsCust CHAR(1)
> > )[/color]
> 8<-----------Big snip[color=green]
> >
> > In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> > 2, etc. Ideally I'd feed it a single year to lookup
> >
> > I'm resisting the urge to create cursor here -- anyone have any hints?
> >
> > ...Chris.[/color]
>
> As I said in my previous posting:
> The computation you want to perform is a subtraction.
> There are some caveats concernig your data.
>
> Chris wanted hints, not complete solutions.[/color]

I didn't take that as his literal intention. If it was, a quick glance
will reveal a solution, but probably not lead to comprehension,
and he can choose to ignore it.
[color=blue]
> The solutions offered are likely to fail (I didn't test this)
> if there is an 'active' year without any 'inactive' predecessor.[/color]

His sample data includes an active year without an inactive
predecessor. As Chris was helpful enough to include DDL
and sample data, I assume all respondents who offered
complete solutions availed themselves of it. As far as I
can tell, my solution solves the problem.

--
JAG

**--CELKO--** · Jul 23 '05, 08:35 AM

Re: Count consecutive numbers

Yet another version, with a little-used predicate!

SELECT X.cust_nbr, MIN(X.start_dat e) AS start_date, X.end_date
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_yea r)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_date;

**--CELKO--** · Jul 23 '05, 08:36 AM

Re: Count consecutive numbers

Opps! fix my typos:

SELECT X.cust_nbr, MIN(X.start_yea r) AS start_date, X.end_year
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_yea r)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_year;

**ChrisD** · Jul 23 '05, 08:37 AM

Re: Count consecutive numbers

ChrisD wrote:
[color=blue]
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1,
> 2001 for 2, etc. Ideally I'd feed it a single year to lookup[/color]

Thanks all for the nudges!

I was able to make this work using a combination of John's and Kenneth's
samples. Joe's works too.

In practice I will always have a previous year -- but I suppose it's a
always a good idea to check.

....Chris.

Count consecutive numbers

Count consecutive numbers

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment