Re: [SQL] Mail for error in tuples - Mailing list pgsql-sql
| From | Vikrant Rathore |
|---|---|
| Subject | Re: [SQL] Mail for error in tuples |
| Date | |
| Msg-id | 3761666B.4D11AB35@chemquick.com Whole thread Raw |
| In response to | RE: [SQL] Mail for error in tuples ("Jackson, DeJuan" <djackson@cpsgroup.com>) |
| List | pgsql-sql |
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
> >
> >
uname
| logdate|logtime |duration | status|
nasadd|port|bytesin|bytesout|packin|packout|misc
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+-------------------------+--------------------+--------------------+----+-------+--------+------+-------+----
penguin
|02-15-1999|00:30:59|@ 18 mins 30 secs | |205.252.137.143 | 74| 0|
0| 0| 0| 0
penguin
|02-15-1999|03:37:27|@ 2 hours 51 mins 41 secs| |205.252.137.143 | 87| 0|
0| 0| 0| 0
penguin
|02-15-1999|15:30:55|@ 1 hour 2 mins 9 secs | |205.252.137.143 | 19| 0|
0| 0| 0| 0
penguin
|02-15-1999|15:32:33|@ 13 secs | |205.252.137.143 | 76| 0|
0| 0| 0| 0
penguin
|02-16-1999|05:45:41|@ 11 mins 19 secs | |205.252.137.143 | 71| 0|
0| 0| 0| 0
penguin
|02-16-1999|05:48:53|@ 2 mins 9 secs | |205.252.137.143 | 28| 0|
0| 0| 0| 0
penguin
|02-16-1999|05:50:46|@ 22 secs | |205.252.137.143 | 97| 0|
0| 0| 0| 0
penguin
|02-16-1999|06:02:22|@ 6 mins 37 secs | |205.252.137.143 | 79| 0|
0| 0| 0| 0
penguin
|02-16-1999|17:55:53|@ 2 hours 6 mins 57 secs | |205.252.137.143 | 42| 0|
0| 0| 0| 0
penguin
|02-16-1999|18:28:50|@ 32 mins 13 secs | |205.252.137.143 | 3| 0|
0| 0| 0| 0
penguin
|02-16-1999|18:35:46|@ 1 min 26 secs | |205.252.137.143 | 90| 0|
0| 0| 0| 0
penguin
|02-17-1999|03:16:22|@ 25 mins 3 secs | |205.252.137.19 | 32| 148941|
700463| 2661| 2321| 0
penguin
|02-17-1999|12:12:53|@ 1 min 25 secs | |205.252.137.143 | 50| 0|
0| 0| 0| 0
penguin
|02-17-1999|23:29:51|@ 9 mins 13 secs | |205.252.137.143 | 5| 0|
0| 0| 0| 0
penguin
|02-18-1999|14:31:40|@ 15 mins 5 secs | |205.252.137.143 | 59| 0|
0| 0| 0| 0
penguin
|02-18-1999|15:02:52|@ 26 mins 56 secs | |205.252.137.143 | 41| 0|
0| 0| 0| 0
penguin
|02-18-1999|19:54:40|@ 2 hours 44 mins 42 secs| |205.252.137.143 | 36| 0|
0| 0| 0| 0
penguin
|02-19-1999|03:21:53|@ 6 hours 14 mins 33 secs| |205.252.137.143 | 68| 0|
0| 0| 0| 0
avengeri
|02-15-1999|00:48:12|@ 4 mins 5 secs | |205.252.137.143 | 73| 0|
0| 0| 0| 0
avengeri
|02-15-1999|14:36:15|@ 8 mins 11 secs | |205.252.137.143 | 47| 0|
0| 0| 0| 0
avengeri
|02-15-1999|17:30:04|@ 1 hour 44 mins 42 secs | |205.252.137.143 | 9| 0|
0| 0| 0| 0
avengeri
|02-15-1999|18:05:06|@ 31 mins 45 secs | |205.252.137.143 | 59| 0|
0| 0| 0| 0
avengeri
|02-15-1999|19:55:35|@ 54 mins 8 secs | |205.252.137.143 | 33| 0|
0| 0| 0| 0
avengeri
|02-17-1999|04:15:20|@ 1 min 16 secs | |205.252.137.143 | 27| 0|
0| 0| 0| 0
avengeri
|02-18-1999|15:44:03|@ 6 mins 57 secs | |205.252.137.143 | 81| 0|
0| 0| 0| 0
avengeri
|02-18-1999|18:43:40|@ 58 secs | |205.252.137.143 | 68| 0|
0| 0| 0| 0
avengeri
|02-18-1999|19:58:54|@ 2 mins 30 secs | |205.252.137.143 | 8| 0|
0| 0| 0| 0
avengeri
|02-18-1999|21:57:38|@ 1 min | |205.252.137.143 | 63| 0|
0| 0| 0| 0
avengeri
|02-19-1999|05:19:37|@ 45 mins 26 secs | |205.252.137.143 | 29| 0|
0| 0| 0| 0
hochengs
|02-15-1999|11:15:30|@ 14 mins 31 secs | |205.252.137.143 | 80| 0|
0| 0| 0| 0
hochengs
|02-15-1999|13:43:33|@ 27 mins 32 secs | |205.252.137.143 | 21| 0|
0| 0| 0| 0
hochengs
|02-15-1999|17:23:00|@ 26 mins 4 secs | |205.252.137.143 | 4| 0|
0| 0| 0| 0
hochengs
|02-15-1999|20:08:23|@ 2 hours 1 min 22 secs | |205.252.137.143 | 37| 0|
0| 0| 0| 0
hochengs
|02-16-1999|00:45:04|@ 21 mins 59 secs | |205.252.137.143 | 20| 0|
0| 0| 0| 0
hochengs
|02-16-1999|17:02:10|@ 1 hour 6 mins 55 secs | |205.252.137.143 | 54| 0|
0| 0| 0| 0
hochengs
|02-16-1999|21:15:08|@ 5 mins 36 secs | |205.252.137.143 | 40| 0|
0| 0| 0| 0
hochengs
|02-16-1999|23:54:36|@ 47 mins 3 secs | |205.252.137.143 | 44| 0|
0| 0| 0| 0
hochengs
|02-17-1999|15:48:05|@ 10 mins 8 secs | |205.252.137.143 | 51| 0|
0| 0| 0| 0
hochengs
|02-18-1999|00:59:35|@ 1 hour 49 mins 17 secs | |205.252.137.143 | 75| 0|
0| 0| 0| 0
hochengs
|02-18-1999|16:55:18|@ 6 mins 7 secs | |205.252.137.143 | 48| 0|
0| 0| 0| 0
hochengs
|02-19-1999|01:35:43|@ 20 mins 15 secs | |205.252.137.143 | 95| 0|
0| 0| 0| 0
scheng
|02-15-1999|11:15:49|@ 14 mins 42 secs | |205.252.137.143 | 24| 0|
0| 0| 0| 0
scheng
|02-15-1999|11:30:56|@ 10 mins 1 sec | |205.252.137.143 | 60| 0|
0| 0| 0| 0
scheng
|02-16-1999|00:11:14|@ 6 mins 37 secs | |205.252.137.143 | 16| 0|
0| 0| 0| 0
scheng
|02-17-1999|10:43:34|@ 21 mins 30 secs | |205.252.137.143 | 33| 0|
0| 0| 0| 0
scheng
|02-17-1999|11:06:28|@ 22 mins 16 secs | |205.252.137.143 | 39| 0|
0| 0| 0| 0
scheng
|02-18-1999|11:50:46|@ 23 mins 31 secs | |205.252.137.143 | 50| 0|
0| 0| 0| 0
davepeng
|02-15-1999|11:54:09|@ 1 hour 19 mins 58 secs | |205.252.137.19 | 8| 73809|
489421| 1310| 1340| 0
davepeng
|02-15-1999|14:28:08|@ 32 mins 38 secs | |205.252.137.19 | 41| 25383|
191848| 369| 392| 0
davepeng
|02-15-1999|14:48:11|@ 19 mins 1 sec | |205.252.137.19 | 20| 33839|
33345| 2187| 2195| 0
davepeng
|02-15-1999|16:15:02|@ 21 mins 40 secs | |205.252.137.19 | 21| 34063|
358225| 586| 563| 0
davepeng
|02-15-1999|18:12:50|@ 15 mins 41 secs | |205.252.137.19 | 44| 14812|
170407| 348| 325| 0
davepeng
|02-15-1999|21:45:40|@ 6 mins 14 secs | |205.252.137.19 | 13| 28926|
190626| 375| 367| 0
davepeng
|02-16-1999|00:09:40|@ 35 mins 13 secs | |205.252.137.19 | 9| 160023|
1809136| 3094| 2775| 0
davepeng
|02-16-1999|14:00:43|@ 38 mins 51 secs | |205.252.137.19 | 30| 64090|
446148| 780| 779| 0
davepeng
|02-16-1999|15:16:33|@ 1 min 47 secs | |205.252.137.19 | 20| 22753|
138629| 309| 312| 0
davepeng
|02-17-1999|14:20:27|@ 1 min 36 secs | |205.252.137.19 | 33| 17482|
142900| 258| 279| 0
davepeng
|02-18-1999|18:39:07|@ 1 hour 32 mins 53 secs | |205.252.137.19 | 39| 138678|
2360797| 2919| 2630| 0
davepeng
|02-18-1999|21:38:13|@ 35 secs | |205.252.137.19 | 25| 1083|
9642| 25| 34| 0
davepeng
|02-18-1999|21:42:16|@ 3 mins 10 secs | |205.252.137.19 | 23| 16341|
82192| 174| 152| 0
davepeng
|02-19-1999|01:05:32|@ 1 hour 35 mins 47 secs | |205.252.137.19 | 48| 364065|
1513856| 5729| 4991| 0
chengmk
|02-15-1999|12:45:59|@ 43 mins 33 secs | |205.252.137.19 | 43| 213545|
2177439| 3815| 4963| 0
chengmk
|02-16-1999|15:40:02|@ 38 mins 33 secs | |205.252.137.19 | 44| 347902|
2939344| 5723| 7103| 0
chengmk
|02-18-1999|13:45:09|@ 44 mins | |205.252.137.19 | 32| 196040|
1956093| 3448| 4517| 0
chengmk
|02-18-1999|13:50:10|@ 4 mins 11 secs | |205.252.137.19 | 14| 1699|
80| 7| 0| 0
chengmk
|02-18-1999|21:20:10|@ 1 hour 4 mins 5 secs | |205.252.137.19 | 21| 442830|
4568541| 8199| 10616| 0
sccheng
|02-17-1999|02:34:01|@ 1 hour 52 mins 36 secs | |205.252.137.143 | 66| 0|
0| 0| 0| 0
sccheng
|02-18-1999|02:23:44|@ 1 hour 37 mins 28 secs | |205.252.137.143 | 5| 0|
0| 0| 0| 0
sccheng
|02-18-1999|20:58:03|@ 1 hour 23 mins 49 secs | |205.252.137.143 | 18| 0|
0| 0| 0| 0
chengyee
|02-18-1999|16:30:30|@ 14 mins 57 secs | |205.252.137.143 | 54| 0|
0| 0| 0| 0
chengyee
|02-18-1999|17:49:17|@ 1 hour 16 mins 13 secs | |205.252.137.143 | 41| 0|
0| 0| 0| 0
chengyee
|02-18-1999|18:42:36|@ 50 mins 22 secs | |205.252.137.143 | 7| 0|
0| 0| 0| 0
chengyee
|02-18-1999|19:09:29|@ 1 min 5 secs | |205.252.137.143 | 43| 0|
0| 0| 0| 0
chengyee
|02-18-1999|19:20:12|@ 47 secs | |205.252.137.143 | 53| 0|
0| 0| 0| 0
chengyee
|02-18-1999|22:47:36|@ 2 hours 24 mins 13 secs| |205.252.137.143 | 84| 0|
0| 0| 0| 0
(75 rows)