select min(date) for set of values-CodePudding

How to select the min(date) for set of values like this table:

    date               status       failure_time
-------------------------------------------------
 2022-03-22 17:58:03     1             NULL
 2022-03-22 18:00:03     0             NULL
 2022-03-22 18:02:03     0             NULL
 2022-03-22 18:04:03     1              6
 2022-03-22 18:06:03     1             NULL
 2022-03-22 18:08:03     0             NULL
 2022-03-22 18:10:03     1              2

My try using this trigger :

    DECLARE LAST_STATUS integer;
    DECLARE LAST_DATE datetime;
    SET @LAST_STATUS := (SELECT `status` from crm ORDER by id DESC limit 1);
    SET @LAST_DATE   := (SELECT `date` from crm ORDER by id DESC limit 1);

    IF (NEW.status = 1 and  @LAST_STATUS = 0 ) THEN
        SET NEW.`failure_time` := TIMESTAMPDIFF(MINUTE, @LAST_DATE , NEW.date) ;
    END IF;

Gave a result like:

    date               status       failure_time
-------------------------------------------------
 2022-03-22 17:58:03     1             NULL
 2022-03-22 18:00:03     0             NULL
 2022-03-22 18:02:03     0             NULL
 2022-03-22 18:04:03     1              2
 2022-03-22 18:06:03     1             NULL
 2022-03-22 18:08:03     0             NULL
 2022-03-22 18:10:03     1              2

My guess is to create a table which works as a flag and a trigger, when there is new 'zero' I will insert the date inside that table, but I don't need that work around.

I know that I need to get the date of first 'zero' before 'one' but I don't know how.

CodePudding user response：

In MySQL, variables you DECLARE have no @ sigil, and they are local variables scoped to the trigger or procedure you declare them in. Variables with the @ sigil you don't have to declare (in fact it's an error if you try), and they have are scoped to the session. So they're completely different. Many people get confused with MySQL because in some other SQL products (ahem Microsoft), the syntax of variables is different.

I'd also recommend using INTO syntax, so you don't have to do the SELECT query twice.

DECLARE LAST_STATUS INT;
DECLARE LAST_DATE DATETIME;

SELECT status, date INTO LAST_STATUS, LAST_DATE 
FROM crm ORDER BY id DESC LIMIT 1;

IF (NEW.status = 1 and  LAST_STATUS = 0 ) THEN
    SET NEW.`failure_time` = TIMESTAMPDIFF(MINUTE, LAST_DATE , NEW.date) ;
END IF;

The := operator should be written as = when you use it in a SET statement. The := version is used when you want to do an assignment as a side-effect within an expression.

CodePudding user response：

I got the result by reading again and again my question,

DECLARE LAST_STATUS         INT;
DECLARE LAST_ONE_ID         INT;
DECLARE FIRST_ZERO_DATE     DATETIME;

select max(id) INTO LAST_ONE_ID from crm where status = 1 order by date desc;
select status INTO LAST_STATUS FROM crm ORDER BY id DESC LIMIT 1;
SELECT `date` INTO FIRST_ZERO_DATE from crm where id = ( LAST_ONE_ID   1 ) ORDER by id DESC limit 1;

IF (NEW.status = 1 and  LAST_STATUS = 0 ) THEN
    SET NEW.`failure_time` := TIMESTAMPDIFF(SECOND, FIRST_ZERO_DATE , NEW.date) ;
END IF;

I use second for test, here's the result:

thanks to @Bill Karwin and @barmar for your help.

also I think that there is some improvement to the previous query.