42 lines
759 B
SQL
42 lines
759 B
SQL
|
|
USE GWMS_PZZFRR;
|
|
DROP PROCEDURE IF EXISTS DeletePlantLogrecords;
|
|
|
|
|
|
DELIMITER $$
|
|
|
|
CREATE PROCEDURE DeletePlantLogrecords(
|
|
IN pStartDate DATETIME,
|
|
IN pPlantId INT,
|
|
IN pMinInt INT,
|
|
IN pFluxType VARCHAR(250)
|
|
)
|
|
BEGIN
|
|
|
|
# calcolo il primo record dalla data indicata
|
|
DECLARE pDt DATETIME DEFAULT pStartDate;
|
|
|
|
SET pDt=
|
|
(
|
|
SELECT DtEvent
|
|
FROM PlantLog
|
|
WHERE PlantId = pPlantId
|
|
AND FluxType = pFluxType
|
|
AND DtEvent > pStartDate
|
|
ORDER BY DtEvent
|
|
LIMIT 1
|
|
) + INTERVAL 10 SECOND;
|
|
|
|
#SELECT pDt;
|
|
|
|
# calcolo TUTTI i record successivi (da eliminare)
|
|
DELETE
|
|
FROM PlantLog
|
|
WHERE PlantId = pPlantId
|
|
AND FluxType = pFluxType
|
|
AND DtEvent > pDt AND DtEvent < (pStartDate + INTERVAL pMinInt MINUTE)
|
|
;
|
|
|
|
END$$
|
|
|
|
DELIMITER ; |