Query to return the attendance and leave of staff

[AmrMohammed]

Greetings,

I have 3 tables in my database HR_Employee_Personal_Info, HR_Employee_Job_Info and HR_Attend_And_Leave_Of_Staff respectively, I want to get the attendance of each employee based on his work shift. I will mention the columns within each table which will be of interest in order to do the job.

Table HR_Employee_Personal_Info: [Employee ID], [Employee Name]
Table HR_Employee_Job_Info: [Employee ID], [Job Title], [Shift ID]
Table HR_Attend_And_Leave_Of_Staff: [Employee ID], [Verify Mode] which will be 1 for fingerprint and 15 for face, [In/Out Mode] which will be 0 for attend and 1 for leave, [Date], [Time]

I have 2 work shifts one begin at 8:00:00 AM and end on the same day at 4:00:00 PM and the other begin at 6:30:00 PM and end on the next day at 8:15:00 AM, 8:15:00 to allow workers of the day shift to put their face & fingerprint, I have a datagridview , 2 datetimepickers and a combobox contains the shift names I have. All what I want that after I select specific days within the datetimepickers and select the shift and press a button I retrieve attendance of only those employees in that shift within the table of attendance log I mentioned above Table No. 3. The datagridview showed as follows

[Employee ID], [Employee Name], [Job Title], [Attendance Data]. [Attendance Time], [Leave data], [Leave Time]

So, I want to generate a query or number of quries that is when executed return me that data of employee that way looks in the datagridview returns NULL if it found one or both of [Attendance Time], [Leave Time] has no value within the Attendance Log Table (Table No. 3) I hope that I explained my problem well and the idea of the problem becomes clear.

Thanks in advance. Best Regards.

[BitBlt]

A couple of observations about your scenario, then a couple of questions.

Observations:
1. There is nothing in the data to tell you the time when a shift starts or ends. That means you have to hard-code it somewhere, which makes it difficult to add shifts or change start/end times.
2. There is no good direct relationship between the attendance table and the job table, just [Employee Id]. This means you have to build in some sort of procedural evaluation to figure out what shift a particular attendance applies to (assuming one Employee can switch shifts at some point, if that sort of thing is important to you).
3. There is no way to tie the different attendings and leavings. You have to piece it together with the date/time combinations and hope that the employee remembered to clock in and out.

Questions:
1. What do the columns "[Attendance Data]" and "[Leave Data]" mean? Are they a combination of "[Verify Mode]" and "[In/Out Mode]"? Or are they supposed to actually be "[Date]"?
2. Are you trying to build a "data scrubbing" query, to find unmatched attendings and leavings? As in, "I want to find out if someone arrived but never left, or someone left and never arrived"?
3. Are you trying to figure out based on Employee, if they are late to their shift, or are leaving early?

If I'm off the mark here, just please clarify what you're looking for.

[BitBlt]

Okay, so I couldn't leave well enough alone. I went ahead and made some assumptons about your tables, and I put together a query that will return all rows for your tables showing each employee, and whether they either attended or left (or both) for a particular shift (1 = day shift, 2 = night shift). Shows "NULL" for the leaving if there is an attend but no leave, and shows NULL for the attending if there is a leave but no attend.

I warn you, it's not pretty. Hold your nose:

SELECT 
a.[Employee ID], 
a1.[Employee Name], 
a.[Job Title], 
b.[Date] AS [Attendance Data], 
b.[Time] AS [Attendance Time], 
c.[Date] AS [Leave data], 
c.[Time] AS [Leave Time]
FROM dbo.HR_Employee_Job_Info a
INNER JOIN dbo.HR_Employee_Personal_Info a1
ON a.[Employee ID] = a1.[Employee ID]
INNER JOIN dbo.HR_Attend_And_Leave_Of_Staff b
ON b.[Employee Id] = a.[Employee Id]
AND b.[In/Out Mode] = 0
LEFT JOIN dbo.HR_Attend_And_Leave_Of_Staff c
ON c.[Employee Id] = a.[Employee Id]
AND ISNULL(b.[Date], c.[Date]) = c.[Date]
AND c.[Time] =
    (
    SELECT MIN(d.[Time]) 
    FROM dbo.HR_Attend_And_Leave_Of_Staff d
    WHERE d.[Employee Id] = c.[Employee Id]
    AND d.[Date] = c.[Date]
    AND d.[Time] > ISNULL(b.[Time], c.[Time])
    )
WHERE a.[Shift ID] = 1
UNION
SELECT 
a.[Employee ID], 
a1.[Employee Name], 
a.[Job Title], 
b.[Date], 
b.[Time], 
c.[Date], 
c.[Time]
FROM dbo.HR_Employee_Job_Info a
INNER JOIN dbo.HR_Employee_Personal_Info a1
ON a.[Employee ID] = a1.[Employee ID]
INNER JOIN dbo.HR_Attend_And_Leave_Of_Staff c
ON c.[Employee Id] = a.[Employee Id]
AND c.[In/Out Mode] = 1
LEFT JOIN dbo.HR_Attend_And_Leave_Of_Staff b
ON b.[Employee Id] = a.[Employee Id]
AND ISNULL(c.[Date], b.[Date]) = b.[Date]
AND b.[Time] =
    (
    SELECT MAX(d.[Time]) 
    FROM dbo.HR_Attend_And_Leave_Of_Staff d
    WHERE d.[Employee Id] = b.[Employee Id]
    AND d.[Date] = b.[Date]
    AND d.[Time] < ISNULL(c.[Time], b.[Time])
    )
WHERE a.[Shift ID] = 1
UNION
SELECT 
a.[Employee ID], 
a1.[Employee Name], 
a.[Job Title], 
b.[Date], 
b.[Time], 
c.[Date], 
c.[Time]
FROM dbo.HR_Employee_Job_Info a
INNER JOIN dbo.HR_Employee_Personal_Info a1
ON a.[Employee ID] = a1.[Employee ID]
INNER JOIN dbo.HR_Attend_And_Leave_Of_Staff b
ON b.[Employee Id] = a.[Employee Id]
AND b.[In/Out Mode] = 0
LEFT JOIN dbo.HR_Attend_And_Leave_Of_Staff c
ON c.[Employee Id] = a.[Employee Id]
AND CAST(CONVERT(VARCHAR(10), c.[Date], 101) + ' ' + CAST(c.[Time] AS VARCHAR(10)) AS DATETIME) =
    (
    SELECT MIN(CAST(CONVERT(VARCHAR(10), d.[Date], 101) + ' ' + CAST(d.[Time] AS VARCHAR(10)) AS DATETIME)) 
    FROM dbo.HR_Attend_And_Leave_Of_Staff d
    WHERE d.[Employee Id] = c.[Employee Id]
    AND d.[Date] = DATEADD(d, 1, b.[Date])
    AND d.[Time] < b.[Time]
    )
WHERE a.[Shift ID] = 2
UNION
SELECT
a.[Employee ID], 
a1.[Employee Name], 
a.[Job Title], 
b.[Date], 
b.[Time], 
c.[Date], 
c.[Time]
FROM dbo.HR_Employee_Job_Info a
INNER JOIN dbo.HR_Employee_Personal_Info a1
ON a.[Employee ID] = a1.[Employee ID]
INNER JOIN dbo.HR_Attend_And_Leave_Of_Staff c
ON c.[Employee Id] = a.[Employee Id]
AND c.[In/Out Mode] = 1
LEFT JOIN dbo.HR_Attend_And_Leave_Of_Staff b
ON b.[Employee Id] = a.[Employee Id]
AND CAST(CONVERT(VARCHAR(10), b.[Date], 101) + ' ' + CAST(b.[Time] AS VARCHAR(10)) AS DATETIME) =
    (
    SELECT MAX(CAST(CONVERT(VARCHAR(10), d.[Date], 101) + ' ' + CAST(d.[Time] AS VARCHAR(10)) AS DATETIME)) 
    FROM dbo.HR_Attend_And_Leave_Of_Staff d
    WHERE d.[Employee Id] = c.[Employee Id]
    AND d.[Date] = DATEADD(d, -1, c.[Date])
    AND d.[Time] > c.[Time]
    )
WHERE a.[Shift ID] = 2

If you want to verify against your data, just use "SELECT a1.*, a.*, b.*, c.*" in place of each of the four main SELECTs above.

This was tested on a MS SQL2008 instance. If you like I can post my table definitions and sample data inserts.

Hope this is what you were looking for. If not, it was still a fun exercise for me. Good luck!

[AmrMohammed]

I do not know what I can say to you MR. Alfred B. Smith "BitBlt" I got the Name from the end of your post. You make me a query that give me back all cases of attendance for an employee base on specific shift. I have a table within database called HR_Work_Shift and its columns are [Work Shift ID], [Work Shift Name], [Start Time], [End Time] so when ever I got a result I will take it, in case, I have an occurance of attend and leave at a day and take resulting attend time and leave time and compare them to start and end time of the shift to know if that person come and leave on time or there is a delay. Thanking words I know are not enough to thank you for your support and help. The statements you wrote are great and unfortunately I'm not good at SQL and that is my biggest problem in that job I'm working on now & I'm trying to figure out your statement, however, I cannot understand some sections of it but it returns me the data right way.

I will explain to you from another view. I have employee a, b, c and d. Employee a and b are assigned to work shift 1 "the day shift" and employees c and d are assigned to work shift 2 "the night shift".
I want on the windows form I designed to choose the start date, end date and a specific type of shift then when I pressed a button it gives me all the employees assigned to the shift I selected beside their attendance and leave dates and times within thr range of dates I selected from the datetimepickers.
So lets say that employees a and b both have some days they have attendance and leave dates and times, some days they have attendance only or leaves only "they put their fingerprint wrong way so it did not get properly" and some days employee a did not come to work and b too have his own days in which he did not come to work too.
I hope my point of view become clear now so I want all employees assigned to a specific shift even if they have no attendance and leave records so that I know that those employees on those days did not come to the work.

I got a new issue that sometimes employees do not see that the terminal is on leaving state or attendance state and they put their fingerprint for example employees of the shift No. 2 put their fingerprints at 8:00:00 AM to record their leavings, and on that time it is supposed that the employees of the day shift, shift No. 1, to put their fingerprints to record their attendance, the termianl was on attendance state so I want to switch those invalid records for employees to their true states.

Finally, I cannot say a word that describes how much I appreciate your help and support. Can you lead me to way so I can read or improve my skills in SQL I really will meet many problems that way I can make statements but not like that one you wrote I can make simple join statements that is very clear but complex one I cannot do it.

God bless you and drop your way all great things.

Best regards, Amr.

[BitBlt]

You're welcome for the help. Please mark this thread "solved" if you got the answer you needed. If you have additional questions, you should start a new thread. It's more likely you will get a timely answer that way.

Your best bet to improve your SQL skills is to google SQL tutorials (there are thousands of them, at all different levels...find one you like).

Next, put up a nice little "play area" database in your development environment, and do "science experiments" on some real-life data. Like learning a musical instrument, you should PRACTICE, PRACTICE, PRACTICE.

Next, come to DaniWeb, and look over some of the "solved" threads. You could even try to come up with your own solutions rather than just look at the given solutions. That way, you can almost use it like a textbook.

Finally, you should go to other SQL Server publications (one of my favorites is "SQLServerCentral") for "tips and tricks" articles.

Hope these tips are helpful to you. Good luck, and happy coding!

Oh, and my name isn't Alfred B. Smith...he's just the guy I got the quote from. :-)

[AmrMohammed]

If that thing will not annoy you. What did you mean by ISNULL(b.[Date], c.[Date]) = c.[Date] I know that the ISNULL return a value, commonly 0, if its first parameter has no value so if the b.[Date] has no value it will return the value of c.[Date] but what is the mean of c.[Date] = c.[Date] in such case?

You used such expression many times within the query and I'm asking to be able to analyze it and benefit from it as much as I can. I know I may be annoying, forgive me for that, but really I wanna learn and know.

Thanks in advance.

[BitBlt]

No annoyance, glad to help.

Using the ISNULL in that fashion simply is a way to make sure there is a value to compare to. In this case, it's an artifact of an earlier iteration at solving your issue. It may not even be necessary. As a science experiment, you might consider removing the ISNULL and see if it makes a difference.

In some cases, you can use something like this:
...
AND ISNULL(b.[Date], '1/1/1900') < ISNULL(c.[Date], '1/1/1900')
...
What this will do is make sure that if no date is available, it is treated as a low value. So, if b.[Date] was null, the above snippet would evaluate to TRUE (assuming c.[Date] had a value). If you didn't use the ISNULL, it would evaluate FALSE in every case because anything compared to NULL gives (as far as a conditional is concerned) FALSE.

Here's a little science experiment you can do to illustrate this behavior:

declare @myDate as varchar(10)
declare @myNullDate as varchar(10)
select @myDate = '1/1/2012', @myNullDate = NULL

select @myDate as '@myDate', @myNullDate as '@myNullDate', 
case 
    when @myDate = @myNullDate OR @myDate > @myNullDate OR @myDate < @myNullDate then 'Valid Compare'
    else 'Invalid compare'
end as Result1, 
case 
    when @myDate = isnull(@myNullDate, '1/1/1900') OR @myDate > isnull(@myNullDate, '1/1/1900') OR @myDate < isnull(@myNullDate, '1/1/1900') then 'Valid Compare' 
    else 'Invalid compare'
end as Result2, 
case 
    when @myDate = isnull(@myNullDate, @myDate) OR @myDate > isnull(@myNullDate, @myDate) OR @myDate < isnull(@myNullDate, @myDate) then 'Valid Compare' 
    else 'Invalid compare'
end as Result3

Each 'case' statement has a complete disjoint comparison set (meaning that all possible conditions are accounted for... greater, equal or less). Note that comparing anything to NULL gives a value NULL, so it's neither true nor false. Therefore the first CASE statement returns the 'else' condition because none of the cases can be evaluated.

This is what I mean by "science experiments". When you come across a concept or technique, build a little "test case" to illustrate the behavior, tweak the statement to see how it behaves in different scenarios and learn from it.

Good luck!