Previous row calculations using SQL

Hi,
I need help in writing a SQL, where in a calculated column depends on previo
us's row column, for example,
Table: test
Time Pkts Seq_no
--
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72
Query Result: The output of the query should be
Time Pkts Seq_no drops
---
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0
Is this kind of calculation possible using SQL. Any help in this regards wil
l be highly appreciated.
Thanks
-MIf is indeed possible to do this in SQL, but you cannot do this with a
calculated column. The rules of calculated columns are that the calculation
is restricted to the current row only.
To perform this calculated you will need to create a column to store the
data. Then use an "instead of" trigger to perform the calculation, and save
the result into the correct row.
There are a couple of warnings with this method.
1. Perform as few inserts and updates as possible. Inserting 1000 rows, one
at a time will be extremely expensive. It is better to perform a single
insert of 1000 rows instead - use temporary tables or another buffering
technique to batch process.
2. You need to make sure that all the nuances of the code are taken into
account. It is vital that the correct calulation is always performed. This
is not trivial, but is possible. I know because I've done it before - but
unfortunatly cannot share the code as it's not my property.
Regards
Colin Dawson
www.cjdawson.com
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Get a copy of SQL-2005 and look at SUM() OVER() function that is new to
Standard SQL. It can get complicated but it is used for running
totals, etc.|||Try this, note that this relies on Time being unique.
If Time isn't unique, you'll need some additional
rules to determine the "previous" row.
SELECT t1.Time,
t1.Pkts,
t1.Seq_no,
t2.Seq_no + t1.Pkts - t1.Seq_no AS drops
FROM test t1
LEFT OUTER JOIN test t2 ON t2.Time<t1.Time
AND t2.Time=(SELECT MAX(t3.Time)
FROM @.test t3
WHERE t3.Time<t1.Time)
ORDER BY t1.Time|||Have you considered doing this at select time instead of storing the value?
You certainly need to take performance into consideration, but it could be
done this way. Note that like the solution posted by MarkC600, this assumes
that time is unique.
Create view Test_vw as
Select a.time
, a.pkts
, (
select sum(b.pkts)
from test b
where b.time = a.time
) as seq_no
from test a
OR (see disclaimer below)
Create view Test_vw as
Select a.time
, a.pkts
, sum(a.pkts) over(order by a.time)
from test a
group by a.time
, a.pkts
Note that the second query syntax is likely wrong, since I don't have a
system to test it on, and put it together based on what I could quickly
gather from the web. I think you can use the functionality in SQL Server
2005, but not on 2000. You can see how it is simpler than the first query,
and the benefit (from a code maintenance perspective) you can get from
learning it if you are on 2005.
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Sorry... I missed the entire second half of your post somehow... I thought
you were asking how to get Seq_no.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23pHFGXBgGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Have you considered doing this at select time instead of storing the
value?
> You certainly need to take performance into consideration, but it could be
> done this way. Note that like the solution posted by MarkC600, this
assumes
> that time is unique.
> Create view Test_vw as
> Select a.time
> , a.pkts
> , (
> select sum(b.pkts)
> from test b
> where b.time = a.time
> ) as seq_no
> from test a
> OR (see disclaimer below)
> Create view Test_vw as
> Select a.time
> , a.pkts
> , sum(a.pkts) over(order by a.time)
> from test a
> group by a.time
> , a.pkts
> Note that the second query syntax is likely wrong, since I don't have a
> system to test it on, and put it together based on what I could quickly
> gather from the web. I think you can use the functionality in SQL Server
> 2005, but not on 2000. You can see how it is simpler than the first
query,
> and the benefit (from a code maintenance perspective) you can get from
> learning it if you are on 2005.
> "mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
> news:mmonis.28cv5s@.mail.codecomments.com...
>