October 21, 2011 at 10:00 am
Hi All,
I am currently working on a POC to generate a plan on already existing data.
let me explain...
assuming that current date is day 1, i need to plan the work for employee X,Y,Z
i know that X will be on PDO on day 3,4,5
Y already has 6 hours of work assigned to him on the day 4
Overhead activities:-
day 1: 2 hrs
day 2: 1 hrs
day 3: 3 hrs
day 4: 1 hrs
day 5: 1 hrs
Now, i have a list of projects that i need to plan for those resources based on the deadline.
A - deadline day 5 - estimate = 10 - assigned to X
B - deadline day 6 - estimate = 4 - Assigned to Z
C - deadline day 2 - estimate = 9 - Assigned to Z
D - deadline day 5 - estimate = 20 - Assigned to Y
My question is:-
Can I generate an MS project like plan to determine capacity for X,Y,X using SPs and table variable
knowing that
(1) no more than 8 hours are available in a day
(2) projects with closer deadlines have higher priority and need to be completed at any cost.
I could place the data in a table var and update each and every field when > 8 (or use triggers), but i want to limit the number of updates to the cells.
Is that something that could be implemented.
Thanks in advance.
Avi
October 21, 2011 at 10:06 am
It should be possible to do something like that. If you want help you are going to need to provide a LOT more information. Check out the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2011 at 11:30 am
If you're trying to do this with updates in cells, and plan on putting in rows or columns for each person's work, why use SQL? I might suggest Excel does a better job with some formulas of displaying this data and handling updates.
If you're trying to rebuild something that calculates open hours, as Sean mentioned, you'll need to provide more info and more definitions on how you will set boundaries for the various scenarios you need.
October 21, 2011 at 1:45 pm
Joel Spolsky has a good article on using Excel for scheduling work time. You can find it here:
http://www.joelonsoftware.com/articles/fog0000000245.html
He makes the (very interesting point) that Excel has a lot of great features that you can use for scheduling purposes, because that's what the Excel team at Microsoft used for their software development schedules! (By the way. that is a great example of "eating your own dogfood".)
If you need something more complex than that, why not buy Microsoft Project (or some other planning software). Unless you are doing this for your own entertainment, why re-invent the wheel?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply