asked on Set startDate to row above's time based on case statement
I am trying to check to see if the value for column 'complete' of is less than the startDate and if it is then I need to set the startDate to that value so the row is included in the return set....
]
In looking at the data set here:
Table and code testing
I have several things not working as I would like
1. row 12 isError should = 1 as row 12 and row 13 are both actiivtyID 1257
2. if row 10 is the FIRST row returned in a select statement (using a where date between start and end) I need to set the start date to row 10's completed value
]
In looking at the data set here:
Table and code testing
I have several things not working as I would like
1. row 12 isError should = 1 as row 12 and row 13 are both actiivtyID 1257
2. if row 10 is the FIRST row returned in a select statement (using a where date between start and end) I need to set the start date to row 10's completed value
MySQL Server
Last Comment
portlight
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
you are correct that they are out of order - and for this project - that is okay!
(wrong as it may be!) they are able to go back and edit record times which put them out of wack -
(wrong as it may be!) they are able to go back and edit record times which put them out of wack -
Ok, but that explains why there's no value in isError - going by record ids, rather than rowNums, there are not two consecutive rows with the same activity id.
ASKER
I actually fixed this by creating a temp table that picked up row Numbers based on times and not the actual recordId
Then called this temp table in stored procedure that returned rows I needed
DECLARE startDate VARCHAR (50);
DECLARE endDate VARCHAR (50);
DECLARE mylogID INT;
DECLARE myItemId varchar (50);
DECLARE myItemId2 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%');
SET @sql = NULL;
SET @sql = CONCAT(
'CREATE TEMPORARY TABLE tempTable3 AS (SELECT
@row := @row + 1 AS rownum,
a.logId,
L1.recordId,
L2.recordId AS next_recordId,
L1.completed,
L2.completed AS next_completed,
L1.activityId AS activityJoin,
L2.activityId AS next_activityId,
IF(L1.activityId = L2.activityId, 1, NULL) AS isError,
CASE
when a.name LIKE ''%Enable%'' THEN time_to_sec(timediff(L2.completed, L1.completed)) / 3600
ELSE NULL
END AS coolingHours,
TO_SECONDS(L2.completed) - TO_SECONDS(L1.completed) AS timeInSeconds,
((SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS charge,
time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
CONVERT_TZ(''', startDate, ''', ''UTC'', ''US/Pacific'') AS startingDate,
CONVERT_TZ(''', endDate, ''', ''UTC'', ''US/Pacific'') AS endingDate,
((L1.item', myItemID, ' - L1.item', myItemID2, ') *
(TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)))) / 3600 AS kwDifference,
((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS cost,
((((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details,'':'') + 1)))
* (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600))) AS costT,
DATABASE() AS databaseName, i.itemId ,l.details
FROM
(SELECT @row:=0)R,
(SELECT T1.completed,
(SELECT MIN(completed)
FROM log',mylogID, '
WHERE completed > T1.completed) AS next_completed
FROM log',mylogID, ' T1
ORDER BY T1.completed
)TimeOrder
LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
LEFT JOIN activities a ON L1.activityId = a.activityId
LEFT JOIN logs l ON a.logId = l.logId
inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%''
)ORDER BY L1.completed');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
Then called this temp table in stored procedure that returned rows I needed
BEGIN
DECLARE startDate VARCHAR (50);
DECLARE endDate VARCHAR (50);
DECLARE mylogID INT;
DECLARE myItemId varchar (50);
DECLARE myItemId2 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;
2. I don't understand
for complete being less than startdate and setting to startdate just use a case statement for that column:
(case when complete < startdate then startdate else complete end) as complete