Thursday, March 8, 2012

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.

No comments:

Post a Comment