OVER PARTITION BY in MYSQL 5.1? (Without Window Functions)

Question

I have been doing SQL and I am stuck in one point. For example, lets say we have a Table Employee with columns Emp_ID(varchar), DELIVERY_TIME(date), Delivery_number(Varchar)

EMP_ID	DELIVERY_TIME	DELIVERY_NUMBER
E01	21-08-2021	4
E01	null	1
E01	21-08-2022	5
E01	21-05-2021	2
E01	07-08-2021	3
E02	21-08-2021	4
E02	21-08-2023	5
E02	21-08-2020	1
E02	21-06-2021	2
E02	06-08-2021	3

Now I want to manipulate the table by adding one Status column in this by some query, like:

EMP_ID	DELIVERY_TIME	STATUS
E01	21-08-2021	IN FUTURE
E01	null	PENDING
E01	21-08-2022	IN FUTURE
E01	21-05-2021	DELIVERED
E01	07-08-2021	DELIVERING
E02	26-08-2021	IN FUTURE
E02	21-08-2023	IN FUTURE
E02	21-08-2020	DELIVERED
E02	21-06-2021	DELIVERED
E02	06-08-2021	DELIVERING

Basically, if todays date is 8th September 2021 (08-08-2021) then,

• for dates > , IN - FUTURE
• for date < , delivered
• for max(date) < , delivering
• for date that are null, pending
• from all the dates that are less than current date, the max(latest) of them should be marked as delivering. (Only one will be there). For example, if todays date is 08-08-2021 and in the database we have dates such 07-08-2021, 06-08-2021 and 05-08-2021, then 07-08-2021 should be marked as delivering but rest two should be marked as delivered.

But this is done for every employee ID separately. Also, if this can be done using only query and no script, that will more preferable. We can also add new EMP_IDs.

Please guide. Thank you.

I have tried using Over Partition by, but it is not working.

select 
emp_id,
delivery_time,
case 
   when max_delivery_time_when_not_in_future = delivery_time 
   then 'delivering'  else status end as final_status
from 
(select 
*,
max(case when status not in ('In future') then delivery_time else null end) 
    over(partition by emp_id order by delivery_time desc) as max_delivery_time_when_not_in_future (

select 
*,
case when when curdate() < delivery_time then 'In future'
     when  curdate() > delivery_time then 'delivered'
else 'not categorized'
END as status
from [table name]
) date_1) max_date      

EDIT: Create table Employee(Emp_ID varchar(48), DELIVERY_TIME date, Delivery_number varchar(48))

INSERT INTO EMPLOYEE VALUES('E01' , '2021-08-21' , '4');
INSERT INTO EMPLOYEE VALUES('E01' , '2019-08-21' , '1');
INSERT INTO EMPLOYEE VALUES('E01' , '2022-08-21' , '5');
INSERT INTO EMPLOYEE VALUES('E01' , '2021-05-21' , '2');
INSERT INTO EMPLOYEE VALUES('E01' , '2021-08-07' , '3');
INSERT INTO EMPLOYEE VALUES('E02' , '2021-08-21' , '4');
INSERT INTO EMPLOYEE VALUES('E02' , '2023-08-21' , '5');
INSERT INTO EMPLOYEE VALUES('E02' , '2020-08-21' , '1');
INSERT INTO EMPLOYEE VALUES('E02' , '2021-06-21' , '2');
INSERT INTO EMPLOYEE VALUES('E02' , '2021-08-06' , '3');

Answer 1

It seems that you need in this:

SELECT emp_id, 
       delivery_time, 
       t1.delivery_number,
       CASE WHEN delivery_time > @today
            THEN 'IN FUTURE'
            WHEN t3.emp_id IS NOT NULL
            THEN 'DELIVERING'
            ELSE 'DELIVERED' 
            END status
FROM employee t1
LEFT JOIN ( SELECT emp_id, MAX(delivery_time) delivery_time
            FROM employee t2
            WHERE delivery_time < @today
            GROUP BY emp_id ) t3 USING (emp_id, delivery_time)
ORDER BY 1,2;

fiddle

---

please see the edit also. – Hardik Somani

SELECT emp_id, 
       delivery_time, 
       t1.delivery_number,
       CASE WHEN delivery_time IS NULL
            THEN 'PENDING'
            WHEN delivery_time > @today
            THEN 'IN FUTURE'
            WHEN t3.emp_id IS NOT NULL
            THEN 'DELIVERING'
            ELSE 'DELIVERED' 
            END status
FROM employee t1
LEFT JOIN ( SELECT emp_id, MAX(delivery_time) delivery_time
            FROM employee t2
            WHERE delivery_time < @today
            GROUP BY emp_id ) t3 USING (emp_id, delivery_time)
ORDER BY 1,2;

fiddle