Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Date Time PS/SQL

Re: Date Time PS/SQL

From: swaxolez <mvsunstar_at_yahoo.ca>
Date: 26 Jul 2006 10:41:41 -0700
Message-ID: <1153935701.654260.248270@m79g2000cwm.googlegroups.com>


I've got it figured out now. I created a view using the lag function and then performed the time difference calculation on that view and presto it works fantastically.

Thanks again,

swaxolez wrote:
> Thanks everyone analyic function are exactly what I needed. I have it
> working now the way I want except I'm having trouble returning my time
> difference in minutes. Here is what I've tried so far:
>
> SELECT passkey,
> ID,
> datetime,
> LAG(datetime, 1) OVER (partition by passkey ORDER BY
> ID,passkey,datetime) AS PreviousDateTime,
> floor(datetime - LAG(datetime, 1)*24*60) OVER (ORDER BY
> datetime) AS Date_Difference
> FROM e_table;
>
> I keep getting a windowing error. I've looked at some info on the net
> but nothing is making much sense to me.
>
> Thanks again for the help so far.
>
> Cheers
>
> > > Brian
> > >
> >
> > swaxolez, if I understand the problem correctly at the time of insert
> > the only data known is the passkey and the scanned_datetime. This is
> > actually the only columns you need to define in your table. You can
> > use a view to calculate the other two columns when the data is
> > retrieved as the Last_Scanned_DateTime is actually just the
> > Scanned_DateTime of the prior row for the same Passkey based on
> > Scanned_DateTime ordering. You can easily use an analytic function
> > such as lead or lag to calculate this time per Passkey.
> >
> > If you must store this information then you should select the
> > max(Scanned_DateTime) for a Passkey value and use this row to provide
> > the Last_Scanned_DateTime and in the calculation of the TimeDifference.
> > Again as Brian pointed out there is no need to store the calculated
> > value.
> >
> > HTH -- Mark D Powell --
Received on Wed Jul 26 2006 - 12:41:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US