Record Select Question

[MiagiSan]

Hello,

I am using Crystal 2008 with an SQL database. I have created a report from our CRM system that shows a listing of escalated "Cases".

Case ID Name Description
12345 John Smith Test1
67890 Johnny Smyth Test2


What I am trying to do is be able to show which member of our team changed the case assignment. (Who escalated the case)


This information is found only in the Case History Table which also contains any action performed on that case. (Updates, Saves, etc)

PS_RC_ACTION_HIST is the Table
ROW_LASTMANT_OPERID is the Agent ID field in this table.
DESC254- is the Field that contains a description of the action that took place in the case. The line I am interested in is "Provider Group Changed From (no prior value) to EscalatedCases"

To further complicate things, this line might be slightly different each time. For example, it could also be "Provider Group Changed From DevelopmentCases to EscalatedCases"

How do I select the OPERID from the PS_RC_ACTION_HIST table that has DESC254 LIKE "Provider Group Changed to EscalatedCases"

My end product should look like:


Case ID Name Description Assigned by
12345 John Smith Test1 Agent1
67890 Johnny Smyth Test2 Agent3

Thank you, I hope I added enough information.

MS

 

[Turkbear]

Hi,
Try a where clause something like:

where INSTR(PS_RC_ACTION_HIST,"to EscalatedCases") > 0

 

[Charliy]

If it's always at the end of the string, just add
AND RIGHT({table.field},14}) = "EscalatedCases"

to your select statement

 

[MiagiSan]

Perfect!! Thank you!!