Archives of the TeradataForum

Message Posted: Mon, 11 Nov 2008 @ 01:17:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello Teradata experts,

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.

I need to produce the following table

     partID    time_delta (days)     location
     P1        11                    L1
     P1        15                    L2
     P1        calculate based       L3
                 on current_date
     P2       4                      L1
     P2       60                     L2
     P2       calculate based        L3
                 on current_date
     P3       3                      L1
     P3       calculate based        L2
                 on current_date

I can not figure out how to handle intermittent installs in SQL. I was thinking about min and max time, but it leaves out one instlall in the middle.

Please help!

Regards,

Pit.

	<Prev	Next>		<<First	<Prev	Next>	Last>>