May 13, 2008 at 2:46 pm
I have a table setup as such labeled - tbl_items:
seq account amount disp
---- -------- ------- ----
10 12345 5000.00 +
20 23451 500.00 -
30 34512 1000.00 -
40 12453 2000.00 -
50 44423 1000.00 B
60 22456 500.00 -
110 12345 4356.55 +
120 68901 4000.00 -
130 68904 356.55 -
I'm trying to return consecutive rows. So for example:
Select * from tbl_items where account = 12345[/]
then return all records below that record until the next '+' in column disp.
so the result set would be:
seq account amount disp
---- -------- ------- ----
10 12345 5000.00 +
20 23451 500.00 -
30 34512 1000.00 -
40 12453 2000.00 -
50 44423 1000.00 B
60 22456 500.00 -
I've tried a WHILE loop, nested selects and nothing... I'm now to the point of being confused as ever... Any assistance would surely keep me out of the nut house..
May 13, 2008 at 2:55 pm
A recursive common table expression (recursive CTE) can do that pretty easily. Here's a sample based on your sample data:
create table #T (
Seq int primary key,
Account int)
insert into #T (Seq, Account)
select 10,12345 union all
select 20,23451 union all
select 30,34512 union all
select 40,12453 union all
select 50,44423 union all
select 60,22456 union all
select 110,12345 union all
select 120,68901 union all
select 130,68904
declare @Account_in int
select @account_in = 12345
;with CTE (Seq) as
(select min(Seq)
from #t
where account = @account_in
union all
select #t.seq
from #t
inner join cte
on #t.seq = cte.seq + 10)
select *
from cte
Of course, you can name the CTE something more meaningful, and this example assumes your interval of 10 is always true. I'm also assuming you want to start with the first Seq that has the account you're looking for. You can change that by getting rid of the min() in the anchor member of the CTE.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 3:21 pm
Okay that opened my eyes but the next '+' is not necessarily the same account number as the first first '+'. So the table could look like this:
seq account amount disp
---- -------- ------- ----
10 12345 5000.00 +
20 23451 500.00 -
30 34512 1000.00 -
40 12453 2000.00 -
50 44423 1000.00 B
60 22456 500.00 -
110 99889 4356.55 +
120 68901 4000.00 -
130 68904 356.55 -
I also failed to mention that I'm at the mercy of a vendors db design so I won't be able to alter the structure. Also, SQL2000 is the version.
Thanks so much.
May 14, 2008 at 9:59 am
Won't matter if the next desired row is the same account number.
Will matter that it's SQL 2000. (You posted this in the 2005 forum, so I assumed it was 2005.) You'll either need to use a While Loop or a Cursor to get this in 2000, so far as I know.
Edit (5/16): Actually, I misread part of the spec. A loop definitely isn't needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2008 at 10:07 am
rbateman (5/13/2008)
Okay that opened my eyes but the next '+' is not necessarily the same account number as the first first '+'. So the table could look like this:seq account amount disp
---- -------- ------- ----
10 12345 5000.00 +
20 23451 500.00 -
30 34512 1000.00 -
40 12453 2000.00 -
50 44423 1000.00 B
60 22456 500.00 -
110 99889 4356.55 +
120 68901 4000.00 -
130 68904 356.55 -
I also failed to mention that I'm at the mercy of a vendors db design so I won't be able to alter the structure. Also, SQL2000 is the version.
Thanks so much.
you can use the "running total" logic from this article to get you there....
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
Yes that involves dumping things into a temporary table with a new column, and then pulling results from it, but it should beat the pants off of most cursor or WHILE methods.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 11:27 am
Thanks GSquared-- Can a moderator move the thread?
May 16, 2008 at 7:20 am
Matt, he's not talking about getting a running total. He's talking about "pull up the next row if the ID for that row is a set interval higher than the ID for the prior row, and keep doing so till you run into one that isn't that interval."
I don't see a way for the running total query to do that. Maybe I'm missing something.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2008 at 9:09 am
I may have misunderstood something, but what about this?
create table #T (
Seq int primary key,
Account int,
Disp char)
--drop table #t
insert into #T (Seq, Account, Disp)
select 10,12345,'+' union all
select 20,23451,'' union all
select 30,34512,'' union all
select 40,12453,'b' union all
select 50,44423,'' union all
select 60,22456,'+' union all
select 110,12345,'' union all
select 120,68901,'' union all
select 130,68904,''
declare @start int
select @start=Seq from #T where Account = 12345 and Disp = '+'
declare @end int
select top 1 @end=SeQ from #T where SeQ > @start and Disp = '+' --order by SeQ desc
--print convert(char, @start) + ' ' + convert(char, @end)
select * from #T where Seq >= @start AND Seq <= @end
May 16, 2008 at 9:22 am
I'm in the presence of Jedi masters.... that did it! I have much melon candy to consume... thank you all for your contributions..
May 16, 2008 at 9:26 am
if that did it, then hopeflly those Seq's keep incrementing. Also, those are 3 distinct selection statements going on, not the best.
May 16, 2008 at 9:30 am
The only thing I changed was (BOLD below) the <= to a <. So that it would not include the next '+' since that starts another new set.
create table #T (
Seq int primary key,
Account int,
Disp char)
--drop table #t
insert into #T (Seq, Account, Disp)
select 10,12345,'+' union all
select 20,23451,'' union all
select 30,34512,'' union all
select 40,12453,'b' union all
select 50,44423,'' union all
select 60,22456,'+' union all
select 110,12345,'' union all
select 120,68901,'' union all
select 130,68904,''
declare @start int
select @start=Seq from #T where Account = 12345 and Disp = '+'
declare @end int
select top 1 @end=SeQ from #T where SeQ > @start and Disp = '+' --order by SeQ desc
--print convert(char, @start) + ' ' + convert(char, @end)
select * from #T where Seq >= @start AND Seq < @end
drop table #t
May 16, 2008 at 9:34 am
Cool. Glad you got it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply