Count of Previous Codes per Group

[dleewms]

I am reposting this as I realize that my first post was not clear/detailed enough. I am having a lot of trouble with this report and hope that someone can assist me. I have tried the previous suggestions and nothing has worked.

I am on Crystal 10 using an Oracle 8i database.

O.K. I have tubes which can have one or several defects. There is a defect that causes us to ultimately scrap the tube. The Defect code to scrap the tube is 90040. Whatever defect chronologically occurred right before the 90040 defect code was added is the one that I need. Whenever defects are added to a tube, a unique activity_# number is added to the record. This number is in sequential order. I need to identify the defect that caused us to scrap the tube and then get a count of those defects.

Below are three tubes with defects

Tube A
Defect Codes Date/Time Activity_#
90040 3/4/2004 8:51:53AM 153078265
90045 2/18/2004 2:47:32AM 150988537
90062 2/14/2004 3:37:51AM 150551080
90062 2/18/2004 5:15:32AM 151003795
90089 2/182004 10:28:02AM 151036804

Tube B
Defect Codes Date/Time Activity_#
90038 2/19/2004 5:00:27AM 151171980
90040 2/23/2004 10:36:42AM 151708300
90044 2/19/2004 5:08:30AM 151172754
90045 2/18/2004 8:26:33AM 151024526
90062 2/18/2004 6:31:57AM 151036804
90062 2/18/2004 10:26:42AM 151036722
90096 2/19/2004 10:56:19AM 151307636

Tube C
Defect Codes Date/Time Activity_#
90040 3/4/2004 10:19:04AM 153088914
90045 2/2/2004 1:30:10PM 148613340
90062 2/2/2004 11:47:29AM 148595237
90089 2/3/2004 7:41:59AM 148763278

My report simply needs
Defect Code Count
90089 2
90096 1

I have grouped the report first by defect code. Next, I added a second grouping by Activity_#. I am working on conditionally suppressed the rows where next({Command.DEFECT_CODE})<>'90040' or ({Command.DEFECT_CODE}) = '900040'. I can get individual codes per tube but I need the summary information
Any help would be greatly appreciated.

DLee

 

[synapsevampire]

To simplify things, let's demonstrate how you select only rows that have the defect that caused the error.

Group by tube.

In the Report->Edit Selection Formula->Group place a formula akin to:

{table.activity} = maximum({table.activity},{table.tube})

Now you can use a cross-tab to produce the summary by dropping the defect code into the row and summary area and set the summary to count.

-k

 

[dleewms]

K,

Thanks for responding but the problem is that the group selection formula does not find the defect that caused the error. It is simply finding the last defect per tube, which often is defect 90040. I need to get the code that caused the 90040 defect code.
I have tried using the group selection formula, but can't use the "NEXT" function in it since "NEXT" is evaluated later.

 

[dGrouse]

I assume that defect records for "tube 1" and "tube 2", etc., are all in the same table, and that there's another column we're not seeing that identifies the particular tube -- is that correct? If so, here's a solution based on a SQL command object that gives you what you need:

select t4.defect_code,
count(t4.defect_code) 'count_defect_code'
from tube_defects t1 inner join
(
-- second level subquery - returns the tubeID and
-- the defect code for the defect prior to the 94004
select t2.tubeID,
t2.defect_code
from tube_defects t2 inner join
(
-- inner most query - a table of each tube ID and
-- the defect date prior to the 94004 for each tube
select tubeid,
maximum(date/time) as 'dt_before_94004'
from tube_defects
where defect_code <> '90040'
group by tubeID
) t3 on t2.tubeID = t.tubeID and t2.date/time = t3.dt_before_94004
) t4 on t1.tubeID = t4.tubeID
-- filter out all records in the topmost level where
-- there wasn't any 94004
where t1.defect_code = '94004'


I think that'll work... best wishes.

Jeff Prenevost
IS Administrator
Emergency Physicians Medical Group, PC
Ann Arbor, MI