need a little help with a query or script that can fill the gaps shown in the image below.
Looking at the image, the gap should be filled where the blue ends and the red line starts, where the red line ends and the green line starts, etc.
The query getting the result from my database looks like this and is called using a stored procedure.
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT 'IF(cycle = ', cycle, ', ton_to_kn(tension_current_value), NULL) AS ', CONCAT('''Cycle ', cycle, '\'') ) INTO @sql FROM machine_data WHERE test_id = test; SET @sql = CONCAT('SELECT DISTINCT md.cylinder_current_position - t.offset AS Position, ', @sql, ' FROM machine_data md JOIN test t ON t.id = md.test_id AND md.test_id = ', test); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT @sql;
The query generates a dynamic pivot table with each cycle of a test as a column.
The gap comes from generating the pivot table from historical data, see image.
I tried writing a script that will fill the gap by setting the last ‘None’ value of each cycle to the first value of the next. My solutions seems to be functional but not very lightweight.
Any help would be appreciated I can alter the query if that is the best way to do this.