sql select next? - 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

sql select next?

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-03 : 14:06:18

Hi i have a query that i need to write and i need an idea on how to do it.

so i have people that do different jobs for a certain amount of time

p1 - sweeping - 12:00
p1 - sweeping - 12:15
p1 - sweeping - 12:30
p1 - garbage - 12:45
p1 - sweeping - 12:50
p1 - sweeping - 12:55

basically i want to make a view that will only display the first time that something is being done and ignores the other events if it's the same one

so my view should show this
p1 - sweeping - 12:00
p1 - garbage - 12:45
p1 - sweeping - 12:50

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-03 : 14:24:46

SQL 2000, 2005 or 2008?

--
Gail Shaw
SQL Server MVP

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-03 : 14:25:35

2008

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-04 : 05:34:34

Easy in 2005 and 2008.

SELECT Person, job, time
FROM
(SELECT Person, job, time, ROW_NUMBER() OVER (Partition by Person, job ORDER By Time) as RowNo
FROM
SomeTable) sub
WHERE RowNo = 1

--
Gail Shaw
SQL Server MVP

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-05 : 15:25:48

ok that looks good but i also need the last sweeping because it's the last one so if someone wanted to see total sweeping, they would only see the 12:50 and they don't know how long that one was for (5 minutes) and suggestions?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-05 : 17:05:17

That was not in your initial requirement...

Not as clean, but should work (no guarantees on performance if it's a large table. Test and check yourself). I assume you want the last time for each person, not just the latest time. Hard to tell because you only have one person listed.

SELECT Person, job, time
FROM
(SELECT Person, job, time, ROW_NUMBER() OVER (Partition by Person, job ORDER By Time) as JobRow, ROW_NUMBER() OVER (Partition by Person ORDER By Time DESC) AS PersonRow
FROM
SomeTable) sub
WHERE (JobRow = 1 OR PersonRow = 1)

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 17:17:00

Here.

DECLARE	@Sample TABLE
	(
		Person VARCHAR(10),
		Job VARCHAR(10),
		TimeValue VARCHAR(5)
	)

INSERT	@Sample
SELECT	'p1', 'sweeping', '12:00' UNION ALL
SELECT	'p1', 'sweeping', '12:15' UNION ALL
SELECT	'p1', 'sweeping', '12:30' UNION ALL
SELECT	'p1', 'garbage', '12:45' UNION ALL
SELECT	'p1', 'sweeping', '12:50' UNION ALL
SELECT	'p1', 'sweeping', '12:55'

;WITH cteSource(Person, Job, TimeValue, allID, jobID)
AS (
	SELECT	Person,
		Job,
		TimeValue,
		ROW_NUMBER() OVER (PARTITION BY Person ORDER BY TimeValue) AS allID,
		ROW_NUMBER() OVER (PARTITION BY Person, Job ORDER BY TimeValue) AS laborID
	FROM	@Sample
)
SELECT		Person,
		Job,
		TimeValue
FROM		(
			SELECT	Person,
				Job,
				TimeValue,
				ROW_NUMBER() OVER (PARTITION BY allID - jobID ORDER BY TimeValue) AS recID
			FROM	cteSource
		) AS d
WHERE		recID = 1
ORDER BY	Person,
		TimeValue

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources