[Webinar] Streamline your web hosting managementRegister Today

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

Need to set start date to rowNum - 1

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
portlight
Asked:
portlight
1 Solution
 
gpizzutoCommented:
Use Coalesce function.
I.e.
SELECT coalesce(coolingHours,completed) FROM ...
0
 
portlightAuthor Commented:
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
 
SharathData EngineerCommented:
Can you provide some sample data from your tables and expected result
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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