Store Procedure Regarding @claimTypes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Store Procedure Regarding @claimTypes

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-30 : 12:26:29

I have this procedure below that work well. The only problem I am having is I can't select the two types of Claim Types (HCFA,UB). I can select them separate, but not togethat. I get no data when I do that. Can someone look at my store procedure to let me know what I am doing wrong?

USE [ExecReports]
GO
/****** Object: StoredProcedure [dbo].[P_Historic_TaxID_Report] Script Date: 06/30/2010 12:10:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[P_Historic_TaxID_Report]

@bottomdate datetime,
@topdate datetime,
@claimTypes Varchar (50)

AS

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Historic_TaxID_Report]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Historic_TaxID_Report]

Create Table Historic_TaxID_Report(

[claimTypes] [varchar](4) NOT NULL,
[PROVIDERTAXIDNUMBER] [varchar](9) NULL,
[PROCESSEDDATE] [datetime] NULL,
[clm_att1] [varchar](2) NULL,
[clm_att2] [varchar](2) NULL,
[clm_att3] [varchar](2) NULL,
[clm_att4] [varchar](2) NULL,
[claimnumber1] [varchar](20) NULL,
[CLAIMNUMBER] [varchar](20) NULL,
[CLAIMSTATUS] [varchar](4) NULL,
[TYPEOFBILL] [varchar](3) NULL,
[STATEMENTFROMDATE] [datetime] NULL,
[STATEMENTTODATE] [datetime] NULL,
[TOTALCHARGES] [decimal](10, 2) NULL,
[ALLOWEDCHARGES] [decimal](10, 2) NULL,
[SAVINGS] [decimal](10, 2) NULL,
[PCT of Savings] [decimal](27, 13) NULL,
[ProviderFullName] [varchar](45) NULL,
[PROVIDERSTATE] [varchar](2) NULL,
[ProviderCity] [varchar](30) NULL,
[Providerpostalcode] [varchar](13) NULL,
[PROVIDERCOUNTY] [varchar](25) NULL,
[PROVIDERTAXIDNUMBERKEY] [varchar](9) NULL,
[EMPLOYERGROUPNAME] [varchar](30) NULL,
[CLIENTNAME] [varchar](35) NULL,
[PROVIDERTYPECODE] [varchar](4) NULL,
[ProviderOptios] [varchar](4) NULL,
[EMPLOYERNAME] [varchar](30) NULL,
[PROVIDERGROUPNAME] [varchar](35) NULL,
[PROVIDERID] [varchar](8) NULL,
[MEMBERNUMBER] [varchar](30) NULL,
[LASTNAME] [varchar](20) NULL,
[CLIENTNUMBER] [varchar](17) NULL,
[PATIENTLASTNAME] [varchar](20) NULL,
[PATIENTFIRSTNAME] [varchar](15) NULL,
[clientID] [varchar](8) NULL,
[cli_altid] [varchar](30) NULL,
[clm_adjto] [varchar](8) NULL,
[clm_adjfm] [varchar](8) NULL,
[clm_adjsc] [varchar](2) NULL,
[Pro_County] [varchar](25) NULL,
[New3digitZip] [varchar](3) NULL,
[PrimaryDX] [varchar](6) NULL,
[Specialty] [varchar](3) NULL,
[SpecialtyDescription] [varchar](35) NULL
)

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[#Historic_TaxID_10_Greater]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[#Historic_TaxID_10_Greater]

select * INTO #Historic_TaxID_10_Greater from execreports.dbo.VW_Historic_TaxID_Report
where PROVIDERTAXIDNUMBER in (

Select
PROVIDERTAXIDNUMBER

From execreports.dbo.VW_Historic_TaxID_Report
where [PROCESSEDDATE] >= @bottomdate and [PROCESSEDDATE] <= @topdate and
(claimTypes IN (@claimTypes)) ----'HCFA'
group by PROVIDERTAXIDNUMBER

HAVING COUNT (PROVIDERTAXIDNUMBER) >= 10) and
[PROCESSEDDATE] >= @bottomdate and [PROCESSEDDATE] <= @topdate and
(claimTypes IN (@claimTypes)) ---'HCFA'
ORDER BY PROVIDERTAXIDNUMBER,claimTypes

Insert INTO Historic_TaxID_Report( claimTypes,PROVIDERTAXIDNUMBER,PROCESSEDDATE,clm_att1, clm_att2, clm_att3, clm_att4, claimnumber1,
CLAIMNUMBER,CLAIMSTATUS,TYPEOFBILL,STATEMENTFROMDATE,STATEMENTTODATE,TOTALCHARGES, ALLOWEDCHARGES,
SAVINGS,[PCT of Savings],ProviderFullName,PROVIDERSTATE,ProviderCity,Providerpostalcode,PROVIDERCOUNTY,
PROVIDERTAXIDNUMBERKEY,EMPLOYERGROUPNAME,CLIENTNAME,PROVIDERTYPECODE, ProviderOptios, EMPLOYERNAME,
PROVIDERGROUPNAME,PROVIDERID,MEMBERNUMBER,LASTNAME,CLIENTNUMBER,PATIENTLASTNAME,PATIENTFIRSTNAME,
clientID,cli_altid,clm_adjto,clm_adjfm,clm_adjsc,Pro_County,New3digitZip,PrimaryDX,Specialty,
SpecialtyDescription)

SELECT DISTINCT

claimTypes,
PROVIDERTAXIDNUMBER,
PROCESSEDDATE,
clm_att1,
clm_att2,
clm_att3,
clm_att4,
claimnumber1,
CLAIMNUMBER,
CLAIMSTATUS,
TYPEOFBILL,
STATEMENTFROMDATE,
STATEMENTTODATE,
TOTALCHARGES,
ALLOWEDCHARGES,
SAVINGS,
PCT_of_Savings,
ProviderFullName,
PROVIDERSTATE,
ProviderCity,
Providerpostalcode,
PROVIDERCOUNTY,
PROVIDERTAXIDNUMBERKEY,
EMPLOYERGROUPNAME,
CLIENTNAME,
PROVIDERTYPECODE,
ProviderOptios,
EMPLOYERNAME,
PROVIDERGROUPNAME,
PROVIDERID,
MEMBERNUMBER,
LASTNAME,
CLIENTNUMBER,
PATIENTLASTNAME,
PATIENTFIRSTNAME,
clientID,
cli_altid,
clm_adjto,
clm_adjfm,
clm_adjsc,
Pro_County,
New3digitZip,
PrimaryDX,
Specialty,
SpecialtyDescription

From #Historic_TaxID_10_Greater

----------------------------------------------------------------------

IF object_id('tempdb..#Historic_TaxID_10_Greater') IS NOT NULL
BEGIN
DROP TABLE #Historic_TaxID_10_Greater
END

-------------------------------------------------------

select * from Historic_TaxID_Report
Order by PROVIDERTAXIDNUMBER

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-30 : 12:53:52

I didn't go thru your entire code, but you can't do this
(claimTypes IN (@claimTypes)) ----'HCFA'

if @claimtypes looks something like 'a,b,c,d', you can turn that into a table using a ParseValues function, which you can search this site for, and then join to that table. Also you don't need to put the same thing in your where clause and having clause

Jim

Everyday I learn something that somebody else already knew

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-30 : 13:20:16

I also tried it this way and it still didn't work....

That’s why we are using below to add coats for each string. But still it’s not working
declare @test varchar(50)
set @test = 'HCFA,UB'
Declare @test1 varchar(50)
Declare @test2 varchar(50)
set @test1 = ''''+ SUBSTRING (@test,charindex(',',@test)+1,2)+''''
print @test1
set @test2 = ''''+ SUBSTRING (@test,charindex('H',@test),4)+''''
print @test2
Declare @test3 varchar(50)
set @test3 = @test1+','+@test2
print @test3

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-30 : 13:27:16

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources