Is this possible with Sql 2005 CTE?

Question

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??

Answer 1

It is possible but slow to use a Common Table Expression to calculate a running total. This is one of the few cases where a cursor performs better.

Answer 2

would something like this work?

;WITH CALENDAR_WITH_INDEX(CALENDAR_DATE, AVAILABLE_MINUTES, DATE_INDEX)
(
     SELECT
          CALENDAR_DATE,
          1440 - SCHEDULED_MINUTES,               /* convert scheduled minutes to available minutes */
          ROW_NUMBER() OVER (ORDER BY CALENDAR_DATE DESC) /* get day indexes. can't use DATE functions to get previous day (think holidays) */
     FROM
          CALENDAR
     WHERE
          LINE_ID = @LINE_ID AND
          CALENDAR_DATE < @DUEDATE                        /* use <= instead of < if you can do stuff on the scheduled date too */
),
WITH TIME_SLICES (SCHEDULED_DATE, MINUTESPENDING, SLICE_INDEX)
(
     SELECT 
          CALENDAR_DATE, 
          @DURATION - (AVAILABLE_MINUTES),                /* knocks of minutes available from our running total */
          DATE_INDEX
     FROM 
          CALENDAR_WITH_INDEX                             
     WHERE
          DATE_INDEX = 1                                  /* gets the first date usable date */

     UNION ALL

     SELECT 
          CALENDAR_DATE, 
          MINUTESPENDING - AVAILABLE_MINUTES
          DATE_INDEX
     FROM 
          CALENDAR_WITH_INDEX
          INNER JOIN TIME_SLICES 
               ON DATE_INDEX = SLICE_INDEX + 1            /* this gets us the date 1 day before */
     WHERE
          MINUTESPENDING > 0                              /* stop when we have no more minutes */
)
SELECT MIN(SCHEDULED_DATE) FROM TIME_SLICES

I guess performance is going to be bad because of the row_number, recursive part.