Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need to set start date to rowNum - 1

Posted on 2013-12-26
3
Medium Priority
?
345 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
3 Comments
 
LVL 8

Accepted Solution

by:
gpizzuto earned 1500 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

783 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