Solved

Need to set start date to rowNum - 1

Posted on 2013-12-26
3
342 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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