How to make SANDIWCH leave query in MySql?

Question

Many of you might aware of sandwich leave. But how to implement at database level its bit difficult for me. I have scenerio where if employees took 2 LEAVE i.e. before and after of their WEEK-OFF then all the 3 days should marked as LEAVE. Below is the basic requirement I am sharing.

HERE IS REQUIREMENT OF SANDWICH LEAVE

Here is some sample data. It may eventually occur to me to supplement this with a corresponding desired result.

CREATE TABLE IF NOT EXISTS `atnsystem` (
  `Emp_id` int unsigned NULL,
  `attendance_date` date NULL,
  `in_datetime` datetime NULL,
  `out_datetime` datetime NULL,
  `remark` varchar(100) NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `atnsystem` (`Emp_id`, `attendance_date`, `remark`) VALUES
  ('66', '2020-02-17', 'LEAVE'),
  ('66', '2020-02-16', 'WEEK-OFF'),
  ('66', '2020-02-15', 'LEAVE');

This is the SQL_FIDDLE link table created in fiddler

I want if remark is like LEAVE , WEEK-OFF and LEAVE again then that WEEK-OFF should get CONVERTED to LEAVE as well.

It doesn't matter what get inserted but while using SELECT query if it found YESTERDAY as LEAVE and TODAY as WEEK-OFF and then again next day as LEAVE then the WEEK-OFF day should get considered as LEAVE as well. I hope I made my query clear and any help will be highly appreciated.

It would be nicer to spend the time revising the company's leave policy. — Strawberry, Feb 29 '20 at 07:03
@Strawberry, your suggestion seems nice but I need an solution on sandwich leave. Also, I am trying my best to find the solution. If you have solution then please share. — OTP Generator, Feb 29 '20 at 07:10
@Strawberry, I understood I even share the dummy data produced my me at lastline. I think that is enough to understand what i want. And from what i am struggling with. I also shared the Id details that is of 6, 7, 8, & 10. One can compare and understand easily. — OTP Generator, Feb 29 '20 at 07:22
@Strawberry, Please suggest what else do one need to understand my query. I'll elaborate accordingly. — OTP Generator, Feb 29 '20 at 07:24
@Strawberry, See now I added the SQL-FIDDLe link as well. Please check is possible to understand my question. — OTP Generator, Feb 29 '20 at 08:23
@OTPGenerator You might need to build complicated queries like on https://stackoverflow.com/questions/8812696/increasing-mysql-query-performance-math-heavy-query/8813471#8813471 to get the values from the "previous" row to decide if you want to replace the remark to "LEAVE". Is it an option to do what you are trying to do in a programming language instead? — Progman, Feb 29 '20 at 09:52
What would the remark be if the emp is NOT on leave or week off? — P.Salmon, Feb 29 '20 at 10:11
@P.Salmon, Then it will be either Present, half-day, late mark. — OTP Generator, Feb 29 '20 at 12:31

score 1 · Accepted Answer · answered Mar 01 '20 at 07:50

An approach to this would be to transform everything that is not week-off so that we can assign a number (bloc in the query below) for every block of week-off. After that we can know the min and max date in each block and can use a simple sub query in the main query to establish if the remark immediately before and immediately after the min and max date is leave. for example

+--------+-----------------+-------------+--------------+----------+
| Emp_id | attendance_date | in_datetime | out_datetime | remark   |
+--------+-----------------+-------------+--------------+----------+
|     66 | 2020-02-29      | NULL        | NULL         | week-off |
|     66 | 2020-02-28      | NULL        | NULL         | NULL     |
|     66 | 2020-02-27      | NULL        | NULL         | leave    |
|     66 | 2020-02-26      | NULL        | NULL         | week-off |
|     66 | 2020-02-25      | NULL        | NULL         | week-off |
|     66 | 2020-02-24      | NULL        | NULL         | NULL     |
|     66 | 2020-02-23      | NULL        | NULL         | leave    |
|     66 | 2020-02-22      | NULL        | NULL         | week-off |
|     66 | 2020-02-21      | NULL        | NULL         | week-off |
|     66 | 2020-02-20      | NULL        | NULL         | leave    |
|     66 | 2020-02-19      | NULL        | NULL         | NULL     |
|     66 | 2020-02-18      | NULL        | NULL         | leave    |
|     66 | 2020-02-17      | NULL        | NULL         | NULL     |
|     66 | 2020-02-16      | NULL        | NULL         | WEEK-OFF |
|     66 | 2020-02-15      | NULL        | NULL         | LEAVE    |
|     66 | 2020-02-14      | NULL        | NULL         | leave    |
+--------+-----------------+-------------+--------------+----------+
16 rows in set (0.00 sec)


select t.*, b.*,
         (select t1.remark 
         from t t1
         where t1.emp_id = t.emp_id and
                t1.attendance_date < b.mindt 
         order by t1.attendance_date desc limit 1) previous_remark,
         (select t1.remark 
         from t t1
         where t1.emp_id = t.emp_id and
                t1.attendance_date > b.maxdt 
         order by t1.attendance_date asc limit 1) next_remark   ,
         case
          when 
            (select t1.remark 
            from t t1
            where t1.emp_id = t.emp_id and
                t1.attendance_date < b.mindt 
            order by t1.attendance_date desc limit 1) = 'leave'
          AND
            (select t1.remark 
            from t t1
            where t1.emp_id = t.emp_id and
                t1.attendance_date > b.maxdt 
            order by t1.attendance_date asc limit 1) ='leave' THEN
             'leave'
          ELSE t.remark 
          END as final_remark    
from t
left join
(
select a.emp_id,a.bloc,min(a.attendance_date) mindt,max(a.attendance_date) maxdt
from
(
select s.*,
        if(newremark = 'p',0,If(newremark <> @p,@b:=@b+1,@b:=@b)) bloc,
        @p:=newremark p
from
(
select t.*,
        case when remark = 'week-off' then remark else 'p' end as newremark
from t
order by attendance_date asc
) s
cross join (select@b:=0,@p:='') b
order by attendance_date asc
) a
where bloc > 0
group by a.emp_id, a.bloc
) b
on b.emp_id = t.emp_id and t.attendance_date between b.mindt and b.maxdt
order by t.emp_id,t.attendance_date;

+--------+-----------------+-------------+--------------+----------+--------+------+------------+------------+-----------------+-------------+--------------+
| Emp_id | attendance_date | in_datetime | out_datetime | remark   | emp_id | bloc | mindt      | maxdt      | previous_remark | next_remark | final_remark |
+--------+-----------------+-------------+--------------+----------+--------+------+------------+------------+-----------------+-------------+--------------+
|     66 | 2020-02-14      | NULL        | NULL         | leave    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | leave        |
|     66 | 2020-02-15      | NULL        | NULL         | LEAVE    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | LEAVE        |
|     66 | 2020-02-16      | NULL        | NULL         | WEEK-OFF |     66 | 1    | 2020-02-16 | 2020-02-16 | LEAVE           | NULL        | WEEK-OFF     |
|     66 | 2020-02-17      | NULL        | NULL         | NULL     |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | NULL         |
|     66 | 2020-02-18      | NULL        | NULL         | leave    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | leave        |
|     66 | 2020-02-19      | NULL        | NULL         | NULL     |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | NULL         |
|     66 | 2020-02-20      | NULL        | NULL         | leave    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | leave        |
|     66 | 2020-02-21      | NULL        | NULL         | week-off |     66 | 2    | 2020-02-21 | 2020-02-22 | leave           | leave       | leave        |
|     66 | 2020-02-22      | NULL        | NULL         | week-off |     66 | 2    | 2020-02-21 | 2020-02-22 | leave           | leave       | leave        |
|     66 | 2020-02-23      | NULL        | NULL         | leave    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | leave        |
|     66 | 2020-02-24      | NULL        | NULL         | NULL     |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | NULL         |
|     66 | 2020-02-25      | NULL        | NULL         | week-off |     66 | 3    | 2020-02-25 | 2020-02-26 | NULL            | leave       | week-off     |
|     66 | 2020-02-26      | NULL        | NULL         | week-off |     66 | 3    | 2020-02-25 | 2020-02-26 | NULL            | leave       | week-off     |
|     66 | 2020-02-27      | NULL        | NULL         | leave    |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | leave        |
|     66 | 2020-02-28      | NULL        | NULL         | NULL     |   NULL | NULL | NULL       | NULL       | NULL            | NULL        | NULL         |
|     66 | 2020-02-29      | NULL        | NULL         | week-off |     66 | 4    | 2020-02-29 | 2020-02-29 | NULL            | NULL        | week-off     |
+--------+-----------------+-------------+--------------+----------+--------+------+------------+------------+-----------------+-------------+--------------+
16 rows in set (0.16 sec)

Where t is the table name in my DB.

NOTE your data has to be clean and sort of assumes an entry for every employee for every day. I have included more columns than you require in your output so that you can see what's happening.

Hi @P.Salmon, this query is working perfect for single employees, but taking lots of time for more than 150 employees when data fetching for 1 month — OTP Generator, Sep 03 '20 at 09:53

Akshay Shinde · Answer 2 · 2022-09-22T14:20:56.073

I try to solve the problem by creating a function and used same in code.

Function -

ALTER FUNCTION [UDF]
(
@eid int
,@date1 date
,@number int    
)
RETURNS  VARCHAR(50)
AS
BEGIN
DECLARE  @status VARCHAR(50)
select @status =status from table1
where eid=@eid and  date= dateadd(day,@add_number, @date1)
return @status 
END

Use this function in your code. I write the code considering Saturday and Sunday as week off.

Note :- Consider making changes in your code accordingly.

select eid, status , date,
case 
when DATENAME(weekday, date) = 'Saturday'
and UDF(eid, date, -1) = 'Absent'
and UDF(eid, date, 2) = 'Absent' then 'Absent'
when DATENAME(weekday, date) = 'Sunday' 
and UDF(eid, date, -2) = 'Absent'
and UDF(eid, date, 1)  = 'Absent' then 'Absent'
else null
end as new_status
from table1

My table name here is table1. Flag here represent - Present or Absent of employee for that day.

This code worked for multiple employees for multiple days.

How to make SANDIWCH leave query in MySql?

2 Answers2

Linked