What I'm trying to accomplish here is to detect the instances where the same rep/hcp_cid combination appears at least twice in this violations table to flag the rep for an audit.

Table definition is as follows:

CREATE TABLE [dbo].[SE_Violation_Detection](

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

[Doc_Num] [nvarchar](20) NULL,

[Call_Date] [datetime] NULL,

[Create_Date] [datetime] NULL,

[Call_Update_Date] [datetime] NULL,

[Call_Status] [nvarchar](30) NULL,

[HCP_Addr1] [nvarchar](100) NULL,

[HCP_Addr2] [nvarchar](64) NULL,

[HCP_City] [nvarchar](50) NULL,

[HCP_State] [nvarchar](2) NULL,

[HCP_Zip] [nvarchar](50) NULL,

[HCP_Country] [nvarchar](50) NULL,

[HCP_LID] [numeric](18, 0) NULL,

[Rep_Name] [nvarchar](100) NULL,

[Rep_ID] [nvarchar](9) NULL,

[Selling_Face] [nvarchar](50) NULL,

[Terr_ID] [nvarchar](50) NULL,

[Acct_CID] [numeric](18, 0) NULL,

[Acct_Name] [nvarchar](100) NULL,

[Order_Shipment_Conf_Date] [datetime] NULL,

[Ref_Num] [nvarchar](20) NULL,

[Point_Order_Number] [nvarchar](20) NULL,

[Restriction_Name] [nvarchar](100) NULL,

[Restriction_Type_Desc] [nvarchar](100) NULL,

[Violation_Dect_Date] [datetime] NULL,

[Email_Sent_Date] [datetime] NULL,

[HCP_CID] [numeric](18, 0) NULL,

[HCP_FName] [nvarchar](50) NULL,

[HCP_LName] [nvarchar](50) NULL,

[HCP_Specialty] [nvarchar](50) NULL,

[HCP_Specialty_Code] [nvarchar](10) NULL,

[HCP_Prof_Desig] [nvarchar](50) NULL,

[HCP_DEA_Number] [nvarchar](20) NULL,

[HCP_DEA_Exp_Date] [datetime] NULL,

[HCP_St_License] [nvarchar](25) NULL,

[HCP_St_License_Exp_Date] [datetime] NULL,

[S_P] [nvarchar](1) NULL,

[Prod_NDC11] [nvarchar](11) NULL,

[Prod_Description] [nvarchar](100) NULL,

[Quantity] [int] NULL,

[Quantity_Requested] [int] NULL,

[Violation_Flag] [nvarchar](1) NULL,

[Trans_DateTime] [datetime] NULL,

[Import_Datetime] [datetime] NULL CONSTRAINT [DF_SE_Violation_Detection_Import_Datetime] DEFAULT (getdate())

) ON [PRIMARY]

An extract of the table data is attached.

Based on a quick analysis of the data, Bonnie Collins has 94 instances, Robert Yachini doesn't have any, Shabnam Elyaszadeh has 9, and Shelly Prather has 3 (just to name a few).

I've also tried this other query, but all this does is bring back all reps that have more than one entry in the table. It doesn't filter the records according to the business rules.

select rep_id,rep_name,hcp_cid,hcp_fname, hcp_lname,count(*) from SE_Violation_Detection group by rep_id,rep_name,hcp_cid,hcp_fname, hcp_lname

having count(*)>1