How to create procedure in mysql

I am creating a procedure in mysql but I am facing issues with the required result and now I am in a total mess. Please help me!

The requirements is this:

Get the Top 20 entries from the table, for the current day. By Top 20 entries, they should be the ones that occurred most often during the day – if possible. – If the error found occurred before in the last 30 days, show the count it appeared for the previous 30 days, as well as the date in which the error first appeared. For example – app 1 had an error with a message saying “error on connect” which occurred today. However, it also had this error occur in the past 30 days, occurring over 5 times. The count should show as 6 (current occurrence and previous 5 occurrences). The date the error first appeared should show up as well. The output should look as follows – by column name: – App name – Error message – Count – How many times the error message appeared in the last 30 days, for the unique key of “app name” and “error message” – Date – Date this error first appeared, for the unique key of “app name” and “error message”

My table looks like this:

Table Picture

So far I have created the following procedure:

CREATE PROCEDURE ExpectedResult()
BEGIN
    SELECT log_id, app_name, error_message, error_date, count(error_message)
    FROM app_log WHERE error_date < '2021-03-14' -- CURDATE()
    group by app_name, error_message;
END$$
DELIMITER ;

Answer

it takes much more time to rebuild your data, as to program a simole sql query

CREATE TABLE app_log (
  `LOG_ID` INTEGER,
  `APP_NAME` VARCHAR(5),
  `ERROR-MESSAGE` VARCHAR(34),
  `ERROR-DATE` VARCHAR(10)
);

INSERT INTO app_log
  (`LOG_ID`, `APP_NAME`, `ERROR-MESSAGE`, `ERROR-DATE`)
VALUES
  ('1', 'APP_1', 'UNABLE TO CONNECT -  101', '2021-03-14'),
  ('2', 'APP_1', 'UNABLE TO CONNECT - 101', '2021-03-01'),
  ('3', 'APP_1', 'UNABLE TO CONNECT - 101', '2021-01-01'),
  ('4', 'APP_1', 'UNABLE TO CONNECT - 102', '2021-03-14'),
  ('5', 'APP_1', 'UNABLE TO CONNECT - 102', '2021-01-01'),
  ('6', 'APP_1', 'UNABLE TO CONNECT - SERVER ERROR', '2021-03-14'),
  ('7', 'APP_1', 'UNABLE TOCONNECT -102', '2020-01-01'),
  ('8', 'APP_2', 'SERVER ERROR 1', '2021-03-14'),
  ('9', 'APP_2', 'SERVER ERROR 1', '2021-03-01'),
  ('10', 'APP_2', 'SERVER ERROR 1', '2021-01-01'),
  ('11', 'APP_2', 'SERVER ERROR 2', '2021-03-14'),
  ('12', 'APP_2', 'SERVER ERROR 2', '2020-01-01'),
  ('13', 'APP_2', 'PROXY ERROR', '2021-03-14'),
  ('14', 'APP_3', 'SERVER UNAVAILABLE', '2021-03-14'),
  ('15', 'APP_3', 'SERVER UNAVAILABLE', '2021-03-01'),
  ('16', 'APP_3', 'SERVER UNAVAILABLE', '2021-01-01');
    SELECT MAX(LOG_ID), APP_NAME, `ERROR-MESSAGE`, MAX(`ERROR-DATE`), count(`ERROR-MESSAGE`) count_messages
    FROM app_log WHERE `ERROR-DATE` >=   CURDATE()  - iNTERVAL 30 daY
    group by APP_NAME, `ERROR-MESSAGE`
    ORDEr BY  count_messages DESC
    LIMIT 20;
MAX(LOG_ID) | APP_NAME | ERROR-MESSAGE                    | MAX(`ERROR-DATE`) | count_messages
----------: | :------- | :------------------------------- | :---------------- | -------------:
          9 | APP_2    | SERVER ERROR 1                   | 2021-03-14        |              2
         15 | APP_3    | SERVER UNAVAILABLE               | 2021-03-14        |              2
          1 | APP_1    | UNABLE TO CONNECT -  101         | 2021-03-14        |              1
          2 | APP_1    | UNABLE TO CONNECT - 101          | 2021-03-01        |              1
          4 | APP_1    | UNABLE TO CONNECT - 102          | 2021-03-14        |              1
          6 | APP_1    | UNABLE TO CONNECT - SERVER ERROR | 2021-03-14        |              1
         11 | APP_2    | SERVER ERROR 2                   | 2021-03-14        |              1
         13 | APP_2    | PROXY ERROR                      | 2021-03-14        |              1

db<>fiddle here

Leave a Reply

Your email address will not be published. Required fields are marked *