RE: [SQL] Mail for error in tuples - Mailing list pgsql-sql

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@chemquick.com]
> Sent:    Friday, June 11, 1999 2:42 PM
> To:    Jackson, DeJuan
> Cc:    pgsql-sql@postgreSQL.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@chemquick.com]
> > > Sent: Friday, June 11, 1999 1:45 PM
> > > To:   pgsql-sql@postgreSQL.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 >>