Checking Data Using Query

Sometimes we must check data to fixing 'broken' before generated to produce information. Wrong data will produce missing information, won’t it? SQL (Structured Query Language) is a database computer language designed for managing data in relational database management system (RDBMS). Its scope includes data query and update, schema creation and modification, and data access control. Query/SQL can help us to check data especially when we have millions of data.

Checking absent

SELECT Employee.[Date In],Employee.[name],Employee.[Date Out],Kehadiran.[Employee ID],Kehadiran.[Unit ID],Kehadiran.[l6_Name],Kehadiran.[L5_Name],Kehadiran.[Tanggal]
FROM Kehadiran
INNER JOIN Employee
ON Kehadiran.[Employee ID]=Employee.[Employee ID]
WHERE Tanggal BETWEEN '2009-07-15 00:00:00' AND '2009-07-15 23:59:59'
AND [Absen Masuk] = ''
AND ([Kode Range] =002 OR [Kode Range]=001 OR [Kode Range]=003)
AND [Kode Leave]=''
AND Employee.[status id]='D1'
AND Employee.Active=1
AND [Date In]<='2009-07-15'
AND Employee.[status ID]='D1'
ORDER BY Kehadiran.[Unit ID],Kehadiran.L6_ID,Kehadiran.L5_ID

Checking Data Error Process

SELECT Employee.[Employee ID],Employee.[Name] FROM Employee
WHERE [Employee ID] NOT IN
(
SELECT Kehadiran.[Employee ID] from Employee
INNER JOIN Kehadiran ON Employee.[Employee ID]=Kehadiran.[Employee ID]
WHERE Kehadiran.Tanggal BETWEEN '2009-07-08 00:00:00' AND '2009-07-08 23:59:59'
AND Kehadiran.[Status ID]='D1'
)
AND Employee.[Status ID]='D1'
AND [Date Out]>'2009-07-08 00:00:00'
ORDER BY [Employee ID]

Checking Overtime

SELECT * FROM Kehadiran
WHERE Tanggal BETWEEN '2009-03-01 00:00:00' AND '2009-03-15 23:59:59'
AND [absen MAsuk]<>'' AND [Kode Leave]<>''