Grouping by Count

[AvgMoJoe]

I am trying to accomplish the following:
Group report by Patients with '1 Emergency Visit Only','2 to 5 Emergency Visits', or 'More than 5 Emergency Visits'.

I thought I could use the following SQL Expression to count:

COUNT("PAT_ENC_HSP"."ED_EPISODE_ID") OVER (PARTITION BY "PAT_ENC_HSP"."PAT_ID")

and a Formula to Group:

if {%Number of Visits} = 1 then "1 Visit Only"
else if {%Number of Visits} in 2 to 5 then "2 to 5 Visits"
else if {%Number of Visits} > 5 then "More than 5 Visits"
else "*Unknown Number of Visits"

If I put these two on the report, they display as expected. However they do not group as expected; everything is grouped in '1 Visit Only'.

I imagine it has something to do with when the expression is processed vs when the grouping happens/is printed and I need to be clued in on how to fix, or maybe there is a better approach to this grouping?

Crystal Reports 2008

 

[briangriffin]

I would try adding a command object instead of a SQL Expression:

select PAT_ENC_HSP.PAT_ID, count(PAT_ENC_HSP.ED_EPISODE_ID) as EncounterCount from yourtable group by PAT_ENC_HSP.PAT_ID

Then join the command to the PAT_ENC_HSP table.

Your formula should work:

if {EncounterCount} = 1 then "1 Visit Only"
else if {EncounterCount} in 2 to 5 then "2 to 5 Visits"
else if {EncounterCount} > 5 then "More than 5 Visits"
else "*Unknown Number of Visits"

If you're joining to the encounter table, or pulling the SQL expression from the encounter table, then there should never be an unknown number of visits, right? If you're joining to the patient table there could be no visits, but make sure you join Patient to the command using a left join. Then the formula becomes:

if isnull({EncounterCount}) then "*Unknown Number of Visits"
else {EncounterCount} = 1 then "1 Visit Only"
else if {EncounterCount} in 2 to 5 then "2 to 5 Visits"
else if {EncounterCount} > 5 then "More than 5 Visits"

 

[AvgMoJoe]

If I apply a time range to the resulting scheme, will EncounterCount show total encounters within PAT_ENC_HSP table, or only count those within the time period?

 

[briangriffin]

Create the parameters in the command object:

select PAT_ENC_HSP.PAT_ID, count(PAT_ENC_HSP.ED_EPISODE_ID) as EncounterCount from yourtable
where AdmitDate between {?StartOfRange} and {?EndOfRange}
group by PAT_ENC_HSP.PAT_ID

Offhand, I think this would be easier if the parameters didn't exist in the report prior to adding them to the command (and this is much easier if you use two parameters, instead of one range parameter). Then the parameters will show up in the field explorer, and you can incorporate them into the main report.

 

[AvgMoJoe]

Adding to Command ended up making a report that ran forever. I ended up messing with the original way, and noticed I hadn't put the Number of Visits in the actual grouper (there is a grouper, a display of the grouper, and a chart display of the grouper. Now it works. I hate it when a stupid mistake causes so much frustration!