All Forums Database
dr.murthy_td 7 posts Joined 06/13
23 Nov 2014
Teradata Pivot

I have a requirement where i need to implement pivot to achieve below result.
 
Source:
EMP_ID,Date,Seq_nbr,Punch
123,20-nov-2014,1,08:20
123,20-nov-2014,2,12:50
123,20-nov-2014,1,14:12
123,20-nov-2014,2,18:00
 
 
Target:
EMP_ID,Date,Seq_nbr,Punch_in,Punch_out
123,20-nov-2014,1,08:20,12:50
123,20-nov-2014,1,14:12,18:00
 
 
Can any one suggest on this...

D N Murthy
dnoeth 4628 posts Joined 11/04
26 Nov 2014

Is there Seq_nbr guaranteed to be always in the correct order: 1,2,1,2,1,2,...?

SELECT 
   EMP_ID,Date,Seq_nbr,
   Punch AS Punch_in,
   MIN(Punch)  -- next value
   OVER (PARTITION BY EMP_ID
         ORDER BY Date, Punch
         ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS Punch_out
FROM tab
QUALIFY Seq_nbr = 1

 

Dieter

You must sign in to leave a comment.