August 5, 2014 at 3:29 pm
Hello. I have a table of project data. In one column, we estimate the number of hours the project will take. I am having problems creating a query that will forecast how long each will take based on the completion of the one prior to it and using a daily average production rate of let's say 5 hrs/day.
So several projects could be completed in one day, as long as they didn't exceed the 5 hour production threshhold.
Any help would be appreciated. Thanks!
Below is some sample data you could use.
create table #temp(priority int,project varchar(255),estimatehrs int)
insert #temp (priority,project,estimatehrs) values (1,'Project 1',2)
insert #temp (priority,project,estimatehrs) values (2,'Project 2',6)
insert #temp (priority,project,estimatehrs) values (3,'Project 3',5)
insert #temp (priority,project,estimatehrs) values (4,'Project 4',2)
insert #temp (priority,project,estimatehrs) values (5,'Project 5',3)
insert #temp (priority,project,estimatehrs) values (6,'Project 6',1)
insert #temp (priority,project,estimatehrs) values (7,'Project 7',2)
insert #temp (priority,project,estimatehrs) values (8,'Project 8',1)
insert #temp (priority,project,estimatehrs) values (9,'Project 9',3)
insert #temp (priority,project,estimatehrs) values (10,'Project 10',5)
August 5, 2014 at 5:53 pm
Since you're on SQL 2012, you can use some of the new partitioning functionality to do this. Take a look at this blog post: http://ole.michelsen.dk/blog/calculate-a-running-total-in-sql-server-2012/
I only have a 2008 R2 instance available to me at the moment so here's what I came up with. This query assume 5 hours/day, and that you are starting work on the projects "today" and that you have all 5 hours of productive work left today to make progress.
create table #temp(priority int,project varchar(255),estimatehrs int);
insert #temp (priority,project,estimatehrs) values (1,'Project 1',2);
insert #temp (priority,project,estimatehrs) values (2,'Project 2',6);
insert #temp (priority,project,estimatehrs) values (3,'Project 3',5);
insert #temp (priority,project,estimatehrs) values (4,'Project 4',2);
insert #temp (priority,project,estimatehrs) values (5,'Project 5',3);
insert #temp (priority,project,estimatehrs) values (6,'Project 6',1);
insert #temp (priority,project,estimatehrs) values (7,'Project 7',2);
insert #temp (priority,project,estimatehrs) values (8,'Project 8',1);
insert #temp (priority,project,estimatehrs) values (9,'Project 9',3);
insert #temp (priority,project,estimatehrs) values (10,'Project 10',5);
SELECT
t.priority
, t.project
, t.estimatehrs
, (SELECT sum(estimateHrs) FROM #temp t2 WHERE t2.priority <= t.priority) AS [TotalWorkingHoursSoFar]
, DATEADD(DAY, (SELECT sum(estimateHrs) FROM #temp t2 WHERE t2.priority <= t.priority) / 5, CAST(CURRENT_TIMESTAMP as date)) AS [FinishDate]
FROM #temp t
DROP TABLE #temp;
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
August 5, 2014 at 7:49 pm
Thanks for your reply, I appreciate you taking the time to help with this.
After testing your query with a few different scenarios, I came across a slight problem. I have included the scenario data below so you can see what I'm referring to. The priority 1, 2 and 3 projects have a total estimate of 5 hours, so all 3 should be able to be completed today based on our productivity rate of 5 hours/day. However, the priority 3 project is being forecast to be completed by the following day.
create table #temp(priority int,project varchar(255),estimatehrs int);
insert #temp (priority,project,estimatehrs) values (1,'Project 1',2);
insert #temp (priority,project,estimatehrs) values (2,'Project 2',2);
insert #temp (priority,project,estimatehrs) values (3,'Project 3',1);
insert #temp (priority,project,estimatehrs) values (4,'Project 4',2);
insert #temp (priority,project,estimatehrs) values (5,'Project 5',3);
insert #temp (priority,project,estimatehrs) values (6,'Project 6',1);
insert #temp (priority,project,estimatehrs) values (7,'Project 7',2);
insert #temp (priority,project,estimatehrs) values (8,'Project 8',1);
insert #temp (priority,project,estimatehrs) values (9,'Project 9',3);
insert #temp (priority,project,estimatehrs) values (10,'Project 10',5);
August 5, 2014 at 8:44 pm
Try this out. Updated for SQL 2012 as well.
create table #temp(priority int,project varchar(255),estimatehrs int);
insert #temp (priority,project,estimatehrs) values (1,'Project 1',2);
insert #temp (priority,project,estimatehrs) values (2,'Project 2',2);
insert #temp (priority,project,estimatehrs) values (3,'Project 3',1);
insert #temp (priority,project,estimatehrs) values (4,'Project 4',2);
insert #temp (priority,project,estimatehrs) values (5,'Project 5',3);
insert #temp (priority,project,estimatehrs) values (6,'Project 6',1);
insert #temp (priority,project,estimatehrs) values (7,'Project 7',2);
insert #temp (priority,project,estimatehrs) values (8,'Project 8',1);
insert #temp (priority,project,estimatehrs) values (9,'Project 9',3);
insert #temp (priority,project,estimatehrs) values (10,'Project 10',5);
SELECT
ProjectHours.[priority]
, ProjectHours.project
, ProjectHours.estimatehrs
, ProjectHours.RequiredHours
, DATEADD(DAY, (ProjectHours.RequiredHours / 5) - CASE WHEN ProjectHours.RequiredHours % 5 = 0 AND ProjectHours.RequiredHours >=5 THEN 1 ELSE 0 END, CAST(CURRENT_TIMESTAMP AS date))
FROM
(
SELECT
t.[priority]
, t.project
, t.estimatehrs
, SUM(t.estimatehrs) OVER (ORDER BY t.priority) AS [RequiredHours]
FROM #temp t
) AS [ProjectHours]
DROP TABLE #temp
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
August 5, 2014 at 9:50 pm
This is beautiful. Thank you very much.
I was trying with a case like you did, but without success. I am curious about this piece of your code:
CASE WHEN ProjectHours.RequiredHours % 5
What is the % doing? I am not familiar with this. I tried researching it but I'm not quite sure what it's called. When I searched for % or percentage I just got inundated with percentage calculation tutorials etc.
Thanks again for your help.
August 5, 2014 at 10:26 pm
% is the Modulo operator.
http://msdn.microsoft.com/en-us/library/ms190279(v=sql.110).aspx
Essentially it returns the remainder of a division operation.
So 7 % 5 would = 2, since 7 divided by 5 is 1 remainder 2.
5 % 5 would be 0, since there is no remainder.
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
August 6, 2014 at 6:49 am
Excellent, thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply