Grouping based on highest priority statute class

[PDAnalyst]

Hi,

I am using CR11 that hits an Oracle database to pull daily arrestee records.
I have created a main report and two sub-reports. The main report has the information regarding their unique ID as well as the location of arrest, one subreport has their information (name, height, weight, previous addresses) and other subreport has their charges (which can be multiple and in most cases has more than 2-3).

The problem that I have is that I need to group them in the main report depending on the seriousness of their crime class (Felony, Gross Misd etc.). As far as I remember, there is no way to group based on a passed variable from subreport to main report (it could have been so much easier). I can add the Crime Class to the main report and group based on that but it then duplicates each record (so if a person has been charged with 3 crimes and each with different category, he/she gets listed in each group once, instead of only getting listed in the highest Felony group).

Here is how currently the data looks:

ArrestDate BKNG# Location
06/29/09 00111 123 Main st
MIS 199.280.2 Resist arrest
MIS 200.485 Dom Bat (these coming from subreport)
06/29/09 00123 223 Center St
FEL 200.481 Battery w/ dead weap
MIS 266.750 Muni-Contempt (from subreport)
06/29/09 00222 2400 Nowhere St
GM 199.444 Consp to Commit Crime
MIS 266.001 FTA-Traffic (from subreport)


So I need it to group like this:

FEL
06/29/09 00123 223 Center St
FEL 200.481 Battery w/ dead weap
MIS 266.750 Muni-Contempt (from subreport)

GM
06/29/09 00222 2400 Nowhere St
GM 199.444 Consp to Commit Crime
MIS 266.001 FTA-Traffic (from subreport)

MIS
06/29/09 00111 123 Main st
MIS 199.280.2 Resist arrest
MIS 200.485 Dom Bat (these coming from subreport)

Any help is appreciated

Safa

 

[lbass]

How many tables are you working with? What does the SQL query currently look like?

-LB

 

[PDAnalyst]

I am working with 3 tables in the main report (if I add the main charges table then it is 4), 8 tables in the person subreport, 3 tables in the charges subreport. I can add the table that contains the charge class to the main report but when I try to group based on this, it then duplicates the other subreports for each individual and will place an individual that is charged both with a Felony and Misdemeanor to both groups. (as an example if I have 34 people arrested and there were total 0f 72 charges for these people, if I do not group with the charge class, I get 34 individuals, if I group with the main charge, then I get 72 people with multiple of same individual)


Here is the SQL statement for the main report:

SELECT "EH_BOOKING"."BOOKING_NUMBER", "EH_BOOKING"."START_DATE",
"EH_BOOKING"."ENTITY_ID", "EH_ENTITY_ARREST_INCIDENT"."ARREST_DATE",
"EH_GENERIC_ATTRIBUTE"."ATTRIBUTE_NAME",
"EH_ENTITY_ARREST_INCIDENT"."ARREST_LOCATION", "EH_BOOKING"."BOOKING_ID"
FROM ("EPIC"."EH_BOOKING" "EH_BOOKING" LEFT OUTER JOIN
"EPIC"."EH_ENTITY_ARREST_INCIDENT" "EH_ENTITY_ARREST_INCIDENT" ON
"EH_BOOKING"."BOOKING_ID"="EH_ENTITY_ARREST_INCIDENT"."BOOKING_ID") INNER
JOIN "EPIC"."EH_GENERIC_ATTRIBUTE" "EH_GENERIC_ATTRIBUTE" ON
"EH_ENTITY_ARREST_INCIDENT"."INCIDENT_ID"="EH_GENERIC_ATTRIBUTE"."ITEM_ID"
WHERE "EH_GENERIC_ATTRIBUTE"."ATTRIBUTE_NAME"='DR#'


Thanks

Safa

 

[lbass]

Please show the fields from the charges table and identify them so that I can tell how they correspond to your sample data. Also, how do you identify the seriousness of the crime class--can this be done through some other field or do you simply know what the "order" is? If so, what is it?

-LB

 

[PDAnalyst]

lbass,

Thanks for helping me as always.

In the main report, I have added the EH_charges table to see if I can group on that but everytime when I add the Statute Class and try to group on it, this creates duplicate entries for the individuals who has more than one charge.

Safa

 

[PDAnalyst]

Forgot to mention how to determine the seriousness of the crime. Felony is the most serious, then Gross Misdemeanor, then Misdemeanor and lastly Infraction (Which is very rare)
Unfortunately, the seriosness of the crime can only be determined from the Statute Class other thna going through each of the ~4,000 NRS and Municipal codes and assigning each of them to a category.

Safa