I have been working on a query that will return a suggested start date for a manufacturing line based on due date and the number of minutes needed to complete the task.
There is a calendar table(LINE_ID, CALENDAR_DATE, SCHEDULED_MINUTES) that displays per manufacturing line, the number of minutes scheduled for that day.
Example: (Usually 3 shifts worth of time scheduled per day, no weekends but can vary)
1, 06/8/2010 00:00:00.000, 1440
1, 06/7/2010 00:00:00.000, 1440
1, 06/6/2010 00:00:00.000, 0
1, 06/5/2010 00:00:00.000, 0
1, 06/4/2010 00:00:00.000, 1440
In order to get the suggested start date, I need to start with the due date and iterate downward through the days until i have accumulated enough time to complete the task.
My Question can something like this be done with CTE, or is this something that should be handled by a cursor. Or... am i just going about this the wrong way completely??