Filtering Duplicates

Question

Hello everyone and thanks for the help in advance. I working on a medical application that tracks referrals to specialists. The referrals are all stored in one table, tbl_Log_Referrals that has a unique PatientID along with another column with the specialty type, i.e. cardiology, dermatology, radiology, etc. The issue is that the referring physician may send out multiple cardiology referrals in order to find the specialist that may be able to see the patient fastest. So I need to be able to count the total specialty referrals, in other words, count all of the patients who received referrals, but only count each specialty once for each patient. Any help would be appreciated.

Answer 1

CREATE TABLE [dbo].[tbl_Log_ReferralLog](

[ID] [int] IDENTITY(1,1) NOT NULL

, [TimeLogged] [datetime] NULL default(getdate())

,[Specialty] varchar NULL

, [MRNumber] char NULL)

Insert into [dbo].tbl_Log_ReferralLog values

('cardiology','1234500000')

,('cardiology','1234500000')

,('cardiology','1234500000')

,('cardiology','1234500001')

,('cardiology','1234500002')

Select *

,dense_rank() Over(Partition by [Specialty] Order by [MRNumber]) dnk

from [dbo].[tbl_Log_ReferralLog]

drop table [dbo].[tbl_Log_ReferralLog]

Answer 2

SELECT PatientID, SpecialityType, COUNT(*)
FROM  btl_LOG
GROUP BY PatientID, SpecialityType, 

This is quite much a guess from your verbal description. We usually recommend that for a question like this that you post the CREATE TABLE statement for your table, preferably simplified to focus on the main problem, together with INSERT statements with sample data. We also need the expected results given that sample data. This helps to clarify your verbal description and makes it easy to copy and paste into a query window to develop a tested solution.