I'm going to post the code but first im going to explain what im trying to accomplish through pseudo code.
I'm inserting duplicated records in a temporary table. Then I wanted to examine each record in that table individually to do a select that matches that record and based off that, mark a field final for all the records ('Y', 'N', 'E'). To achieve this I've used 2 cursors and its awful. It's awful because its a lot of hard to read, nasty code and it also make my query take over an hour to run. Is there anyway to make it run better with selects/updates?
Following is the code:
declare @hic [nvarchar](255)
declare @oscar [float]
declare @typecode [float]
declare @fromdate [datetime]
declare @thrudate [datetime]
declare @claimcode [float]
declare @claimeffdate [datetime]
declare @id [int]
declare @finalhic [nvarchar](255)
declare @finaloscar [float]
declare @finaltypecode [float]
declare @finalfromdate [datetime]
declare @finalthrudate [datetime]
declare @finalclaimcode [float]
declare @finalclaimeffdate [datetime]
declare cur1 CURSOR LOCAL for
select
[HIC #],
[Provider Oscar #],
[Claim Type Code],
[Claim From Date],
[Claim Thru Date],
[Claim Adjustment Type Code] from #tmp_hic_cancels
open cur1
fetch next from cur1 into @hic, @oscar, @typecode, @fromdate, @thrudate, @claimcode
while @@FETCH_STATUS = 0 BEGIN
--select @hic, @oscar, @typecode, @fromdate, @thrudate, @claimcode
begin
--typecode = 10
if @typecode = 10
BEGIN
insert into #tmp_hic_cancel_batch
select
[ID],
[HIC #],
[Provider Oscar #],
[Claim Type Code],
[Claim From Date],
[Claim Thru Date],
[Claim Adjustment Type Code],
[Claim Effective Date]
from [ACO].[dbo].['PA_Header_Temp']
where [HIC #] = @hic
and [Claim Type Code] = @typecode
and [Provider Oscar #] = @oscar
and [Claim From Date] = @fromdate
--Mark final based on claimcode
DECLARE Cur2 CURSOR FOR
select
[ID],
[HIC #],
[Provider Oscar #],
[Claim Type Code],
[Claim From Date],
[Claim Thru Date],
[Claim Adjustment Type Code],
[Claim Effective Date]
from #tmp_hic_cancel_batch
OPEN Cur2;
FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate;
WHILE @@FETCH_STATUS = 0
--BEGIN CUR2
BEGIN
--IF A 2 EXISTS IN BATCH, perform these operations
if (EXISTS (select
[Claim Adjustment Type Code]
from [ACO].[dbo].['PA_Header_Temp']
where [HIC #] = @finalhic
and [Provider Oscar #] = @finaloscar
and [Claim Type Code] = @finaltypecode
and [Claim From Date] = @finalfromdate
and [Claim Adjustment Type Code] = @finalclaimcode
and [Claim Adjustment Type Code] = 2))
BEGIN
--MARK FINAL CODES BASED ON CLAIM CODES
if @finalclaimcode = 2
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
, 'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
else
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
END
--IF NO 2 EXISTS IN BATCH
ELSE
BEGIN
if @finalclaimcode = 1
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
END
--END IF NO 2 EXISTS IN BATCH
FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
END;
--END CUR2
CLOSE Cur2;
DEALLOCATE Cur2;
END--END typecode = 10
--else typecode = 40...
else
BEGIN
insert into #tmp_hic_cancel_batch
select
[ID],
[HIC #],
[Provider Oscar #],
[Claim Type Code],
[Claim From Date],
[Claim Thru Date],
[Claim Adjustment Type Code],
[Claim Effective Date]
from [ACO].[dbo].['PA_Header_Temp']
where [HIC #] = @hic
and [Provider Oscar #] = @oscar
and [Claim Type Code] = @typecode
and [Claim From Date] = @fromdate
and [Claim Thru Date] = @thrudate
and [Claim Adjustment Type Code] = @claimcode
--Mark final based on claimcode
DECLARE Cur2 CURSOR FOR
select
[ID],
[HIC #],
[Provider Oscar #],
[Claim Type Code],
[Claim From Date],
[Claim Thru Date],
[Claim Adjustment Type Code],
[Claim Effective Date]
from #tmp_hic_cancel_batch
OPEN Cur2;
FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
WHILE @@FETCH_STATUS = 0
--BEGIN CUR2
BEGIN
--IF A 2 EXISTS IN BATCH, perform these operations
if (EXISTS (select
[Claim Adjustment Type Code]
from [ACO].[dbo].['PA_Header_Temp']
where [HIC #] = @finalhic
and [Claim Type Code] = @finaltypecode
and [Provider Oscar #] = @finaloscar
and [Claim From Date] = @finalfromdate
and [Claim Thru Date] = @finalthrudate
and [Claim Adjustment Type Code] = @finalclaimcode
and [Claim Adjustment Type Code] = 2))
BEGIN
--MARK FINAL CODES BASED ON CLAIM CODES
if @finalclaimcode = 2
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
else
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
END
--IF NO 2 EXISTS IN BATCH
ELSE
BEGIN
if @finalclaimcode = 1
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
else
begin
insert into #tmp_hic_final
select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
,'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
end
END
--END IF NO 2 EXISTS IN BATCH
FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
END;
--END CUR2
CLOSE Cur2;
DEALLOCATE Cur2;
END
--clears temp table for next batch
delete from #tmp_hic_cancel_batch
fetch next from cur1 into @hic, @oscar, @typecode, @fromdate, @thrudate, @claimcode
END
close cur1
deallocate cur1
Cursors are the very problem, as you have surmised. They are terribly inefficient and only appropriate in very limited conditions. For large scale processing, like this seems to be doing, with even nested operations, cursors are never a good solution.
You will need to go through the whole procedure and extract each bit out of the cursor. I will give you the first one, and that is why I am thinking of this as an answer and not just a comment.
The first insert that happens inside the first cursor, could be extracted to just before the whole procedure starts, and it would end up looking like this:
insert into #tmp_hic_cancel_batch
select PAHT.[ID],
PAHT.[HIC #],
PAHT.[Provider Oscar #],
PAHT.[Claim Type Code],
PAHT.[Claim From Date],
PAHT.[Claim Thru Date],
PAHT.[Claim Adjustment Type Code],
PAHT.[Claim Effective Date],
PAHT.[Current ClaimID],
PAHT.[Claim Bill Facility Type Code],
PAHT.[Claim Bill Classification Code],
PAHT.[Principal Diagnosis Code],
PAHT.[Admitting Diagnosis Code],
PAHT.[Claim Medicare Non Payment Reason Code],
PAHT.[Claim Payment Amount],
PAHT.[Claim NCH Primary Payer Code],
PAHT.[FIPS state Code],
PAHT.[Bene Patient Status Code],
PAHT.[Diagnosis Related Group Code],
PAHT.[Claim Outpatient Service Type Code],
PAHT.[Facility Provider NPI #],
PAHT.[Operating Provider NPI #],
PAHT.[Attending provider NPI #],
PAHT.[Other Provider NPI #],
PAHT.[Claim IDR Load Date],
PAHT.[Bene Equitable BIC HICN #],
PAHT.[Claim Admission Type Code],
PAHT.[Claim Admission Source Code],
PAHT.[Claim Bill Frequency Code],
PAHT.[Claim Query Code],
from [ACO].[dbo].['PA_Header_Temp'] PAHT
inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
PAHT.[Claim Type Code] = THC.[Claim Type Code] and
PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
PAHT.[Claim From Date] = THC.[Claim From Date]
where PAHT.[Claim Type Code] = 10
You would then have to move on to the next bit inside the next cursor, and extract it the same way, changing a cursor operation into a combination of selects with the appropriate joins.
You would probably find that the whole thing reduces to a couple of queries. Or, if one knew enough about the specifics of the problem, one could come up with those queries straight out. But it would take a few hours to do this for the whole procedure, so I am not going to attempt the whole thing. Also, I am fairly sure the above is correct, but there is no way for me to test it, so...
Yes, this can be made a LOT faster and more efficient. This can be done with just queries (selects, inserts, updates), an NO cursors. But there is no way you are going to get someone to do the whole thing for you for free.