SQL Expression for Crystal Reports XI
Posted on 2006-05-24
As some of you may be aware, Crystal Reports XI is able to use SQL expressions for reporting. I've asked this question o the CR XI forum but I also wanted to ask it here to you SQL Experts.
I use Crystal Reports XI and I need to create a SQL Expression for my report. I need the expression to ASSIGN a FILE TYPE to each FILENUMBER in my data table when I run the report for use in SORTING in a crosstab.
My data table looks like this:
FILENUMBER APPLICABLEDATE ACTIVITYCODE STATUS CODE SUBJECTCODE
100 01/10/06 TR ABC
100 01/10/06 RR ABC
100 01/19/06 PS ABC
100 01/19/06 PF ABC
100 02/13/06 HS ABC
100 02/18/06 OE ABC
100 02/13/06 RC ABC
100 01/11/06 TR DOG
100 01/11/06 RR DOG
100 01/18/06 PS DOG
100 01/18/06 PF DOG
100 02/14/06 HS DOG
100 02/19/06 OE DOG
101 01/10/06 TR ABC
101 01/10/06 RR ABC
101 01/19/06 PS ABC
101 01/19/06 PF ABC
101 02/13/06 HS ABC
101 02/18/06 OE ABC
101 02/13/06 RC ABC
101 01/11/06 TR DOG
101 01/11/06 RR DOG
101 01/18/06 PS DOG
101 01/18/06 PF DOG
101 02/14/06 HS DOG
101 02/19/06 OE DOG
I need for the SQL Expression to be able to assign a LITIGATIONTYPE based on the COMBINATIONS of ACTIVITY AND STATUS CODES.
The expression needs to first of all isolate the data for the FILENUMBER, then isolate by SUBJECTCODE and then, do the calc in order to assign a LITIGATION TYPE.
The following type of calc needs to be done.
FOR each SUBJECT CODE within a FILENUMBER the following analysis needs to be done:
- If there are ACTIVITY CODES of TR, RR, RC and a STATUS CODE of PF… then this is a LIT COMPLETED TYPE.
- If there are ACTIVITY CODES of TR, RR but no STATUS CODE of PF then this is a LIT STARTED
- If there are ACTIVITY CODES of TR, RR,RC but no STATUS CODE of PF then this is NO LIT NEEDED
I have more types, but I think that if I can get the SQL Expression, I can plug in the other types myself.
NOTE: There can be numerous SUBJECT CODES per file but basically each SUBJECT CODE should have the ACTIVITY AND STATUS CODE data to use for the LITIGATION TYPE assignment.
PLEASE, PLEASE let me know if you need more information.