Working with a dataset ... or how to use set-based thinking to add missing rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Working with a dataset ... or how to use set-based thinking to add missing rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-05 : 15:08:12

Scott writes "I have created a dataset in asp.net which is nothing more than a table of values that looks like:

A is a calculated field and B is the count where equal to A. What I need to do is to add rows for A equal to 4 and 7 with B set to 0. I am currently accomplishing this task by sequentially looping through the dataset in asp.net and adding rows as I go. Once done, I add another column which is the backwards sum of B. The final dataset looks something like this:

Is it possible to accomplish this in a set-based manner?

I'm using SQL Server 2000 with SP3 on a Windows XP SP1 machine."

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-05 : 16:02:37

Er..

what is the expression that gives you your calculated column A? can I see the source data that A is calculated from please.

What is a backward sum? count of rows not equal to A ?

If you can show me example of the data from the beggining and confirm what a backward sum is I might be able to help.

ps. and maybe explain a bit about what your trying to accomplish, i.e. what part of the real world are we modeling here?

;-]... Quack Waddle

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-05 : 16:19:20

[code]create table test ( a INT, b INT)

insert into test
select 1, 500 union all
select 2, 400 union all
select 3, 200 union all
select 5, 100 union all
select 6, 80 union all
select 8, 40 union all
select 9, 20

select * from test

insert into test
select 4,0 union all
select 7,0

select *,
(select isnull(sum(t2.b),0) from test t2 where t2.a >= t1.a) c
from test t1
order by a

drop table test[/code]

scott@creditre.net
Starting Member

3 Posts

Posted - 2004-05-05 : 16:38:39

Column A is the sum of all benefits paid to a claimant divided by the expected benefit. This is a proxy for the duration of claim. B is the number of claimants with a ceiling(duration) equal to the duration B. C is the backwards sum of B or the number still on claim at duration B.

Backwards sum is like a reverse cumulative sum ... start adding from the bottom of the list instead of the top. Ehorn's solution "select *, (select isnull(sum(t2.b),0) from test t2 where t2.a >= 1.a) c from test t1 order by a" is similar to what I came up with myself.

How I added in the missing rows:

select @maxDuration = max(Duration) from #Temp
while @duration < @maxDuration
begin
set @duration = @duration + 1
select top 1 @checkduration = duration from #temp
delete from #temp where duration = @duration
if @checkduration <> @duration
begin
insert into #temp2 (Duration, Number, BenefitsReceived) values(@duration,0,0)
end
continue
end

Is this an OK approach?

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-05 : 17:15:03

if we knew A would be 1 - 100 say, you could create a table called pivot with single int column i and fill it with rows 1 - 100. (there's a sneeaky way of doing this I'll look it up and post it tomorrow)

then left join on it, and use a case statement or coalesce to return A when not null or i when null

so:

insert into #temp2 (Duration, Number, BenefitsReceived)
select coalesce(t.a,p.i),coalesce(t.b,0),coalesce(t.c,0)
from pivot p left join #temp t on p.i=t.a

;-]... Quack Waddle

scott@creditre.net
Starting Member

3 Posts

Posted - 2004-05-05 : 17:17:17

Unfortunately, column A is totally variable; it could be any number.

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-05 : 17:22:56

need to look up my create pivot script, might be able to select min and max of A into two variables and fill up pivot with the numbers, then run the left join statement.

;-]... Quack Waddle

scott@creditre.net
Starting Member

3 Posts

Posted - 2004-05-05 : 17:31:20

The min of A will always be 0. However, A will also need to contain 0.125, 0.25, and 0.5.

So I take it you don't like my temp table approach?

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-05 : 17:34:07

CREATE TABLE Pivot (

i INT,

PRIMARY KEY(i)

)

CREATE TABLE Foo(

i CHAR(1)

)

INSERT INTO Foo VALUES('0')

INSERT INTO Foo VALUES('1')

INSERT INTO Foo VALUES('2')

INSERT INTO Foo VALUES('3')

INSERT INTO Foo VALUES('4')

INSERT INTO Foo VALUES('5')

INSERT INTO Foo VALUES('6')

INSERT INTO Foo VALUES('7')

INSERT INTO Foo VALUES('8')

INSERT INTO Foo VALUES('9')

INSERT INTO Pivot

SELECT f1.i+f2.i+f3.i

FROM Foo f1, Foo F2, Foo f3

this gives 0 - 1000, add Foo f4 you get 10,000

then:
declare @max int, @min int

select @max = max(A) from #temp
select @min = min(A) from #temp

insert into #temp2 (Duration, Number, BenefitsReceived)
select coalesce(t.a,p.i),coalesce(t.b,0),coalesce(t.c,0)
from pivot p left join #temp t on p.i=t.a
where p.i between @min and @max

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-05 : 17:39:17

I think the temp table loop is a fine approach, I have used while loops too for various solutions.

I think we have exhaustively proved a loop is the only candidate here.

If a set based way comes to me I'll let you know.

;-]... Quack Waddle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources