I am trying to set the value of cooling hours on first row IF the completed date is before the startdate
row Num completed next_completed cooling Hours
9 11/25/2013 11:21:15 PM 11/27/2013 1:05:45 AM 25.7417
10 11/27/2013 1:05:45 AM 11/29/2013 11:18:34 PM (null)
11 11/29/2013 11:18:34 PM 11/30/2013 7:37:45 PM 20.3197
12 11/30/2013 7:37:45 PM 11/30/2013 11:36:57 PM (null)
13 11/30/2013 11:36:57 PM 12/1/2013 6:52:19 PM 19.2561
in the example dates above coolingHours is = time difference between next_Completed and completed for each row
however for ROW 9 I need to set coolingHours to time difference between next_completed and startDate - all other rows stay the same
startDate is a variable passed into the storedProcedure (for this example it would be 11///2013)
I tried the code below however it returned only the min(rowNum) and it did not effect the coolingHours
WHEN completed < ''',startDate,''' AND rowNum = min(rowNum)
THEN time_to_sec(timediff(l.next_completed, ''',startDate,''')) / 3600
END AS coolingHours,
databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds
FROM tempTable3 l
inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%''
WHERE l.itemId = ''',myItemId,'''
AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
I am open to suggestions at this point!