Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

trying to set a different value for column using case statement

I am trying to set the value of cooling hours on first row IF the completed date is before the startdate
 sample data

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

    SELECT   rowNum, 
       l.logId,l.completed,l.next_completed,l.first_completed,
        isError,charge,totalTimeRange,startingDate,endingDate,
         CASE
            WHEN completed < ''',startDate,''' AND rowNum = min(rowNum)
                THEN time_to_sec(timediff(l.next_completed, ''',startDate,''')) / 3600
                ELSE coolingHours
            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, '''

Open in new window


I am open to suggestions at this point!
0
portlight
Asked:
portlight
1 Solution
 
portlightAuthor Commented:
Adding the below code fixed the issue

   set @newcoolingHours = (SELECT time_to_sec(timediff(next_completed, startDateOriginal)) / 3600 AS newCoolingHours 
        FROM tempTable3 WHERE completed > startDateOriginal AND completed BETWEEN startDate AND endDate LIMIT 1);
   SET xHours = @newCoolingHours;

Open in new window


full code:
 set @newcoolingHours = (SELECT time_to_sec(timediff(next_completed, startDateOriginal)) / 3600 AS newCoolingHours 
        FROM tempTable3 WHERE completed > startDateOriginal AND completed BETWEEN startDate AND endDate LIMIT 1);
   SET xHours = @newCoolingHours;

  SET @sqlTemp = NULL;
  SET @sqlTemp = 
   CONCAT(
  'SELECT   rowNum,
   IF ((DAYOFYEAR(completed) = DAYOFYEAR(''',startDate,''') ),''',xHours,''',coolingHours)AS coolingHours,
   l.completed,DAYOFYEAR(completed)AS completedDayOfYear,
    ''',startDateOriginal,''' AS OriginalStartDate,DAYOFYEAR(''',startDateOriginal,''')AS originalDayOfYear,
    ''',startDate,''' AS newStartDate,DAYOFYEAR(''',startDate,''')AS startDateDayOfYear,
    l.next_completed,DAYOFYEAR(  l.next_completed)AS nextCompletedDayOfYear,

    isError,charge,totalTimeRange,startingDate,endingDate,
    l.logId, l.first_completed,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, '''

 '); 

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now