Grouping by percentage of time worked by person

Question

Post reply

Grouping by percentage of time worked by person

david.john.gilligan

Default port

Points: 1471
More actions
July 2, 2014 at 3:06 am

#290533

Hello all. At first I was going to use Excel to do this but I was thinking SQL server might be better, esp. because I think it will end up being used more often as they currently do it manually. There is not a lot of data, so even if a loop is needed the run time will be short anyway.
What I want to do is I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
I hope the data below will help explain better. I am trying to avoid RBAR because it is slow and I am not so good at it, but in this case it can be used.
Any ideas SQL Geniuses?
Thanks,
Dave
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[PersonID] [int] NOT NULL,
[PercentLoad] [float] NOT NULL,
[MonthID] [int] NOT NULL
) ON [PRIMARY]
GO
The data:
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL
SELECT 2,123456,0,2 UNION ALL
SELECT 3,123456,0.5,3 UNION ALL
SELECT 4,123456,0.5,4 UNION ALL
SELECT 5,123456,1,5 UNION ALL
SELECT 6,123456,1,6 UNION ALL
SELECT 7,123456,0,7 UNION ALL
SELECT 8,123456,0,8 UNION ALL
SELECT 9,123456,0,9 UNION ALL
SELECT 10,123456,0,10 UNION ALL
SELECT 11,654321,1,1 UNION ALL
SELECT 12,654321,1,2 UNION ALL
SELECT 13,654321,0,3 UNION ALL
SELECT 14,654321,0.5,4 UNION ALL
SELECT 15,654321,0.75,5 UNION ALL
SELECT 16,654321,0.75,6 UNION ALL
SELECT 17,654321,0.5,7 UNION ALL
SELECT 18,654321,0.5,8 UNION ALL
SELECT 19,654321,0.5,9 UNION ALL
SELECT 20,654321,0,10
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #TestProject2 ON
EXPECTED RESULT:
Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this:

SELECT PersonID, StartMonth = MIN(MONTHID), EndMonth = MAX(MONTHID), LOADPCT = PercentLoad

FROM (

SELECT *,

[Grouper] = MONTHID - ROW_NUMBER() OVER(PARTITION BY PersonID, PercentLoad ORDER BY MONTHID)

FROM #TestProject2

) d

GROUP BY PersonID, PercentLoad, [Grouper]

HAVING PercentLoad > 0

ORDER BY PersonID, MIN(MONTHID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dohsan SSCrazy Points: 2972 More actions · Answer 2

This work?

SELECTT.PersonID,

StartMonth= MIN(MonthID),

EndMonth= MAX(MonthID),

T.PercentLoad

FROM

(

SELECTRN = T.MonthID - ROW_NUMBER() OVER (PARTITION BY T.PersonID,T.PercentLoad ORDER BY T.PercentLoad),

T.PersonID,

T.PercentLoad,

T.MonthID

FROM#TestProject2 AS T

WHERET.PercentLoad > 0

) AS T

GROUPBYT.PersonID,

T.PercentLoad,

T.RN

ORDERBYT.PersonID ASC,

StartMonth ASC

Dohsan SSCrazy Points: 2972 More actions · Answer 3

ChrisM@Work (7/2/2014)
Try this:
SELECT PersonID, StartMonth = MIN(MONTHID), EndMonth = MAX(MONTHID), LOADPCT = PercentLoad
FROM (
SELECT *,
[Grouper] = MONTHID - ROW_NUMBER() OVER(PARTITION BY PersonID, PercentLoad ORDER BY MONTHID)
FROM #TestProject2
) d
GROUP BY PersonID, PercentLoad, [Grouper]
HAVING PercentLoad > 0
ORDER BY PersonID, MIN(MONTHID)

Hah too slow it seems!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Dohsan (7/2/2014)
ChrisM@Work (7/2/2014)
Try this:
SELECT PersonID, StartMonth = MIN(MONTHID), EndMonth = MAX(MONTHID), LOADPCT = PercentLoad
FROM (
SELECT *,
[Grouper] = MONTHID - ROW_NUMBER() OVER(PARTITION BY PersonID, PercentLoad ORDER BY MONTHID)
FROM #TestProject2
) d
GROUP BY PersonID, PercentLoad, [Grouper]
HAVING PercentLoad > 0
ORDER BY PersonID, MIN(MONTHID)
Hah too slow it seems!

Not by much, Dohsan 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

david.john.gilligan Default port Points: 1471 More actions · Answer 5

Thanks guys...nice and elegant solution....much better that what I was going for. Works with my test data, so I believe it looks like it will work with real data.

Thanks again,

Dave