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 result Set

Hello,
I am having two tables ...
SendMails (SendMailID,sender,subject)
Data is
1, 'Sender1','Subject1'
2, 'Sender2','Subject2'
3, 'Sender3','Subject3'
RecMails(RecMailId,Recieved,Subject)
Data is
4, 'Rec1','Subject4'
5, 'Rec2','Subject5'
6, 'Rec3','Subject6'
I want in the Report as
1, 'Sender1','Subject1'
2, 'Sender2','Subject2'
3, 'Sender3','Subject3'
4, 'Rec1','Subject4'
5, 'Rec2','Subject5'
6, 'Rec3','Subject6'
I am unable to get this from a single Query...
so I decided to have two tables in the body with one dataset each ...but it
leaves a big blank space in between .. Is there some way possible for this
requirement..
I also tried putting two tables in one group each within a third table but
the blank space remains...
any Idea will be highly appreciated..
Cheers,
siajAre you using a database that does not support a UNION in the query?
Otherwise, UNION them and have them in the same dataset.
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:E449AE49-ECE2-4B23-979D-D877C5BE3BC4@.microsoft.com...
> Hello,
> I am having two tables ...
> SendMails (SendMailID,sender,subject)
> Data is
> 1, 'Sender1','Subject1'
> 2, 'Sender2','Subject2'
> 3, 'Sender3','Subject3'
>
> RecMails(RecMailId,Recieved,Subject)
> Data is
> 4, 'Rec1','Subject4'
> 5, 'Rec2','Subject5'
> 6, 'Rec3','Subject6'
> I want in the Report as
> 1, 'Sender1','Subject1'
> 2, 'Sender2','Subject2'
> 3, 'Sender3','Subject3'
> 4, 'Rec1','Subject4'
> 5, 'Rec2','Subject5'
> 6, 'Rec3','Subject6'
>
> I am unable to get this from a single Query...
> so I decided to have two tables in the body with one dataset each ...but
> it
> leaves a big blank space in between .. Is there some way possible for this
> requirement..
> I also tried putting two tables in one group each within a third table but
> the blank space remains...
> any Idea will be highly appreciated..
> Cheers,
> siaj|||Thanks Douglas...
I am using SQL Server 2000...and I can very well use Union Operator.. It
had never clicked me..
Cheers,
siaj
"Douglas McDowell" wrote:
> Are you using a database that does not support a UNION in the query?
> Otherwise, UNION them and have them in the same dataset.
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:E449AE49-ECE2-4B23-979D-D877C5BE3BC4@.microsoft.com...
> > Hello,
> >
> > I am having two tables ...
> >
> > SendMails (SendMailID,sender,subject)
> > Data is
> > 1, 'Sender1','Subject1'
> > 2, 'Sender2','Subject2'
> > 3, 'Sender3','Subject3'
> >
> >
> > RecMails(RecMailId,Recieved,Subject)
> > Data is
> > 4, 'Rec1','Subject4'
> > 5, 'Rec2','Subject5'
> > 6, 'Rec3','Subject6'
> >
> > I want in the Report as
> >
> > 1, 'Sender1','Subject1'
> > 2, 'Sender2','Subject2'
> > 3, 'Sender3','Subject3'
> > 4, 'Rec1','Subject4'
> > 5, 'Rec2','Subject5'
> > 6, 'Rec3','Subject6'
> >
> >
> > I am unable to get this from a single Query...
> >
> > so I decided to have two tables in the body with one dataset each ...but
> > it
> > leaves a big blank space in between .. Is there some way possible for this
> > requirement..
> > I also tried putting two tables in one group each within a third table but
> > the blank space remains...
> > any Idea will be highly appreciated..
> >
> > Cheers,
> > siaj
>
>

Consecutive numbering WITHOUT Identity

Hi,

I'mtrying to insert records from one table into another table. Thedestination table has a ROWID field which cannot be an identity key,but needs to 'act like' an identity key and have its value populatedwith (Max(ROWID) + 1) for each row added to the table.

To mythinking, simply using (Max(ROWID) + 1) in my SELECT statement will notwork as it will only be evaluated once so if I am adding 1000 recordsand Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of1235.

Is there a way to accomplish this?

Thanks

Hi,

you can increment the row id locally in your program after doing an update in a loop:
rowID = <select max rowid blablabla>
while <there are still rows to add>
Command.executenonquery() - using rowID
rowID +=1
Loop

you can also use a trigger in the database to update the rowid column after insert

|||

Yes.

You can readMax(ROWID) from database intoApplication("id") at the start of web application,e.g. 1234 to avoid frequent access to database.

After insert just add id by 1.

Hope it helps.

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.
>
sqlsql

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.
>

consecutive dates

Forgive the primitive nature of this question, but I'm just trying to do a list of dates (without weekends and perhaps without major holidays). Any help appreciated. (Excel Help said to use the autofill feature, but I can't find it!
Thanks,

Bill Fried

I would start with this article about "calendar tables:"

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

One way to get a list of calendar dates is to use a table of numbers and generate the list of dates by incrementing through the list of numbers. Something like this:

select cast('12/31/6' as datetime) + num

from numbers (nolock)

where num <= 365

|||

The following function will do.. The performance is not bad..

Code Snippet

Create Function dbo.FillDate(@.Startdate datetime, @.Enddate datetime)

Returns @.Data Table (Date DateTime)

as

Begin

While @.Startdate <= @.EndDate

Begin

Insert Into @.Data Select @.Startdate Where Datepart(w,@.Startdate) not in (1,7);

Select @.Startdate = Dateadd(dd,1,@.Startdate);

End

return;

End

Go

Select * from FillDate('1900-01-01','2007-12-31')