Solved

Need to set start date to rowNum - 1

Posted on 2013-12-26
3
340 Views
Last Modified: 2013-12-28
I have a stored procedure that I need to check the first row of data and if column1(coolHours) is null then I need to set the startDate to the same time as column2(completed) in the previous row.

SO in the image below I need to set startDate in the query to the completed value of row10 (rowNum-1)
 

The query that I am using is here - I have tried several different ideas and none have worked so far!
    BEGIN
      DECLARE startDate VARCHAR (50);
      DECLARE endDate   VARCHAR (50);
      DECLARE mylogID   INT;
      DECLARE myItemId varchar (50);
      DECLARE myItemId2 varchar (50);
      DECLARE xHours varchar (50);
    
      SET startDate = FROM_UNIXTIME(fromDate/1000);
      SET endDate   = FROM_UNIXTIME(toDate/1000);
      SET mylogID   = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
      SET myItemID = (SELECT i.itemId FROM items i  WHERE i.name LIKE '%KW PRE%');
      SET myItemID2 = (SELECT i.itemId FROM items i  WHERE  i.name LIKE '%KW STA%');
    
      CALL reportCreateFreeCoolingTempTable(fromDate,toDate,'UTC');
         
      SET @sql = NULL;
      SET @sql = CONCAT(
      'SELECT 
        rowNum,l.logId,l.completed,l.next_completed,isError,charge,totalTimeRange,startingDate,endingDate,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, '''
      ORDER BY completed');   
    
    PREPARE stmt FROM @sql;
     EXECUTE stmt;
     DROP TEMPORARY TABLE tempTable3;
     DEALLOCATE PREPARE stmt;

Open in new window

topRow.jpg
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
3 Comments
 
LVL 8

Accepted Solution

by:
gpizzuto earned 500 total points
ID: 39741451
Use Coalesce function.
I.e.
SELECT coalesce(coolingHours,completed) FROM ...
0
 

Author Comment

by:portlight
ID: 39741552
I do not think this is going to work exactly they way I need it because the FIRST row is not always going to be null.

I only need to change the values if the first returned row is null.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39742136
Can you provide some sample data from your tables and expected result
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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