Distinct count on multiple fields

[TEM3]

Using Crystal Reports 8.5 and Oracle 9i tables......

I need guidance on a game plan for a report on "assignment" productivity.

All activity for a completed assignment is in one table (LABREPT). The activities involved in completing an assignment are the analysis, the review and the final approval. All three contributing activities are tracked by a User ID and a date. For a given date period ({LABREPT.Date Completed}) I want to a distinct count on the number of people involved during that period. The same User ID can appear in any of the three fields ({LABREPT.Analyst}, {LABREPT.Peer Reviewer} and {LABREPT.Final Approval}). I only want to count the User ID once from any of those three fields during the time period. Simply put, I just want to establish how many people were working (doing any of the three activities) during that time period.

Any suggestions?

 

[lbass]

Please don't bother with the new one, as it is not working quite right anyway.

Did you try the last issue on your own? I've noticed that you've created well over 100 threads, with almost no contribution back to the forum. This is not a help desk--it is expected that people will contribute.

-LB

 

[TEM3]

Yes I am working on the new problem. I think if I can find and link an activity date that is done every work day (Date Completed will not work since in the smaller sections there are many days when no assignments are completed), I can use that to get a work day number.

My conrtibution?? Well I have (always) felt that I am still too much of a Crystal Reports novice to be of much help. But I will try. I have hoped that some of my questions and the excellent answer I have benefited from have helped others.....

Lame, I know, but I will try harder (to contribute).

 

[TEM3]

I solved my workday calculation problem by running a report to count the days in a month that assignments were created and then pluggin the numbers into the following formula:

shared numbervar nwd;

if month({LABREPT.Date Completed}) = 1 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 24
)
else
if month({LABREPT.Date Completed}) = 2 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 18 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 19 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 19
)
else
if month({LABREPT.Date Completed}) = 3 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 23 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 22
)
else
if month({LABREPT.Date Completed}) = 4 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 19 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 20 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 20
)
else
if month({LABREPT.Date Completed}) = 5 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 21 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 22
)
else
if month({LABREPT.Date Completed}) = 6 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 21 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 21
)
else
if month({LABREPT.Date Completed}) = 7 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 16 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 21 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 21
)
else
if month({LABREPT.Date Completed}) = 8 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 23 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 23 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 23
)
else
if month({LABREPT.Date Completed}) = 9 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 20 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 19 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 19
)
else
if month({LABREPT.Date Completed}) = 10 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 21 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 22 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 22
)
else
if month({LABREPT.Date Completed}) = 11 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 18 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 19 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 19
)
else
if month({LABREPT.Date Completed}) = 12 then
(
if year({LABREPT.Date Completed}) = 2006 then nwd := 20 else
if year({LABREPT.Date Completed}) = 2007 then nwd := 20 else
if year({LABREPT.Date Completed}) = 2008 then nwd := 20
);


Not elegant, but it works. Thanks again.