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 a view or sp

According to a developer that worked on my db, there is a view :
xvw_CheckVoucherReport
I cant see it is there any way i can find out it exsist and if so what database on my server it is in?Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) would use SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_5qup.asp) to show it to you. You could also use SQL Query Analyzer (http://msdn.microsoft.com/library/en-us/qryanlzr/qryanlzr_1zqq.asp) to check the INFORMATION_SCHEMA.VIEWS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp) for it.

-PatP|||Select * from Sysobjects should generate all the objects in your database.|||Originally posted by sqlserver2k
Select * from Sysobjects should generate all the objects in your database. Good point, but information_schema views are a lot friendlier and are portable. Why start bad habits (using system tables) when good ones are easier?

-PatP|||I second Pat's proposition ... for not to use SYSTEM TABLES when system supplied SPs and ISVs are provided.

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
Sri
This probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --
|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find my Syntax Error!! (Because i cant see it)

Im getting an syntax error in the insert into statement is what my error message says, but i cant see it. So if you could take a look and maybe find the problem it would greatly help.

---

With addDonorReciepts
.CommandText = "insert into FoodDonations (Company, Phone, Contact Name, Street Address, Suit, City, General Location, Donor Date, Donor Time, Bakery Donated, Meat Donated, Fruit Donated, Dairy Donated, Vegetables Donated, Prepared Donated, Beverages Donated, Non-Perish Donated, Non-Food Donated, Calc1 Total, Total Donations, Receipt Number) values ('" & oneRowDon.Item(1) & "', " & oneRowDon.Item(2) & ", '" & oneRowDon.Item(3) & "', '" & oneRowDon.Item(4) & "', " & oneRowDon.Item(5) & ", '" & oneRowDon.Item(6) & "', '" & oneRowDon.Item(7) & "', " & oneRowDon.Item(8) & ", " & oneRowDon.Item(8) & ", " & oneRowDon.Item(11) & ", " & oneRowDon.Item(13) & ", " & oneRowDon.Item(14) & ", " & oneRowDon.Item(12) & ", " & oneRowDon.Item(15) & ", " & oneRowDon.Item(16) & ", " & oneRowDon.Item(19) & ", " & oneRowDon.Item(20) & ", " & oneRowDon.Item(21) & ", " & oneRowDon.Item(22) & ", " & oneRowDon.Item(22) & ", " & rNumber & ")"
.Connection = FHDB
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With

---

Item(1) - String
Item(2) - Int
Item(3) - String
Item(4) - String
Item(5) - int
Item(6) - string
Item(7) - string
Item(8) - Dunno its a DateTime thing
Item(11) - int
Item(13) - int
Item(14) - int
Item(12) - int
Item(15) - int
Item(16) - int
Item(19) - int
Item(20) - int
Item(21) - int
Item(22) - int
rNumber - int

You need to delimit all column names that have spaces. Change Contact Name to [Contact Name], etc.

A good way to debug a SQL statement that you generate is to look at the commandtext string either from debugger or by printing it out and inspecting from SQL Editor in SQL Management Studio where you get basic color coding help.

Hope this helps.

|||Aha... Thanks :)
also some of those ints were not really ints so that caused a few problems