Complex SQL ? (log) and URGENT

Hi list.

     I'm experiencing difficulty with a view that was created to support a Cognos data cube. The Master query is a union of 3 sub views. I need to add some rollup columns to the master view as follows:

     AccidentCount
     RestrictedCount
     AwayCount
     ReportableCount

My query tracks accidents on sites, with the primary key being the Inc_id. There are many other levels on which to group the records. But basically the main query also tracks things like what body part was involved (so can be more than one etc). The problem is that we need the query the way it is (flat) because it feeds a cognos data cube. The problem is we need to count certain items based on values of certain fields. Unfortunately, while these fields relate the the incident, so the field values get replicated for each row because of the other levels that we also must track. Each record looks something like below:

Inc_ID 12334
BodyPart Hand
DaysOff 3
DaysRestr 1

Inc_ID 12334
BodyPart Foot
DaysOff 3
DaysRestr 1

The cognos person who created the cube has created calculated fields in order to get a sum.

For instance, we want to know how many days people had OFF due to an injury (cumalative). However, he had added a field like this:

Inc_ID 12334
BodyPart Hand
DaysOff 3
DaysRestr 1
OffDays If DaysOff > 0 then 1, else 0
RestrcDays If DaysRestr > 0 then 1, else 0

Inc_ID 12334
BodyPart Foot
DaysOff 3
DaysRestr 1
OffDays If DaysOff > 0 then 1, else 0
RestrcDays If DaysRestr > 0 then 1, else 0

Then in Cognos, he just sums these values to get totals. But as you can see, they are skewed, because the totals need to be based on INCIDENT, not all of the subrecords.

These views are very complex and cannot be easily changed as it needs to accomadate Cognos. Is there a way for me to calculate these values (using subqueries) and assign the values to only ONE record per INCIDENT in the view?

As a post note, I would NOT have created such a monster, but I inherited it. The guy who built the Cognos cube was a consultant who took 3-4 MONTHS to build the Cognos structure, and he is no longer here. SO, unfortunately, I need to figure out a way to assign the values to the 2 two columns as follows:

Inc_ID 12334
BodyPart Hand
DaysOff 3
DaysRestr 1
OffDays If DaysOff > 0 then 1, else 0 AND min(rowid) where INC_ID is unique? RestrcDays If DaysRestr > 0 then 1, else 0 AND min(rowid) where INC_ID is unique?

Inc_ID 12334
BodyPart Foot
DaysOff 3
DaysRestr 1
OffDays If DaysOff > 0 then 1, else 0 AND min(rowid) where INC_ID is unique? RestrcDays If DaysRestr > 0 then 1, else 0 AND min(rowid) where Received on Thu Dec 14 2000 - 06:18:24 CST