portlight
asked on
I need to grab an additional row based on a variable result
IF
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
when l1.completed > startDate AND a.name LIKE '%Disable%'
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 ;
ASKER
I have not used cursors before, is it difficult to convert what I have to function as you are talking of.
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.
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.
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....
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....
ASKER
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
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
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;
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;
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please see this article about cursors on MySQL: http://dev.mysql.com/doc/refman/5.0/en/cursors.html