SQL Server Performance

Help with complex query

Discussion in 'SQL Server 2005 General Developer Questions' started by everts, Feb 13, 2011.

  1. everts New Member

    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?
  2. Flexdog New Member

    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)

Share This Page