Presence Report with Sql Query using Left Join Function

In every company, school, and etc, presence is very vital. becuase with it, we can count salary or value to one person.

now, how we can create a presence report with sql query?
I create some simple query example based on presence on company.

In database, we have tables like: Employee, Employee_Absence. here, the idea is joining 2 table based on NIK (employee ID).


The condition we gave in here is based on year and month. we can use inner join statement on this query, because inner join used if the 2 table have a same ID.

in this situation, we use left join, where sql will execute joining 2 table but based on the first table. example: if in Employee table we have 200 employees, and Employee_Absence we have 150 employees. the result will be shown by sql are 200 employees with 50 employees will be null.

here the simple code of the query:

select a.*, d.*,
from Employee a left join Employee_Absence d on a.nik = d.nik and day(a.date) = day(d.date) and month(a.date) = month(d.date)
and year(a.date) = year(d.tanggal)
where year(a.date) = @year and month(a.date) = @month
order by a.nik, a.tanggal

enjoy it...