Row Correlated calculation Query

Hi,
I need help in writing a sql, where a calculated column depends on previous'
s row column, for example,
Table: test
Time Packet Seen Seq_num
--
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 Packet Seen Seq_num 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
MonisDECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5), PacketSeen
INT, SeqNum INT)
INSERT @.table(Timecol, PacketSeen, SeqNum)
SELECT '10:00',20,25 UNION ALL
SELECT '10:01',15,40 UNION ALL
SELECT '10:02',17,57 UNION ALL
SELECT '10:03',10,60 UNION ALL
SELECT '10:04',12,72
SELECT Timecol, PacketSeen, SeqNum FROM @.table
SELECT
t1.Timecol,
t1.PacketSeen,
t1.SeqNum,
t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
FROM
@.table t1
LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"mmonis" wrote:

> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 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 Packet Seen Seq_num 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
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Hi
Yes , it is possible by using T-SQL , however much more easier doing such
reports on the client side
"mmonis" <mmonis.28cehp@.mail.codecomments.com> wrote in message
news:mmonis.28cehp@.mail.codecomments.com...
> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 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 Packet Seen Seq_num 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
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||This looks like it depends on identity being sequential and matching the
order TimeCol. Failed inserts will cause gaps in identity, and we may not
be able to depend on TimeCol always being inserted sequentially either. In
a production environment, I think this will break more often than not. At a
minimum, I think we need a correlated subquery here, no?
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:ED9AB916-DD8B-423D-92D2-0AD19932AE91@.microsoft.com...
> DECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5),
PacketSeen
> INT, SeqNum INT)
> INSERT @.table(Timecol, PacketSeen, SeqNum)
> SELECT '10:00',20,25 UNION ALL
> SELECT '10:01',15,40 UNION ALL
> SELECT '10:02',17,57 UNION ALL
> SELECT '10:03',10,60 UNION ALL
> SELECT '10:04',12,72
> SELECT Timecol, PacketSeen, SeqNum FROM @.table
> SELECT
> t1.Timecol,
> t1.PacketSeen,
> t1.SeqNum,
> t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
> FROM
> @.table t1
> LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
>
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "mmonis" wrote:
>