javib (3/22/2013)

---

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).