Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to set start date to rowNum - 1

Posted on 2013-12-26
3
Medium Priority
?
343 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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. . .
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

705 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