Removing records with the same ID in selected dataset (ADS)

Question

I'm trying to query a clinic practice management database to return a dataset that matches any in a list of values (icd-9 codes) for four different fields, but then only return the id number for the patient, or only the first record for that patient which has one of the icd-9 codes. Then, I plan to finagle some sub report to give me the rest of the data I need for the patient, including the icd-9 codes for the last case/visit.

The software generates something like the following:

SELECT DISTINCT 
                MWTRN."Chart Number" AS Chart_Number, 
                MWTRN."Diagnosis Code 1" AS Diagnosis_Code_1, 
                MWTRN."Diagnosis Code 2" AS Diagnosis_Code_2, 
                MWTRN."Diagnosis Code 3" AS Diagnosis_Code_3, 
                MWTRN."Diagnosis Code 4" AS Diagnosis_Code_4, 
                MWTRN."Date From" AS Date_From, 
                MWTRN."Date To" AS Date_To, 
                MWPAT."First Name" AS First_Name, 
                MWPAT."Last Name" AS Last_Name
FROM MWTRN MWTRN, MWPAT MWPAT
WHERE 
      (MWPAT."Chart Number" = MWTRN."Chart Number")
GROUP BY MWTRN."Chart Number", 
         MWTRN."Diagnosis Code 1", 
         MWTRN."Diagnosis Code 2", 
         MWTRN."Diagnosis Code 3", 
         MWTRN."Diagnosis Code 4", 
         MWTRN."Date From", MWTRN."Date To", 
         MWPAT."Last Name", MWPAT."First Name"

After generating the dataset how can I go back and pair down the results?

I've read a couple of the ad supported SQL tutorial sites to read the generated SQL and thought what I needed was DISTINCT but this only checks whether the whole record is distinct, not just the ID.

-- Layman using Advantage Database Server and reporting software which may be tied to Crystal Reports somehow.

Answer 1

For you testing purposes you will want to have Advantage Data Architect, downloadable from the Advantage DevZone. http://devzone.advantagedatabase.com In addition, you will want the full help file which includes a copy of Cary Jensen's marvelous "Advantage Database Server, a Developer's Guide", available the same place, which will help you with the getting up to speed on SQL in Advantage, and SQL in general. You have a couple of options depending on exactly what it is you are trying to accomplish. You could create an SQL script and take your statement into a temporary table as in

Select into #MyTemp --all the rest of the complex SQL command--

Then you can do what you want with that file to pare it down. If, however, you just want a list of patients that have a certain diagnosis code, you can do something like

Select pat.id, pat.name, pat.address from pat where pat.id in (Select id from tran where diag1 in ('123.3','123.4') UNION Select id from tran where diag2 in ('123.3','123.4') UNION
Select id from tran where diag3 in ('123.3','123.4') UNION Select id from tran where diag4 in ('123.3','123.4');

And if you need to do it in Crystal, you can create that union set using the AddCommand section...just make sure you are using the Advantage Crystal Driver, set up correctly, and obtainable from...you guessed it...Advantage Devzone

Hope that helps.