Re: Date Time PS/SQL

From: swaxolez <mvsunstar_at_yahoo.ca>
Date: 26 Jul 2006 10:07:47 -0700
Message-ID: <1153933667.127270.82960@s13g2000cwa.googlegroups.com>

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:07:47 CDT