RE: [SQL] Mail for error in tuples

test=> select * from test;
d |t
----------------------------+--
Mon Mar 29 21:48:46 1999 CST|a
Mon Mar 29 21:49:02 1999 CST|ab
Mon Mar 29 21:49:18 1999 CST|c
Mon Mar 29 21:49:20 1999 CST|d
Mon Mar 29 21:49:22 1999 CST|e
Mon Mar 29 21:49:24 1999 CST|f
Mon Mar 29 21:49:26 1999 CST|g
Mon Mar 29 21:49:27 1999 CST|
Mon Mar 29 21:49:30 1999 CST|h
Mon Mar 29 21:51:13 1999 CST|i
Mon Mar 29 21:51:15 1999 CST|j
Mon Mar 29 21:51:16 1999 CST|k
Mon Mar 29 21:51:18 1999 CST|l
Mon Mar 29 21:51:19 1999 CST|m
Mon Mar 29 21:51:21 1999 CST|n
Mon Mar 29 21:51:23 1999 CST|o
Mon Mar 29 21:51:24 1999 CST|p
Mon Mar 29 21:51:27 1999 CST|q
Mon Mar 29 21:51:29 1999 CST|r
Mon Mar 29 21:51:31 1999 CST|s
Mon Mar 29 21:51:33 1999 CST|t
Mon Mar 29 21:51:34 1999 CST|u
Mon Mar 29 21:51:36 1999 CST|v
Mon Mar 29 21:51:39 1999 CST|w
Mon Mar 29 21:51:41 1999 CST|x
Mon Mar 29 21:51:42 1999 CST|y
Mon Mar 29 21:51:44 1999 CST|z
Mon Mar 29 21:51:46 1999 CST|aa
Mon Mar 29 21:51:49 1999 CST|ab
Mon Mar 29 21:51:51 1999 CST|ac
Mon Mar 29 21:51:55 1999 CST|ad
(31 rows)

test=> select min(test.d) as d, NULL as diff from test UNION ALL select
t1.d, t1.d-max(t2.d) as diff from test t1, test t2 where t2.d<t1.d group by
t1.d;
d |diff
----------------------------+---------------
Mon Mar 29 21:48:46 1999 CST|
Mon Mar 29 21:49:02 1999 CST|@ 16 secs
Mon Mar 29 21:49:18 1999 CST|@ 16 secs
Mon Mar 29 21:49:20 1999 CST|@ 2 secs
Mon Mar 29 21:49:22 1999 CST|@ 2 secs
Mon Mar 29 21:49:24 1999 CST|@ 2 secs
Mon Mar 29 21:49:26 1999 CST|@ 2 secs
Mon Mar 29 21:49:27 1999 CST|@ 1 sec
Mon Mar 29 21:49:30 1999 CST|@ 3 secs
Mon Mar 29 21:51:13 1999 CST|@ 1 min 43 secs
Mon Mar 29 21:51:15 1999 CST|@ 2 secs
Mon Mar 29 21:51:16 1999 CST|@ 1 sec
Mon Mar 29 21:51:18 1999 CST|@ 2 secs
Mon Mar 29 21:51:19 1999 CST|@ 1 sec
Mon Mar 29 21:51:21 1999 CST|@ 2 secs
Mon Mar 29 21:51:23 1999 CST|@ 2 secs
Mon Mar 29 21:51:24 1999 CST|@ 1 sec
Mon Mar 29 21:51:27 1999 CST|@ 3 secs
Mon Mar 29 21:51:29 1999 CST|@ 2 secs
Mon Mar 29 21:51:31 1999 CST|@ 2 secs
Mon Mar 29 21:51:33 1999 CST|@ 2 secs
Mon Mar 29 21:51:34 1999 CST|@ 1 sec
Mon Mar 29 21:51:36 1999 CST|@ 2 secs
Mon Mar 29 21:51:39 1999 CST|@ 3 secs
Mon Mar 29 21:51:41 1999 CST|@ 2 secs
Mon Mar 29 21:51:42 1999 CST|@ 1 sec
Mon Mar 29 21:51:44 1999 CST|@ 2 secs
Mon Mar 29 21:51:46 1999 CST|@ 2 secs
Mon Mar 29 21:51:49 1999 CST|@ 3 secs
Mon Mar 29 21:51:51 1999 CST|@ 2 secs
Mon Mar 29 21:51:55 1999 CST|@ 4 secs
(31 rows)

I don't think that you can accomplish this in an update.
but I hope this helps,
-DEJ

> -----Original Message-----
> From: Vikrant Rathore [SMTP:vikrant(at)chemquick(dot)com]
> Sent: Friday, June 11, 1999 2:42 PM
> To: Jackson, DeJuan
> Cc: pgsql-sql(at)postgreSQL(dot)org
> Subject: Re: [SQL] Mail for error in tuples
>
> Dear Jackson,
>
> The tuple in radius tables are there in the attached file.
> Now the following things are to be done:
> 1. Select a tuple and subsequent tuple and find the timespan between
> the
> datetime of first and the subsequent tuple. if the timespan is greater
> than the
> duration given in the subsequent tuple then update the subsequent tuple
> with
> duration set to the timespan between the first selected tuple and
> subsequent
> tuple:
> Consider the following tuples
> first tuple:
> uname=penguin
> logdate=06/12/1999
> logtime=11:00:00AM
> duration=1 Hrs
> rest can be left as such
> subsequent tuple
> uname=penguin
> logdate=06/12/1999
> logtime=12:00:00 PM
> duration=2 Hrs
> Then in the subsequent tuple the duration is wrong this data is taken from
> a
> external source now want to validate it by applying the above condition so
> the
> duration in the second tuple should be set to
> uname=penquin
> logdate=06/12/1999
> logtime=12:00:00 PM
> duration=1 Hrs.
> Now can you tell me how can i achieve this in postgresql. I am new to
> postgresql.
> Thanks a lot for your prompt reply.
>
> Regards,
> Vicky
>
>
>
> "Jackson, DeJuan" wrote:
>
> > It might be best if you gave us an actual example or two in stead of
> trying
> > to describe the situation (I couldn't follow it, I have no idea what a
> > radius log is either). So, show us what you're after.
> > -DEJ
> >
> > > -----Original Message-----
> > > From: Vikrant Rathore [SMTP:vikrant(at)chemquick(dot)com]
> > > Sent: Friday, June 11, 1999 1:45 PM
> > > To: pgsql-sql(at)postgreSQL(dot)org
> > > Subject: [SQL] Mail for error in tuples
> > >
> > > Dear friend,
> > >
> > > I have a table named radius with structure
> > >
> > > Table = radius
> > >
> +----------------------------------+----------------------------------+---
> > > ----+
> > >
> > > | Field | Type
> |
> > > Length|
> > >
> +----------------------------------+----------------------------------+---
> > > ----+
> > >
> > > | uname | char()
> > > | 256 |
> > > | logdate | date
> > > | 4 |
> > > | logtime | time
> > > | 8 |
> > > | duration | timespan
> > > | 12 |
> > > | status | char()
> > > | 20 |
> > > | nasadd | char()
> > > | 20 |
> > > | port | int4
> > > | 4 |
> > > | bytesin | int4
> > > | 4 |
> > > | bytesout | int4
> > > | 4 |
> > > | packin | int4
> > > | 4 |
> > > | packout | int4
> > > | 4 |
> > > | misc | int4
> > > | 4 |
> > >
> +----------------------------------+----------------------------------+---
> > > ----+
> > >
> > > I keep my radius log in this table.
> > >
> > > Now in this logdate and logtime are the last logout date and time for
> > > the particular user.
> > >
> > > But sometimes the logdate and logtime and duration are wrong so
> inorder
> > > to correct i have to do following can anyone help me how to do it.
> > >
> > > I want to select the tuples of particular user and test the first
> > > logdate and logtime with the next tuples logdate and logtime and the
> > > duration in the second tuple should not be greater than the difference
> > > of this two tuples if its greater than the difference then it needs to
> > > be set to the difference of the two logdate and logtime. I have to
> test
> > > it till the last tuple.
> > > So can anyone suggest me query for this job.
> > >
> > > Thanks in advance.
> > >
> > > Regards,
> > > Vicky
> > >
> > > << File: tuples.out >>