March 22, 2013 at 10:08 am
I have this query to express a set of business rules.
To get the information I need, I tried joining the table on itself but that brings back many more records than are actually in the table. The table only has about 1700 records in it but I'm getting these results:
rep_idSingle Practitioner
00718264U8836
00729486S96
00726313T75
01078482M75
01005121V71
00603023J68
Below is the query I've tried. What am I doing wrong?
SELECT DISTINCT a.rep_id, count(*) AS 'Single Practitioner'
FROM SE_Violation_Detection a inner join SE_Violation_Detection b
ON a.rep_id = b.rep_id and a.hcp_cid = b.hcp_cid
group by a.rep_id, a.rep_name
having count(*) >= 2
ORDER BY count(*) DESC
March 22, 2013 at 10:27 am
javib (3/22/2013)
I have this query to express a set of business rules.To get the information I need, I tried joining the table on itself but that brings back many more records than are actually in the table. The table only has about 1700 records in it but I'm getting these results:
rep_id rep_name Single Practitioner
00718264UBONNIE COLLINS 8836
00729486SROBERT YACHINI 96
01078482MSHABNAM ELYASZADEH 75
00726313TSHELLY PRATHER 75
01005121VSTACI HARTMAN 71
00603023JNURIA SWABY 68
Below is the query I've tried. What am I doing wrong?
SELECT DISTINCT a.rep_id, a.rep_name, count(*) AS 'Single Practitioner'FROM SE_Violation_Detection a inner join SE_Violation_Detection b
ON a.rep_id = b.rep_id and a.hcp_cid = b.hcp_cid
group by a.rep_id, a.rep_name
having count(*) >= 2
ORDER BY count(*) DESC
Based on the data displayed above, what are you expecting to be returned?
March 22, 2013 at 10:54 am
Table DDL, sample data and expected results would help us to help you. In the absence of that, what does this return?
SELECT DISTINCT rep_id, hcp_cid
FROM SE_Violation_Detection
John
March 22, 2013 at 11:27 am
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
March 22, 2013 at 11:34 am
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).
I really hope the data you just posted doesn't have any PII. Plus, having taken a quick look, not going to take the time to even use it.
March 22, 2013 at 12:19 pm
New extract without any PII has been posted. Sorry about that. Should have deleted that info before posting.
March 22, 2013 at 3:38 pm
javib (3/22/2013)
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.
We are not psychics here, we don't know your business rules.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply