SQL Grouping Question

[smilbert]

I'm relatively new to programming in SQL, but I've been using Crystal Reports for many years, so I'm familiar with databases and tables.

I'm trying to create a view that shows a Patient ID and the COUNT of exams they've ever had. I have a tbExams table (contains the ExamID) and tbEncounters (which contains the PatientID).

I've got another table (tbcdExamStatus) that gives the exam status (I only want Ranks between 499 and 801)

In general, my result view is correct, but I've found that when a patient has exams with more than one Rank value, I get two entries in the table.

I'm guessing my group statement is incorrect. Any help is appreciated! Here is my query:

SELECT dbo.tbEncounters.PatientID, COUNT dbo.tbExams.ExamID) AS PrirorCount, dbo.tbcdExamStatus.Rank, dbo.tbcdExamStatus.Description
FROM dbo.tbExams LEFT OUTER JOIN
dbo.tbcdExamStatus ON dbo.tbExams.Status = dbo.tbcdExamStatus.Code LEFT OUTER JOIN
dbo.tbEncounters ON dbo.tbExams.EncounterID = dbo.tbEncounters.EncounterID
GROUP BY dbo.tbEncounters.PatientID, dbo.tbcdExamStatus.Rank, dbo.tbcdExamStatus.Description
HAVING (dbo.tbcdExamStatus.Rank > 499) AND (dbo.tbcdExamStatus.Rank < 801)
ORDER BY dbo.tbEncounters.PatientID DESC

 

[SQLSister]

Your statement is correct. If you have two differnt ranks for the same person, it must return two rows as that is the data you have. If you only want one, you need to specify a method by which you would determine which one to use. Specifying how you would determine which is the correct rank is the only way we can help you write code tathat will do this. It could be the max of some field or the earliest date or any number of other methods. But something has to tell you which of the two or more records is the one you want or you need to see all the records.

"NOTHING is more important in a database than integrity." ESquared

 

[smilbert]

Thank you for your response.

Is there any way to simply count the patient entries with ranks in a specified range (regardless of what the rank IS?) Anything like a CountIF statement?

Or, is there a way to SUM all of the different ranks into another field (that I can use in my Crystal Report?) There are currently only 5 different values.

 

[SQLSister]

Give me some sameple data and some sample resuilts and we can help you get a query that deos what you want.

"NOTHING is more important in a database than integrity." ESquared

 

[smilbert]

Thanks for all of your help!

First, I'll mention that an exams goes through many different STATUS' (as they progress from scheduled appt to complete). This status relates to another table (tbcdExamStatus) which provides a RANK. It's generally easier to use RANK to include several status' (ie patient has been checked in, but exam has not been billed--there are about 4 possible status in this range). The query below is using the status directly because there are only two that I'm looking for. I do get the same result when I join the table to get the rank.

So, I was able to find a patient with multiple exams with different status values. If I narrow the query to look for the specific PatientID that I know has multiple exams:

SELECT dbo.tbEncounters.PatientID, COUNT(dbo.tbExams.ExamID) AS PrirorCount, dbo.tbExams.Status
FROM dbo.tbExams LEFT OUTER JOIN
dbo.tbEncounters ON dbo.tbExams.EncounterID = dbo.tbEncounters.EncounterID
WHERE (dbo.tbEncounters.PatientID = 83707)
GROUP BY dbo.tbEncounters.PatientID, dbo.tbExams.Status, dbo.tbExams.Status
HAVING (dbo.tbExams.Status = N'C') OR
(dbo.tbExams.Status = N'PDC')
The Result:
PatientID PrirorCount Status
83707 5 C
83707 1 PDC

If you need to see all of the data in the tbExams table, let me know.

 

[SQLSister]

so what do you want to display for this person if he only has one record in your result?

"NOTHING is more important in a database than integrity." ESquared

 

[smilbert]

Sorry for the delay--power storms been keeping me busy. What I'd like to see for the above data is:

PatientID PrirorCount Status
83707 6 (don't need this)

I don't need the status/rank in this view--I've added it while I was troubleshooting the 'duplicate' PatientIDs

Thanks