Complicate select statement (I think)

HI all,
I want to create a selectect statement with a computed colulm where the
computed column takes into account the result of the computed colum of the
prevuous row
psudo code: if the total-cap>total then cap else total-(previous computed)
eg table and rows
total cap
13000 5000
13000 10000
13000 100000
so my view or result would be
total cap computed
13000 5000 5000
13000 10000 8000
13000 100000 0
THis will be for a claims database where each row represents an loss risk
layer
so that row 1 would be the excess, and the computed column would shou
howmuch of the total claim is within excess.
The next row is the next layer stoppper which would show how much of the
claim falss within that layer, taking into acount the result of any layer
before it, ans so on
So each layer takes off a bit of the claims untill the claim total is
finnished. Basically apportioning the claim between the different layers
I dont have any real data or ddl, I'm still trying to make sure if this can
be achieved
You help is appreciated
Thanks
RObertRobert Bravery wrote:
> HI all,
> I want to create a selectect statement with a computed colulm where the
> computed column takes into account the result of the computed colum of the
> prevuous row
> psudo code: if the total-cap>total then cap else total-(previous computed)
> eg table and rows
> total cap
> 13000 5000
> 13000 10000
> 13000 100000
> so my view or result would be
> total cap computed
> 13000 5000 5000
> 13000 10000 8000
> 13000 100000 0
> THis will be for a claims database where each row represents an loss risk
> layer
> so that row 1 would be the excess, and the computed column would shou
> howmuch of the total claim is within excess.
> The next row is the next layer stoppper which would show how much of the
> claim falss within that layer, taking into acount the result of any layer
> before it, ans so on
> So each layer takes off a bit of the claims untill the claim total is
> finnished. Basically apportioning the claim between the different layers
> I dont have any real data or ddl, I'm still trying to make sure if this ca
n
> be achieved
> You help is appreciated
> Thanks
> RObert
What is/are the key(s) of your table and what determines which row is
the current, previous and next layer? In your example it seems that the
only way to fix the order is based on CAP. Is the CAP column really
unique? If not, then apparently there isn't a unique order. Please post
DDL (CREATE TABLE with keys and constraints) so that we don't have to
guess.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Robert
Why can't you do that on the client side?
create table #test
(
[id] int not null primary key,
total int,
cap int
)
insert into #test values (1,13000,5000)
insert into #test values (2,13000,10000)
insert into #test values (3,13000,100000)
SELECT *,CASE WHEN r>total THEN cap END
FROM
(
SELECT *,total-cap as r
FROM #test
) AS Der

> psudo code: if the total-cap>total then cap else total-(previous computed)
13000-5000 >1300
Don't uderstand your requirements
"Robert Bravery" <me@.u.com> wrote in message
news:uSIar6VLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> HI all,
> I want to create a selectect statement with a computed colulm where the
> computed column takes into account the result of the computed colum of the
> prevuous row
> psudo code: if the total-cap>total then cap else total-(previous computed)
> eg table and rows
> total cap
> 13000 5000
> 13000 10000
> 13000 100000
> so my view or result would be
> total cap computed
> 13000 5000 5000
> 13000 10000 8000
> 13000 100000 0
> THis will be for a claims database where each row represents an loss risk
> layer
> so that row 1 would be the excess, and the computed column would shou
> howmuch of the total claim is within excess.
> The next row is the next layer stoppper which would show how much of the
> claim falss within that layer, taking into acount the result of any layer
> before it, ans so on
> So each layer takes off a bit of the claims untill the claim total is
> finnished. Basically apportioning the claim between the different layers
> I dont have any real data or ddl, I'm still trying to make sure if this
> can
> be achieved
> You help is appreciated
> Thanks
> RObert
>
>|||>> I want to create a SELECT statement with a computed column where the com
puted column takes into account the result of the computed column of the pre
vious row <<
Somebody was aasleep in RDBMS 101! Let's get back to the basics of an
RDBMS. Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless. If you want an
ordering, then you need to havs a column that defines that ordering.
You must use an ORDER BY clause on a cursor -- the keys have nothing
whatsoever to do with the display in the front end.
Google up my articles on LIFO and FIFO in SQL at www.dbazine.com. I
think this is what you are after.

Complexx AutoIncrment colum

Hi,

I have a situation where I have to maintain auto incremental values based on values of other colum. Sample table is given below.

colA

colB

colC

1

1

1

1

1

2

1

1

3

1

1

4

1

2

1

1

2

2

1

2

3

2

1

1

2

1

2

2

1

3

Now in above table I want to autoincrement value of colC on the basis of Values of colA+colB. Is there any way I can set this fuctionalty directky on table schema.

Nilkkanth Desai

There is no way to create this kind of functionality on the table schema.

You could use a TRIGGER.

|||

You can use computed column for ColC.

Thanks

Naras.

|||

Hello Narasimhan Jayachandran ,

Thanks for your reply. I tried to findout samples on computed columns but I did not find any. Can u give me sample on this issue. or give me more details link if any on this topic. Your sample code will be more helpful for me to solve this issue.

Thank you,

Nilkanth Desai

|||

It is not good idea to use the computed columns here.. (in other words you can't achive the result using the computed columns, since your computed colum is non-deterministic).

The best approach may be on the trigger or you can use new feature OUTPUT.

dafafadfa

Using OUTPUT:

Create table ABCTable
(C1 int, C2 Int, C3 INT)

Go

Begin Tran;
Begin Try

Declare @.Inserted Table(C1 int, C2 int);

Insert Into ABCTable(c1,C2)
OUTPUT Inserted.C1,Inserted.C2 Into @.Inserted
values (1,1)

Update ABCTable
Set
C3=Isnull(NewC3,1)
From
(
Select
Max(C3)+ 1 NewC3,
A.C1,
A.C2
From
ABCTable A
Join @.Inserted B On A.C1= B.C1 and A.c2=B.C2
Group By
A.C1, A.C2
)as NewC3
Where
ABCTable.C1 = NewC3.C1
And ABCTable.C2 = NewC3.C2
And C3 is NULL

IF @.@.ERROR = 0
Commit Tran
Else
Rollback Tran

End Try

Begin Catch
Rollback
End Catch

|||

Hi Nilkanth,

Computed column :

Example : create table tbcc ( c1 int, c2 int, c3 as (c1+c2))

insert tbcc (c1,c2) values (1,2)

select * from tbcc

c1 c2 c3
-- -- --
1 2 3

More details, refer Books on line under Create table

--
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
) ..............

--

Thanks

Naras.

|||

Hi Narah,

Thanks for am alternative with sample code. It works.

Nilkanth

|||

Another question:

What is done if a row is deleted; are the rows supposed to auto-decrement?

|||

Hi,

It seems for update also it require one more trigger. Overall Triggers are costly in terms of resource utilisation. As this is my main trnsaction trble which handles 98% of traffic of the system I think triggers will be more heavy solution. Microsoft shall provide some mechenism in future release products as this kind of situation is mostlikely faced by most of the database developers. If we can find any schema based solution it will be light & easy to deploy.

Anyway bu this time this is the only solution I can use. If anyone finds more better solution then this please update this blog.

Nilkanth Desai

Complexx AutoIncrment colum

Hi,

I have a situation where I have to maintain auto incremental values based on values of other colum. Sample table is given below.

colA colB colC 1 1 1 1 1 2 1 1 3 1 1 4 1 2 1 1 2 2 1 2 3 2 1 1 2 1 2 2 1 3

Now in above table I want to autoincrement value of colC on the basis of Values of colA+colB. Is there any way I can set this fuctionalty directky on table schema.

Nilkkanth Desai

There is no way to create this kind of functionality on the table schema.

You could use a TRIGGER.

|||

You can use computed column for ColC.

Thanks

Naras.

|||

Hello Narasimhan Jayachandran ,

Thanks for your reply. I tried to findout samples on computed columns but I did not find any. Can u give me sample on this issue. or give me more details link if any on this topic. Your sample code will be more helpful for me to solve this issue.

Thank you,

Nilkanth Desai

|||

It is not good idea to use the computed columns here.. (in other words you can't achive the result using the computed columns, since your computed colum is non-deterministic).

The best approach may be on the trigger or you can use new feature OUTPUT.

dafafadfa

Using OUTPUT:

Create table ABCTable
(C1 int, C2 Int, C3 INT)

Go

Begin Tran;
Begin Try

Declare @.Inserted Table(C1 int, C2 int);

Insert Into ABCTable(c1,C2)
OUTPUT Inserted.C1,Inserted.C2 Into @.Inserted
values (1,1)

Update ABCTable
Set
C3=Isnull(NewC3,1)
From
(
Select
Max(C3)+ 1 NewC3,
A.C1,
A.C2
From
ABCTable A
Join @.Inserted B On A.C1= B.C1 and A.c2=B.C2
Group By
A.C1, A.C2
)as NewC3
Where
ABCTable.C1 = NewC3.C1
And ABCTable.C2 = NewC3.C2
And C3 is NULL

IF @.@.ERROR = 0
Commit Tran
Else
Rollback Tran

End Try

Begin Catch
Rollback
End Catch

|||

Hi Nilkanth,

Computed column :

Example : create table tbcc ( c1 int, c2 int, c3 as (c1+c2))

insert tbcc (c1,c2) values (1,2)

select * from tbcc

c1 c2 c3
-- -- --
1 2 3

More details, refer Books on line under Create table

--
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
) ..............

--

Thanks

Naras.

|||

Hi Narah,

Thanks for am alternative with sample code. It works.

Nilkanth

|||

Another question:

What is done if a row is deleted; are the rows supposed to auto-decrement?

|||

Hi,

It seems for update also it require one more trigger. Overall Triggers are costly in terms of resource utilisation. As this is my main trnsaction trble which handles 98% of traffic of the system I think triggers will be more heavy solution. Microsoft shall provide some mechenism in future release products as this kind of situation is mostlikely faced by most of the database developers. If we can find any schema based solution it will be light & easy to deploy.

Anyway bu this time this is the only solution I can use. If anyone finds more better solution then this please update this blog.

Nilkanth Desai