Hello everyone, I have the following table in which I have a costumer name, transaction date y transaction time (two rows by customer): Customer Trans_date Trans_time CUSTOMERA 2011-02-12 16:48:30 CUSTOMERA 2011-02-12 16:48:36 CUSTOMERB 2011-01-29 08:51:54 CUSTOMERB 2011-01-29 08:56:31 CUSTOMERA 2011-02-14 16:48:30 CUSTOMERA 2011-02-14 16:48:36 I need to group the records by customer name: Customer Trans_date Trans_sTime Trans_fTime Elapsed_Time CUSTOMERA 2011-02-12 16:48:30 16:48:36 X CUSTOMERB 2011-01-29 08:51:54 08:56:31 X CUSTOMERA 2011-02-14 16:48:30 16:48:36 X But, it can be a situation in which a customer does not present the second read (unpaired record?) Does anyone faced a similar problem?
pseudo tsql for the given data SELECT customer, trans_date, 'stime'=min(trans_time), 'ftime'=max(trans_time), 'elapsed'=datediff(s,min(trans_time),max(trans_time)) FROM t GROUP BY customer, trans_date Dealing with datetime partition/boundary/npair -> yep, it's more complex (CTE is not optimal but ranking may be useful for a small dataset)