showing users with NO Timesheets (no data) for period of time 2

[czarzecki]

Hi: My organization has just implemented a time tracking database where employees enter information on tasks they do and charge their work to a particular project. My supervisors want a report that will show WHICH employees have not entered ANY hours for a particular time period. I'm trying replicate in crystal the function of the following SQL:

select user_name
from staff
where user_name not in
(select user_name
from staff s, staff_hours sh
where s.staff_id = sh.staff_id and
bill_date >= to_date('02/23/2003','MM/DD/YYYY') and
bill_date <= to_date('03/01/2003','MM/DD/YYYY'))
order by 1

They also want the dates to be parameters. If I put in the selection criteria:

Bill date >= {parameter start day of period} AND Bill date <= {parameter end day of period}, what I get is those people who HAVE entered time for that period.

How do I do show the opposite?

Please please help.

Send replies to cmonaghan@acf.hhs.gov

 

[lyanch]

If you have crystal 9 you can use your first SQL statement directly. If not, things get a bit more difficult. Even though you have fairly simple SQL, a stored proc would probably be the easiest method. If you can't use a stored proc I would probably use the following approach.

Include only the staff table in the main report. Break the detail section in two. In the first section include a subreport that gets hours for the period. Create a shared variable that evaluates to true when the staff hours id is null in the subreport header. Then suppress the second detail section containing the name if the shared varible is true.

The disadvantages of this approach is that even though you can make the subreport very skinny, and overlay the following sections, if you have a number of staff that are suppressed consecutively, it will show as a blank spot in your report. Since the number that have entered hours is probably the majority I can see this as a problem.

Lisa

 

[lbass]

You could try the following: Use a left join from the staff table -> staff_hours table. Then in your select statement write:

Not({staff_hours.bill_date} in {?startdate} to {?enddate})

This assumes that employee user_names include only those who are currently employed--if you also have former employees, you might want to add a select criterion that establishes that they have worked within the last x days or months. Doing this would also reduce the number of records returned, so you might want to amend the above to read something like:

{staff_hours.bill_date} in ({?startdate}-90) to ({?startdate}-1) and
not({staff_hours.bill_date} in {?startdate} to {?enddate})

-LB

 

[lbass]

My above suggestion didn't work when I tested it out--&quot;Not in&quot; selects all employees who had hours other than the specified period, but who may or may not have worked hours in the specified period. Sorry about that. So here's another method that does seem to work:

Make links between tables equal, create date parameters, and group on {staff.staff_id}.

Record select statement:

{staff_hours.bill_date} in ({?startdate}-90) to {?enddate) //this pulls all employees working in the last 90 days//

Group select statement:

remainder(sum({@billdatevalue},staff.staff_id),100) = 0 //where formula for {@billdatevalue} is:

if {staff_hours.bill_date} < {?startdate} then 100 else if {staff_hours.bill_date} in {?startdate} to {?enddate) then 1

-LB

 

[czarzecki]

Thanks so much for your response, lbass -- this worked, although I'm not quite sure WHY it worked. I appreciate it very much, and I'm sure my managers will too!

 

[lbass]

I'm glad this worked for you. To explain, the formula {@billdatevalue} assigns a value of 100 to any billdate earlier than the period of interest, and a value of 1 to any date within the period. If you sum the values per employee, anyone who has worked during the last three months and also has worked during the period of interest will have a score like 101, 201, 104, 305, etc., while anyone who has worked only prior to the period of interest, will have a score of 100, 200, 400, etc. The formula in the group select means &quot;select anyone with a sum that is exactly divisible by 100, i.e., that has no remainder.&quot; This rules out employees who worked during the period of interest, because they would show remainders (e.g., in example above, remainders of 1, 1, 4, 5).

-LB