Solved

trying to set a different value for column using case statement

Posted on 2013-12-29
1
370 Views
Last Modified: 2013-12-29
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
Comment
Question by:portlight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
portlight earned 0 total points
ID: 39745094
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question