Is this possible in select query ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Is this possible in select query ?

Author

Topic

hztm2
Starting Member

16 Posts

Posted - 2010-07-27 : 12:12:24

Hi - I am trying to select the first row (the earliest start date) for each user and to find the difference in minutes between the start and end times - my source table is like this below:

START END USER
01/07/2010 08:00 01/07/2010 08:15 User1
01/07/2010 10:00 01/07/2010 10:30 User1
01/07/2010 11:00 01/07/2010 14:15 User1

01/07/2010 09:00 01/07/2010 10:02 User2
01/07/2010 13:00 01/07/2010 13:55 User2
01/07/2010 14:00 01/07/2010 17:04 User2

I am looking to return the following:

DATE USER TIME(MINS)
01/07/2010 User1 15
01/07/2010 User2 62

I can format the date, etc but I cannot work out how to pickup just the earliest dated row for each user - I am running the query directly from VB.NET rather than a SP. Thank you very much indeed for any assistance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-27 : 12:39:29

Try this:

Select convert(varchar,Startdate,103) ,datediff(mi,Startdate,Enddate) TimeInMis,
User from
(Select row_number() over (partition by User order by Startdate) as Srno,
Startdate,Enddate,User from Yourtable) As St
Where St.RowNumber = 1

I didn't carry out any testing.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-27 : 12:42:53

Here are a couple way:

-- Setup Sample data
DECLARE @Table TABLE(StartDate DATETIME, EndDate DATETIME, UserName VARCHAR(50))

INSERT @Table
SELECT '01/07/2010 08:00', '01/07/2010 08:15', 'User1'
UNION ALL SELECT '01/07/2010 10:00', '01/07/2010 10:30', 'User1'
UNION ALL SELECT '01/07/2010 11:00', '01/07/2010 14:15', 'User1'
UNION ALL SELECT '01/07/2010 09:00', '01/07/2010 10:02', 'User2'
UNION ALL SELECT '01/07/2010 13:00', '01/07/2010 13:55', 'User2'
UNION ALL SELECT '01/07/2010 14:00', '01/07/2010 17:04', 'User2'


-- Query One
SELECT 
	StartDate, 
	UserName, 
	DATEDIFF(MINUTE, StartDate, EndDate) AS TimeInMinutes
FROM
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY StartDate) AS RowNum
		FROM @Table
	) AS T
WHERE
	RowNum = 1
	
-- Query 2	
SELECT
	T.StartDate, 
	T.UserName, 
	DATEDIFF(MINUTE, T.StartDate, T.EndDate) AS TimeInMinutes
FROM 
	@Table AS T
INNER JOIN
	(
		SELECT MIN(StartDate) AS StartDate, UserName
		FROM @Table
		GROUP BY UserName
	) AS D
	ON T.UserName = D.UserName
	AND T.StartDate = D.StartDate

hztm2
Starting Member

16 Posts

Posted - 2010-07-27 : 13:04:35

Wow - first a very big thank you - could I ask if it is possible though to do the same but over multiple dates. So we get the first time for each day

START END USER
01/07/2010 08:00 01/07/2010 08:15 User1
01/07/2010 10:00 01/07/2010 10:30 User1
01/07/2010 11:00 01/07/2010 14:15 User1

01/07/2010 09:00 01/07/2010 10:02 User2
01/07/2010 13:00 01/07/2010 13:55 User2
01/07/2010 14:00 01/07/2010 17:04 User2

02/07/2010 08:00 02/07/2010 08:30 User1
02/07/2010 10:00 02/07/2010 10:30 User1
02/07/2010 11:00 02/07/2010 14:15 User1

I am looking to return the following:

DATE USER TIME(MINS)
01/07/2010 User1 15
01/07/2010 User2 62
02/07/2010 User1 30

Thank you so much for your assistance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-27 : 13:15:30

Building on what Lamprey has provided

-- Setup Sample data
DECLARE @Table TABLE(StartDate DATETIME, EndDate DATETIME, UserName VARCHAR(50))

INSERT @Table
SELECT '01/07/2010 08:00', '01/07/2010 08:15', 'User1'
UNION ALL SELECT '01/07/2010 10:00', '01/07/2010 10:30', 'User1'
UNION ALL SELECT '01/07/2010 11:00', '01/07/2010 14:15', 'User1'
UNION ALL SELECT '01/07/2010 09:00', '01/07/2010 10:02', 'User2'
UNION ALL SELECT '01/07/2010 13:00', '01/07/2010 13:55', 'User2'
UNION ALL SELECT '01/07/2010 14:00', '01/07/2010 17:04', 'User2'
UNION ALL SELECT '02/07/2010 08:00', '02/07/2010 08:30', 'User1'
UNION ALL SELECT '02/07/2010 10:00', '02/07/2010 10:30', 'User1'
UNION ALL SELECT '02/07/2010 11:00', '02/07/2010 14:15', 'User1'

-- Query One

SELECT 
	StartDate, 
	UserName, 
	DATEDIFF(MINUTE, StartDate, EndDate) AS TimeInMinutes
FROM
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY UserName,convert(varchar(10),StartDate,120) ORDER BY StartDate) AS RowNum
		FROM @Table
	) AS T
WHERE
	RowNum = 1
Order by StartDate

hztm2
Starting Member

16 Posts

Posted - 2010-07-27 : 13:27:42

Thank you all VERY VERY much - this does exactly what I need. I cannot thank you enough.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-27 : 13:45:02

Np. You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources