Filtering Duplicates

Kmcnet 701 Reputation points

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.

A set of technologies in the .NET Framework for building web applications and XML web services.
4,282 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points

    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






    Select *

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

    from [dbo].[tbl_Log_ReferralLog]

    drop table [dbo].[tbl_Log_ReferralLog]

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    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.