Solved

I need to grab an additional row based on a variable result

Posted on 2013-12-19
8
279 Views
Last Modified: 2013-12-22
IF

when l1.completed > startDate AND a.name LIKE '%Disable%'  

Open in new window


true of the first row in the result set, I need to grab the previous row in the database and include it in the result set

CREATE PROCEDURE reportFreeCoolingTrackerDateTest (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            
             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
             
            
SELECT 
    CASE
        when l1.completed > startDate AND a.name LIKE '%Disable%' 
            THEN (time_to_sec(timediff(l1.completed,(CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) ))) / 3600)
       END AS newTime,l1.recordId,
    a.activityId, 
    DATABASE() AS databaseName, ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600))  AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,l1.completed, 
     (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS StartingDate, 
     (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS EndingDate,   
      timeZone  AS timeZonePassed

    
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;
 
    END //

DELIMITER ;

Open in new window

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
  • 5
  • 3
8 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39731250
You should create a cursor to access to the rows of the select. By creating a cursor you can process rows one by one: save the current row to a variable, maintaining the previous row on another variable (so you will need two row variables).

Please see this article about cursors on MySQL: http://dev.mysql.com/doc/refman/5.0/en/cursors.html
0
 

Author Comment

by:portlight
ID: 39731266
I have not used cursors before, is it difficult to convert what I have to function as you are talking of.
0
 
LVL 15

Expert Comment

by:gplana
ID: 39731281
Have you read the article? I think it shouldn't be difficult.

I would do it for you but the problem is I don't really understand what exactly you want. You said: "if (this) true of the first row in the result set, I need to grab the previous row in the database and include it in the result set ". Never exist a previous row of first row.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 15

Expert Comment

by:gplana
ID: 39731288
Also you can get first row just by adding a "TOP 1" just after SELECT word. This will make the SELECT to return just the first record instead of all records.

Then, you can also use variables to assign the value of columns when the SELECT statement returns exactly one row:

SELECT @myvar1=mycolumn1, @myvar2=mycolumn2, ...., @myvarN = mycolumnN
FROM bla bla....
0
 

Author Comment

by:portlight
ID: 39732096
If the initial query grabs 10 of 100 rows - the first row of the result set being row 24 AND that first row meets this condition
when l1.completed > startDate AND a.name LIKE '%Disable%'  

Then I need to figure out how to include row 23 in the result set. Even if the started time is outside of the between dates
0
 
LVL 15

Expert Comment

by:gplana
ID: 39732566
Then I think you should do this:

store the recordId of the found record on a variable (for example v_id).

then make this query:

SELECT @v_previous_id = MAX(recordId)
FROM logl644
WHERE recordId < @v_id;

Then you can get the previous record by issuing this sentence:

SELECT *
FROM your_tables_and_join
WHERE ll.recordId = @v_previous_id;
0
 

Author Comment

by:portlight
ID: 39732594
The problem that i have been running into is this creates two return data sets when the query has been run - is there any way to incorporate this into one return set.
0
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
ID: 39732662
You can create a dynamic sql, i.e. create programmatically the SQL string (by adding the additional SELECTS with the clause UNION) and execute this string.

Please look at this article for more information about dynamic SQL.

http://technet.microsoft.com/en-us/library/ms188001.aspx

Also please look this article for how to use UNION:

http://technet.microsoft.com/es-es/library/ms180026.aspx

Hope it helps. Regards.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 57
Incorrect definition of table mysql.proc 7 87
MySql Recovery 2 34
Generate PDF from MySQL using PHP 3 53
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 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