Consecutive values

Hi,
Given the following dataset, how can I determine the maximum number of
consecutive Bs for a given A?
A B
- -
2 1
2 2
2 7
3 2
3 3
3 4
3 6
3 8
3 9
3 10
3 13
3 14
3 15
3 16
4 1
4 3
4 5
4 6
4 7
4 8
4 10
5...
So the output should resemble:
A MAX Count for B
- -
2 2 -- for 1 2
3 4 -- for 13 14 15 16
4 1 -- no consecutive numbers
5...
Thanks
JerryHomework assignment?
Tom Dacon
Dacon Software Consulting
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u1EPMXv5FHA.2600@.tk2msftngp13.phx.gbl...
> Hi,
> Given the following dataset, how can I determine the maximum number of
> consecutive Bs for a given A?
> A B
> - -
> 2 1
> 2 2
> 2 7
> 3 2
> 3 3
> 3 4
> 3 6
> 3 8
> 3 9
> 3 10
> 3 13
> 3 14
> 3 15
> 3 16
> 4 1
> 4 3
> 4 5
> 4 6
> 4 7
> 4 8
> 4 10
> 5...
> So the output should resemble:
> A MAX Count for B
> - -
> 2 2 -- for 1 2
> 3 4 -- for 13 14 15 16
> 4 1 -- no consecutive numbers
> 5...
> Thanks
> Jerry
>|||untested, as I'm at home:
select a, max(maxb - b + 1)
from(select a, b, (select max(b) from t t1 where t.a=t1.a and t1.b=t.b+
(select count(*) from t t2 where t2.a=t1.a and t.b<t2.b and t2.b<t1.b)
) maxb from t) t
group by a
could be easier with row_number()|||>Homework assignment?
I googled up:
Jerry Spivey MCT, MCSE, MCSD Senior SQL|||Homework...nah...question from a friend.
Yeah...MCDBA, MCP...just too many letters! ;-)
Good question though :-)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1131738216.490554.128630@.g43g2000cwa.googlegroups.com...
> I googled up:
> Jerry Spivey MCT, MCSE, MCSD Senior SQL
>|||Alexander,
Thanks for the post. The untested query yeilded the following resultset:
1 1
2 1
3 1
4 1
5 1
6 1
Any other queries to try?
Thanks
Jerry
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1131738038.314420.248450@.f14g2000cwb.googlegroups.com...
> untested, as I'm at home:
> select a, max(maxb - b + 1)
> from(select a, b, (select max(b) from t t1 where t.a=t1.a and t1.b=t.b+
> (select count(*) from t t2 where t2.a=t1.a and t.b<t2.b and t2.b<t1.b)
> ) maxb from t) t
> group by a
> could be easier with row_number()
>|||If my friend's enemy is my enemy, then is my friend's homework my homework?
:)
ML|||untested, as I'm still at home and bored:
assuming combination (a,b) is unique
select s1.a, max(s2.ctb-s1.ctb+1)
from
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s1,
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s2
where s1.a=s2.a
and (s2.b-s1.b)=(s2.ctb-s1.ctb)
group by s1.a
not sure what was wrong with the previous one, maybe because I used t
twice, both as the table name and as an alias.
another one:
select a, max(bmin - b +1) from
(
select left_end.a,left_end.b, min(right_end.b) bmin
from
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b+1)=t.b
from tb t)
) left_end,
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b-1)=t.b
from tb t)
) right_end
where left_end.a=right_end.a
and left_end.b<=right_end.b
group by left_end.a, left_end.b) intervals|||On Fri, 11 Nov 2005 11:26:19 -0800, Jerry Spivey wrote:

>Hi,
>Given the following dataset, how can I determine the maximum number of
>consecutive Bs for a given A?
(snip)
>A MAX Count for B
>- -
>2 2 -- for 1 2
>3 4 -- for 13 14 15 16
>4 1 -- no consecutive numbers
>5...
Hi Jerry,
Why should the series (4 5)/(4 6)/(4 7)/(4 8) not be reported as a
consecutive series? An error in your post, I presume.
The following is untested. Check out www.aspfaq.com/5006 if you prefer a
tested reply.
SELECT A, MAX(last - first)
FROM (SELECT f.A, f.B AS first, MIN(l.B) AS last
FROM YourTable AS f
INNER JOIN YourTable AS l
ON l.A = f.A
AND l.B >= f.B
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.A = f.A
AND b.B = f.B - 1)
AND NOT EXISTS
(SELECT *
FROM YourTable AS a
WHERE a.A = f.A
AND a.B = l.B + 1)
GROUP BY f.A, f.B) AS seq
GROUP BY A
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Consecutive Hours Query

This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>