CODEWITHSUNDEEP
mysqlsqldatabasedate
reja eja
reja eja

Reputation: 93

sql to view date attendance and leave from work

please help me to solve it.

here I want to display the details of the date on which employees enter and leave work.

this is my query is still misunderstood,

    SELECT
      *
    FROM

    (SELECT
      e.NIK,e.Name,date(d.Enroll)AS attd
    FROM 
      dvc0004 d, emp0003 e
    WHERE
      YEAR(d.Enroll)=YEAR(CURRENT_DATE())) AS subQuery1,

      (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) 
AS subQuery3,
     
     (SELECT
      a.NIK, a.Name, s.ExcDesc, b.PeriodeIn, b.PeriodeEnd
    FROM 
      emp0003 a, emp0016 b, sys0006 s, dvc0004 d
    WHERE
      a.EmployeeID=b.EmpID AND
      b.ExcId=s.ExcID AND
      a.NIK=d.NIK AND
      s.ExcPay='T' AND YEAR(b.PeriodeIn)=YEAR(CURRENT_DATE())) AS subQuery2
    
      WHERE date.selected_date BETWEEN queryA.PeriodeIn AND queryA.PeriodeEnd 
      AND subQuery1.NIK=subQuery2.NIK

in subquery 1: displays the date the employee entered.
in subquery 2: displays the period the employee had been off from work.
in subquery 3: displays all dates according to the date range.


the desired query result :

NIK Name Date Presence
012 Rey 01-01-2021 Annual leave
012 Rey 02-01-2021 Attend
012 Rey 03-01-2021 Sick
012 Rey 04-01-2021 Truant
012 Rey 05-01-2021 Attend
011 Yoan 01-01-2021 Attend
011 Yoan 02-01-2021 Attend
011 Yoan 03-01-2021 Annual leave
011 Yoan 04-01-2021 Annual leave
011 Yoan 05-01-2021 Annual leave




this is a Sample Data on fiddleDB: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=57fe8cb377bbd54856abdad32d0d9cec

Upvotes: 0

Views: 555

Answers (1)

nachospiu
nachospiu

Reputation: 2039

I don't understand completely what you want.

"The desired query result table" is not consistent with de data in dbfiddle.

Check this query and let me know what is wrong or missing on it (MariaDB, MySql 8.0):

SELECT *
FROM

(WITH recursive Date_Ranges AS (
   select EmpID, ExcId, PeriodeIn as PeriodeIn, PeriodeEnd  FROM emp0016
   union all
   select EmpID, ExcId, PeriodeIn + interval 1 day, PeriodeEnd
   from Date_Ranges
   where PeriodeIn < PeriodeEnd)
   
SELECT t3.NIK, t3.Name, t1.PeriodeIn AS `date`, t2.ExcDesc AS Presence
FROM emp0003 t3
INNER JOIN Date_Ranges t1 ON t1.EmpID = t3.EmployeeID
INNER JOIN sys0006 t2 ON t2.ExcID = t1.ExcId
WHERE t2.IsActive = 'T'

UNION ALL

SELECT t3.NIK, t3.Name, DATE(t4.Enroll) AS `date`, 'what presence?' AS Presence
FROM emp0003 t3
INNER JOIN dvc0004 t4 ON t3.NIK = t4.NIK
) AS aux

ORDER BY NIK, `date`;

output whit the example data in dbfiddle:

NIK Name date Presence
1 Rey 2021-01-01 Annual Leave
1 Rey 2021-01-02 Truant
1 Rey 2021-01-03 what presence?
1 Rey 2021-01-04 what presence?
1 Rey 2021-01-05 what presence?
1 Rey 2021-01-06 what presence?
2 Yoan 2021-01-01 what presence?
2 Yoan 2021-01-02 Annual Leave
2 Yoan 2021-01-03 Annual Leave
2 Yoan 2021-01-04 Annual Leave
2 Yoan 2021-01-05 what presence?
2 Yoan 2021-01-06 what presence?

Upvotes: 2

Related Questions