portlight
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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