Complex Percent complete over Time query problem

Question

INTRO

I have a query problem that I have been revisiting time to time over the past few months that just seems simple as can be when i think it in my head, but kicks my butt every time I try to implement it. So I'm seeking your help.

SETUP

I have a few tables in play here.... I'm going to lay them out with general wording, in hopes of keeping it as simple as possible. Please excuse me if I miss an important detail on the first go around.

First have a table tracking the manufacturing of widgets. Each widget has a number of properties tracked (columns), as well as a quantity to be manufactured for this job AND amount of time allotted for the manufacturing of them.

Table 1 Eg.:

WidgetName(UID) | Size | Shape | Color | Quantity | EstimatedHrs
Widget1         | 2lbs | Square| Red   | 7        | 21
Widget2         | 5lbs | Circle| Red   | 2        | 18
Widget3         | 5lbs | Circle| Blue  | 5        | 45
ect....

Second I have a table that records how much time has been spent working on each widget type and who worked on them, and When. (for the sake of simplicity, assume that once the number of Hrs working on them = EstimatedHrs, Widget quantity is 100% complete)

Table 2 Eg.:

LogEntry(UID)  | UserID  |  WidgetName  |  HrsWorked  |  Date/TimeStamp
101            | 3       |  Widget1     |   3         | 2016-10-20 13:57:57
102            | 3       |  Widget2     |   1.5       | 2016-10-20 13:57:57
103            | 3       |  Widget1     |   5         | 2016-10-21 14:57:57

CURRENT UNDERSTANDINGS

In the above setup, We assume the current "Job" is everything in Table 1. So My method for finding the Current Percent complete for this "Job" is relatively simple.

SELECT SUM(COALESCE(t2.HrsWorkd,0) / t1.EstimatedHrs * 100) AS JobPercentComplete 
FROM Table1 t1
LEFT JOIN Table2 t2
  ON t2.WidgetName = t1.WidgetName

I have been successful with finding the percentComplete of different groupings. Eg. what PercentComplete are we for all "Red" widgets?

SELECT SUM(COALESCE(t2.HrsWorkd,0) / t1.EstimatedHrs * 100) AS PercentComplete 
FROM Table1 t1
LEFT JOIN Table2 t2
  ON t2.WidgetName = t1.WidgetName
GROUP BY t1.Color /*Shape,Size,ect...*/

This ability to get percent complete by different grouping via changing only that GROUP BY statement, lends very nicely to putting in a stored procedure and returning different results based off a passed in string.

FINALLY

I have also been successful getting the JobPercentComplete Over Time via:

SET @csum := 0;

SELECT Date(t2.Date/TimeStamp) AS Date,
@csum := @csum + (SUM(COALESCE(t2.HrsWorkd,0) / t1.EstimatedHrs * 100)) AS CumulativePercentComplete

FROM Table1 t1
LEFT JOIN Table2 t2
   ON t2.WidgetName = t1.WidgetName
GROUP BY DATE(t2.Date/TimeStamp)
ORDER BY DATE(t2.Date/TimeStamp)

QUESTION:

After all of that, this is the simple question I can NOT make an EASY to manipulate statement to answer. Show me the Percent complete over time grouped by Color.

FAILED EXAMPLE 1

SET @csum := 0;

SELECT Date(t2.Date/TimeStamp) AS Date,
@csum := @csum + (SUM(COALESCE(t2.HrsWorkd,0) / t1.EstimatedHrs * 100)) AS CumulativePercentComplete

FROM Table1 t1
LEFT JOIN Table2 t2
   ON t2.WidgetName = t1.WidgetName
GROUP BY DATE(t2.Date/TimeStamp), t1.Color 

The above fails because the double grouping makes it so t1.EstimatedHrs != the total estimated hours for ALL of the color.. but instead only for the Estimated hours for All of the color AND same Date... So if a widget wasn't worked on that day, the calculation is off.

FAILED EXAMPLE 2

SET @csum := 0;
SET @currentColor := 0;

SELECT Date(t2.Date/TimeStamp) AS Date,
@csum := @csum + (SUM(COALESCE(t2.HrsWorkd,0) / (SELECT sum(EstimatedHrs) FROM Table1)* 100)) AS CumulativePercentComplete

FROM Table1 t1
LEFT JOIN Table2 t2
   ON t2.WidgetName = t1.WidgetName
GROUP BY DATE(t2.Date/TimeStamp), t1.Color 

The above fails because the result from the Nested Select statement is the total of ALL EstimatedHrs, instead of just the Hrs for the individual Color. P.S. I suppose this could also be done as a join statement to run much quicker. But would still be the wrong number.

FAILED EXAMPLE 3

I have messed with some complex solutions all ending in failure for 2 reasons.. one, they didn't work, but most importiantly 2, I could not change the out put grouping of the statement by simply changing a grouping statement or such, so that I can use/maintain the SAME query to give results for multiple groupings.... As apposed to making a different query for every single grouping. Keep in mind this is a simplified data example.. the real world one has 10's of different potential grouping options.

CONCLUSION

This problem has proven to be far easier "said than done". I know I'm missing something obvious, so hopefully a fresh set of eyes can point me in the right direction. Thanks

Desired Result

Per Request this is the type of result I'm looking to get. With a single call to a Stored Procedure, passing in only a single string that will define what to group by.

Back I want to get

| Size | Shape | Color   |   HrsWorked   |  Date/TimeStamp      |  PercentComplete
| Rand | Rand  | RED     |       3       | 2016-10-20 13:57:57  |    42.9
| Rand | Rand  | RED     |       5       | 2016-10-21 13:57:57  |    101.1

First, I understand that depending on the grouping selected (Color/Size/Shape/ect..) That only the specified grouping will be a valid column, I'm okay managing that on the receiving end of this data. (Hence my usage of the word Rand for Random.

Second, I want the Percent complete to be Percent of "Red" widgets complete.. not Percent of "Job" complete. Hope this clarifies.

Answer 1

I think the grouping you are looking after is hrsworked along with color: Try this on test prepared by Insac on Rextester :

SET @csum := 0;

SELECT Date(t2.log_ts) AS Date,
@csum := @csum + (SUM(COALESCE(t2.HrsWorkd,0) / t1.EstimatedHrs * 100)) AS CumulativePercentComplete,t1.Color,HrsWorkd

FROM (Table1 t1
Left JOIN Table2 t2
   ON t2.WidgetName = t1.WidgetName)
     GROUP BY  t1.Color ,t2.HrsWorkd