Conditional Date Selection Formula

[YANKRAY]

Using Crystal Reports 10.0

I have a report that will give me a list of Employees grouped by Supervisor.

I am attempting to add a new table that has the Clock In information for each empoloyee. The new table is TA_ATTENDANCE. This table lists all of the attendance for each employee. Each employee has an Employee Number for which I can link the two tables.

In the TA_ATTENDANCE table, clock in information is coded as "CI" for each day (field WORK_PERIOD) the employee comes to work and clocks in.

I want to show on the Employees grouped by Supervisor report that an employee is either "clocked in" or "not clocked in" for the current date. The WORK_PERIOD date field is a Date/Time field.

Any suggestion on how to accomplish this result?

EMPLOYEE-1 CLOCKED IN
EMPLOYEE-2 CLOCKED IN
EMPLOYEE-3 NOT CLOCKED IN
EMPLOYEE-4 CLOCKED IN

 

[kskid]

Try this

[ol]
[li]Left outer join the TA_ATTENDANCE to the Employee[/li]
[li]In your record selection, add the following[/li]

(
isnull({ta_addendance.employeeid}) or 
(
{ta_attendence.code} = "CI" and
{ta_attendence.date} = currentdate
)
)

[li]Create a formula, @ci[/li]

if isnull(ta_attendance.employeeid) or {ta_attendence.code} <> "CI" then
  "Not Clocked In"
else
  "Clocked In"

[/ol]

Cheers,
-LW

 

[YANKRAY]

Thanks LW.

This formula gave me just the employees with "CI" for the currentdate.

There is no isnull condition in the TA_ATTENDANCE table. The TA_ATTENDANCE table has all the attendance information for each employee. There will alwas be a match between the TA_ATTENDANCE.EMPLOYEEID and the LREMPLOYEE.EMPLOYEEID.

I need to see the employee for each supervisor, but if their TA_ATTENDANCE.CODE is prior to current date, they should be marked as "Not Clocked In".

 

[kskid]

Will need more detail information. For a given employee, is a TA_ATTENDANCE record created for each day?

EMPLOYEE-1 3/21/2005 CLOCKED IN
EMPLOYEE-1 3/22/2005 NOT CLOCKED IN
EMPLOYEE-1 3/23/2005 CLOCKED IN
EMPLOYEE-2 3/21/2005 CLOCKED IN
EMPLOYEE-2 3/22/2005 CLOCKED IN
EMPLOYEE-2 3/23/2005 CLOCKED IN
EMPLOYEE-3 3/21/2005 NOT CLOCKED IN
EMPLOYEE-3 3/22/2005 NOT CLOCKED IN
EMPLOYEE-3 3/23/2005 NOT CLOCKED IN
EMPLOYEE-4 3/21/2005 NOT CLOCKED IN
EMPLOYEE-4 3/22/2005 NOT CLOCKED IN
EMPLOYEE-4 3/23/2005 CLOCKED IN

If the above condition exists, what is your expected output?

 

[YANKRAY]

The TA_ATTENDANCE table has the following information for each employee and for every days transactions.

Clock-in, Log-In (after an employee Clocks in, they will log in to a particular and/or multiple work orders), Log-Out, then Clock-Out.

The data looks something like this.

EMPLOYEE-1 CI 3/21/2005 7:00
EMPLOYEE-1 LI 3/21/2005 7:05
EMPLOYEE-1 LO 3/21/2005 15:30
EMPLOYEE-1 CO 3/21/2005 15:30

EMPLOYEE-2 CI 3/21/2005 7:00
EMPLOYEE-2 LI 3/21/2005 7:05
EMPLOYEE-2 LO 3/21/2005 15:30
EMPLOYEE-2 CO 3/21/2005 15:30

EMPLOYEE-1 CI 3/22/2005 7:00
EMPLOYEE-1 LI 3/22/2005 7:05
EMPLOYEE-1 LO 3/22/2005 15:30
EMPLOYEE-1 CO 3/22/2005 15:30

EMPLOYEE-2 CI 3/22/2005 7:00
EMPLOYEE-2 LI 3/22/2005 7:05
EMPLOYEE-2 LO 3/22/2005 15:30
EMPLOYEE-2 CO 3/22/2005 15:30

EMPLOYEE-1 CI 3/23/2005 7:00
EMPLOYEE-1 LI 3/23/2005 7:05

So if this is the current data you see that Employee-2 did not clock in today (3/23).

I am looking to see (if I run the report at say 7:15);

EMPLOYEE-1 Clocked in
EMPLOYEE-2 Not Clocked in

 

[kskid]

If Employee 2 did not clock in, then you will have to change your linking

{Employees.employee_id} -> LOJ -> {ta_attendance.employee_id}

and change the formulas

(
isnull({ta_addendance.date}) or 
(
{ta_attendance.code} = "CI" and
{ta_attendance.date} = currentdate
)
)

The results of the record selection will only select the current day's records with "CI"

if isnull(ta_attendance.date) then
  "Not Clocked In"
else
  "Clocked In"

For Employee 2, the date would be null so the value would be "NOT CLOCKED IN"

You can substitute a date parameter for currentdate.

However, if you are using a date range and want to report the status on each day, then that gets a little more complex.

-LW

 

[YANKRAY]

With the new selection and formula, I am only getting the employees who are clocked in.

I do have a Left Outer Join between LREMPLOYEE.employeeID and TA_ATTENDANCE.employeeID.

I don't understand the symbols in -> LOJ -> if this means anything more than a Left Outer Join.

I am also not understanding the isnull {ta_attendance.date} selection. Since there is a value in every record for ta_attendance.date, the selection appears to me to be only showing me records for the selection {ta_attendance.code} =
"CI" and {ta_attendance.date} = currentdate

 

[kskid]

Assuming the employee information is in the detail section, you should be using {LREMPLOYEE.employeeID} instead of {TA_ATTENDANCE.employeeID}. {LREMPLOYEE.employeeID} would print regardless of whether there is a {TA_ATTENDANCE.date} for the current date or not. It appears that you are using {TA_ATTENDANCE.employeeID}. That's why I am checking for a null date. If I cannot find a matching TA_ATTENDANCE.date and CI value, then TA_ATTENDANCE.date will be null for that employeeid.

That is the nature of Left Outer Join which say to list everything from the left table (LREMPLOYEE) even if I don't have a match on the right table (TA_ATTENDANCE).

Since it is a lleft outer join, a selection criteria of just "CI" would result in an equal join. The isnull(TA_ATTENDANCE.date} compensates for this.

So the statement

(
isnull({ta_addendance.date}) or 
(
{ta_attendance.code} = "CI" and
{ta_attendance.date} = currentdate
)
)

interprests as

either send me a matching TA_ATTENDANCE record with a code of "CI" and a date = currentdate

or

send me nothing (i.e. The TA_ATTENDANCE record is null)

-LW

 

[YANKRAY]

I am still getting only employees that do not have a record in {ta_attendance.date} to show as "Not Clocked In".

The isnull {ta_attendance.date} in the selection seems to be the trouble.

Ray