Complex (4 Me!) Sql Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complex (4 Me!) Sql Query

Author

Topic

Everts
Starting Member

2 Posts

Posted - 2011-02-13 : 13:38:33

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-13 : 15:11:21

Yes. Yes.

N 56°04'39.26"
E 12°55'05.63"

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-13 : 19:31:24

-- I subtracted one second from the Trans_Time values on the CUSTOMERA group for 2011-02-14
-- just to show it was different than the group on 2011-01-29 in the output.


CREATE TABLE CustomerTransactions
(CustomerTransactionID         INTEGER
,Customer                      NVARCHAR(9)
,Trans_Date                    DATE
,Trans_Time                    TIME
,CONSTRAINT pk_CustomerTransactions PRIMARY KEY (CustomerTransactionID)
)
go

INSERT INTO CustomerTransactions
(CustomerTransactionID
,Customer
,Trans_Date
,Trans_Time
)
SELECT 1, 'CUSTOMERA', '2011-02-12', '16:48:30' UNION
SELECT 2, 'CUSTOMERA', '2011-02-12', '16:48:36' UNION
SELECT 3, 'CUSTOMERB', '2011-01-29', '08:51:54' UNION
SELECT 4, 'CUSTOMERB', '2011-01-29', '08:56:31' UNION
SELECT 5, 'CUSTOMERA', '2011-02-14', '16:47:30' UNION
SELECT 6, 'CUSTOMERA', '2011-02-14', '16:47:36' 
go

  SELECT CT1.Customer
        ,CT1.Trans_Date
        ,MIN(CT1.Trans_Time) AS Trans_sTime
        ,MAX(CT1.Trans_Time) AS Trans_fTime
        ,DATEDIFF(second, Min(CT1.Trans_Time), Max(CT1.Trans_Time)) AS ElapsedTime
    FROM CustomerTransactions AS CT1
GROUP BY CT1.Customer
        ,CT1.Trans_Date
go

Customer    Trans_Date   Trans_sTime        Trans_fTime         ElapsedTime
CUSTOMERB   2011-01-29   08:51:54.0000000   08:56:31.0000000	277
CUSTOMERA   2011-02-12   16:48:30.0000000   16:48:36.0000000	6
CUSTOMERA   2011-02-14   16:47:30.0000000   16:47:36.0000000	6

If there is no second record for a particular date, then there is no elapsed time and there is nothing to calculate. You did not mention what you wanted done in this case. (The above code will issue an elapsed time of zero for those cases currently.)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-13 : 19:45:47

[code]
declare @tbl table
(
Customer varchar(10),
Trans_Date datetime,
Trans_Time datetime
)
insert into @tbl
SELECT 'CUSTOMERA', '2011-02-12', '16:48:30' UNION ALL
SELECT 'CUSTOMERA', '2011-02-12', '16:48:36' UNION ALL
SELECT 'CUSTOMERB', '2011-01-29', '08:51:54' UNION ALL
SELECT 'CUSTOMERB', '2011-01-29', '08:56:31' UNION ALL
SELECT 'CUSTOMERA', '2011-02-14', '16:48:30' UNION ALL
SELECT 'CUSTOMERA', '2011-02-14', '16:48:36' UNION ALL
SELECT 'CUSTOMERB', '2011-01-29', '10:51:54'

select Customer, Trans_Date, Trans_sTime = min(Trans_Time), Trans_fTime = max(Trans_Time)
from (
select Customer, Trans_Date, Trans_Time,
row_no = row_number() over (partition by Trans_Date order by Trans_Time)
from @tbl
) t
group by Customer, (row_no - 1) / 2, Trans_Date[/code]

KH
[spoiler]Time is always against us[/spoiler]

Everts
Starting Member

2 Posts

Posted - 2011-02-13 : 23:30:28

Wow! thanks guys! It took me a while (since I am a completely newbie) realize that the data types used were from SQL2008, but it works like a charm!
Thanks chris_n_osborne! You saved my day! (and job!)

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-14 : 08:54:41

quote:
Originally posted by Everts

Thanks chris_n_osborne! You saved my day! (and job!)

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources