December 30, 2011 at 11:14 pm
Sample data
User Action DateTime
Smith inserted 22/7/2010 8:33
Andy inserted 22/7/2010 9:40
Steve collected 22/7/2010 10:01
Smith Update 22/7/2010 10:05
Scott inserted 22/7/2010 10:10
Paul inserted 22/7/2010 10:30
Smith collected 22/7/2010 10:45
Smith collected 22/7/2010 10:50
Smith deleted 22/7/2010 11:15
Smith inserted 22/7/2010 12:15
Desired output
User Action Time TimeDifference
Smith Inserted 22/7/2010 8:33
Smith deleted 22/7/2010 11:15
I need to find the time difference between Inserted(first) and deleted(before next inserted)
December 30, 2011 at 11:51 pm
This is the sample data in a more usable format:
CREATE TABLE dbo.Example
(
[User] VARCHAR(50) NOT NULL,
[Action] VARCHAR(10) NOT NULL,
[DateTime] DATETIME NOT NULL
);
GO
INSERT dbo.Example
([User], [Action], [DateTime])
VALUES
('Smith', 'inserted', '2010-07-22 08:33'),
('Andy', 'inserted', '2010-07-22 09:40'),
('Steve', 'collected', '2010-07-22 10:01'),
('Smith', 'Update', '2010-07-22 10:05'),
('Scott', 'inserted', '2010-07-22 10:10'),
('Paul', 'inserted', '2010-07-22 10:30'),
('Smith', 'collected', '2010-07-22 10:45'),
('Smith', 'collected', '2010-07-22 10:50'),
('Smith', 'deleted', '2010-07-22 11:15'),
('Smith', 'inserted', '2010-07-22 12:15');
The sample output is missing values for the time difference, and explanation doesn't go into enough detail for me to guess what you are after.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply