 |
 |
Archives of the TeradataForum
Message Posted: Tue, 11 Nov 2008 @ 08:40:18 GMT
| Subj: | | Re: Time interval between events |
| |
| From: | | Dieter Noeth |
Peter Shvets wrote:
| | I have the follwoing table tracking part installation event at a specific location | |
> partID time_installed location
> P1 12/20/2007 L1
> P1 12/31/2007 L2
> P1 1/15/2008 L3
> P2 2/1/2008 L1
> P2 2/5/2008 L2
> P2 4/5/2008 L3
> P3 4/30/2008 L1
> P3 5/3/2008 L2
| | I need to identify how long each part has been installed at each location in SQL. Every next install invalidates the previous one for the
same partID. | |
SELECT
partid,
COALESCE(MIN(time_installed) OVER
(PARTITION BY partid
ORDER BY time_installed
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
, CURRENT_DATE)
- time_installed AS delta,
location
FROM tab
Dieter
| |
