December 29, 2011 at 2:14 am
I have the following fields in my table.
UserName DateTime Action
abc 22/1/2011 23:45 xyz
abc 23/1/2011 10:00 pqr
abc 24/1/2011 12:00 zxc
abc 24/1/2011 13:00 fgh
def 24/1/2011 15:44 gpr
ghi 24/1/2011 16:00 klm
abc 25/1/2011 10:50 lij
abc 25/1/2011 10:00 mnr
abc 26/1/2011 10:00 opo
I I want to find the start time and end end time for particular user 'abc'
December 29, 2011 at 2:33 am
DECLARE @TableO TABLE
(UserName VARCHAR(100),[DateTime] DATETIME,[Action] VARCHAR(50))
INSERT INTO @TableO
SELECT 'abc','2011-01-22 23:45','xyz' UNION ALL
SELECT 'abc','2011-01-23 10:00','pqr' UNION ALL
SELECT 'abc','2011-01-24 10:00','pqr' UNION ALL
SELECT 'dtr','2011-01-24 10:00','pqr' UNION ALL
SELECT 'abc','2011-01-25 10:00','pqr' UNION ALL
SELECT 'abc','2011-01-26 10:00','pqr' UNION ALL
SELECT 'abc','2011-01-27 10:00','pqr'
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [DateTime]) AS RNo
FROM @TableO
)
SELECT MIN([DateTime]) AS 'Start Time',MAX([DateTime]) AS 'End Time' FROM CTE
WHERE
UserName = 'abc'
Shatrughna
December 29, 2011 at 3:04 am
It works fine for a particular user.But I need start time,end time for all users in my table.
December 29, 2011 at 3:14 am
DECLARE @Tbl TABLE (
UserName VARCHAR(100) ,
DateTime DATETIME ,
Action VARCHAR(50)
)
INSERT INTO @Tbl ( UserName , DateTime , Action)
VALUES
('abc','2011/01/22 23:45','xyz'),
('abc','2011/01/23 10:00','pqr'),
('abc','2011/01/24 12:00','zxc'),
('abc','2011/01/24 13:00','fgh'),
('def','2011/01/24 15:44','gpr'),
('ghi','2011/01/24 16:00','klm'),
('abc','2011/01/25 10:50','lij'),
('abc','2011/01/25 10:00','mnr'),
('abc','2011/01/26 10:00','opo')
SELECT MIN(DateTime) AS StartTime ,
MAX(DateTime) AS EndTime,
UserName
FROM @Tbl
GROUP BY UserName
If that doesn't do what you want, then please clarify exactly what you mean by start and end times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2011 at 3:23 am
GilaMonster,
This what I want.But have huge table with thousands of records.How to load these records into temp table.
December 29, 2011 at 3:31 am
Err, you wouldn't. Why would you want to load them into a temp table?
I've used a table variable to show the concept, because I don't have your table, so I need to create something to test the results. If you have the real table, you'd use the real table. Just change the query so it uses the correct table name (which you didn't tell us)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2011 at 3:47 am
Yes I got it.Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply