| Author |
Message |
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
  
Topic: Top N Across 4 Fields
Posted: 29 Dec 2009 at 10:51am |
|
I am using a medical charges/billing database. For each charge a medical service was provided, and 1 to 4 diagnoses are assigned. In the database they are listed as table.diag1, table.diag2, table.diag3, table.diag4. At least 1 diagnosis is required for each charge.
Individual diagnosis codes can appear in any of the 4 fields, they are not restricted. Sometimes only 1 diagnosis code is available, leaving the other 3 blank. What I want to do is find the top N diagnoses across all four fields. I am not sure how to approach this, but I assume I'll need a few formulas. Any suggestions?
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 11:26am |
So you want to know how many time a diag was used regardless if it was primary, secondary, tertiary or quaternary?
Is this a SQL DB and do you have rights to creating a views or stored procedures? Edited by DBlank - 29 Dec 2009 at 11:26am
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 12 Jan 2010 at 8:18am |
|
Sorry for the long time between responses.
I do not have rights for views.
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 10 Mar 2010 at 7:38am |
|
Does anyone have any clue about this? There's got to be a better way than exporting to Excel. Please help!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Mar 2010 at 7:52am |
For clarification, since they can have up to 4 diags per bill if they have 4 diags you want each one counted towards the Top N count or just a primary diag?
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 10 Mar 2010 at 7:56am |
|
I need to have each one counted.
Specifically the problem is that I cannot determine which diagnosis is primary. Normally it would be the first diagnosis, but data entry was sloppy.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Mar 2010 at 11:19am |
Perhaps using a COMMAND to select the records from the table 4 times and UNION all together
SELECT apptointmentID, Diag1 as DIAG
from table
UNION
SELECT apptointmentID, Diag2 as DIAG
from table
UNION
SELECT apptointmentID, Diag3 as DIAG
from table
UNION
SELECT apptointmentID, Diag4 as DIAG
from table
Then you can do a grouping on the DIAG field and an insert SUMMARY as a COUNT on that field at the group level.
You could also use the TOP N group feateure then if you wanted to.
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 11 Mar 2010 at 8:24am |
|
WOW! This worked perfectly. I didn't even realize this feature was available in Crystal. Thank you!!!
I had to add my WHERE statement to each SELECT, but since Crystal Reports makes use of Parameter fields it was much easier to ensure the same criteria was used. I also needed to change the union to UNION ALL, otherwise I would had lost any duplicated codes (which is exactly what I wanted to see).
I now have a crosstab which counts each DIAG, but I have a blank row right at the top. If I stick DIAG on my details line and page through all the data I'm finding blank lines there too. Any idea on how to eliminate those from my my SQL command? Or would this need to be done through Crystal somewhere else?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Mar 2010 at 8:48am |
Likely these are from "" or NULLs. Try excluding them in your WHERE clauses or you can exlcude them in the select statement in crystal...
NOT (isnull(medication) or medication = "")
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 11 Mar 2010 at 9:31am |
|
Perfect again! Thank you so much!
|
| IP Logged |
|
|
|
