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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy