Checking Data Using Query Sql

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]<>''