November 3, 2008 at 7:38 am
The issue isn't the cursor itself, it's the fact that you've got NULL = NULL at the end. NULL doesn't = NULL, NULL IS NULL. The update statement at the end of your cursor just needs to be modified to use a different piece of criteria, other than the ScheduleID which won't exist.
I didn't see anywhere where I was using a criteria of "= NULL" I only posted SP parameters
that were = NULL and the 'ScheduleID IS NULL' was supposed to be 'EncounterID IS NULL'.
Anyway, this looks like it will work but I need it to update all ptSchedule records that have the same MRN for the selected date of service with the same EncounterID. Can you recommend any changes? I'd love to be able to group by MRN/StartDtTm in the temp table but it isn't letting me do that.
CREATE TABLE #MySchedule (
[ScheduleID] [int]
, [MRN] [varchar] (25)
, [ResourceID] [int]
, [StartDtTm] [datetime]
)
INSERT INTO #MySchedule
SELECT ScheduleID, MRN, ResourceID, StartDtTm
FROM ptSchedule
WHERE EncounterID IS NULL AND StartDtTm > '10/20/2008'
GO
DECLARE @ScheduleID int
DECLARE @MRN1 varchar(25)
DECLARE @MRN2 varchar(25)
DECLARE @ResourceID1 int
DECLARE @StartDtTm1 datetime
DECLARE @StartDtTm2 datetime
DECLARE ptSchedule_cursor CURSOR FAST_FORWARD
FOR SELECT * FROM #MySchedule
OPEN ptSchedule_cursor
FETCH NEXT FROM ptSchedule_cursor INTO @ScheduleID, @MRN1, @ResourceID1, @StartDtTm1
WHILE @@FETCH_STATUS = 0
BEGIN
IF RTRIM(@MRN2) <> RTRIM(@MRN1)
BEGIN
-- Get the next bill number
declare @p3 nvarchar(1000)
exec rr_Get_coPracticeSettings_Counter @Category=N'ENCOUNTERBILLNUMBER',@NumLength=0,@ID=@p3 output
-- Insert the Encounter
declare @p1 int
exec rr_Insert_ptEncounter @EncounterID=@p1 output
, @BillNumber=@p3, @ClaimNumber=NULL, @MRN=@MRN1, @SubscriberContactID=0
, @LocationID=1, @AuthID=0, @BillerUserID=0, @PostingDtTm=NULL, @SiteID=@ResourceID1
, @DeptID=0, @InsuranceID=0, @RefPhysID=0, @ProviderID=0, @DateOfService=@StartDtTm1
, @DateOfIllness=NULL, @TotalAmt=0, @PaidAmt=0, @OpenAmt=0, @PaidInFull=0
, @ClaimSubmissionDtTm=NULL, @ClaimStatus=0, @Diag1=NULL, @Diag2=NULL, @Diag3=NULL
, @Diag4=NULL, @StatementMessage1=NULL, @StatementMessage2=NULL, @BillComment1=NULL
, @BillComment2=NULL, @AdmitDtTm=NULL, @DischargeDtTm=NULL, @AutoAccidentRelated=0
, @EmploymentAccidentRelated=0, @OtherAccidentRelated=0, @State=NULL, @PreAuthNumber=NULL
, @AcceptAssignment=0, @Errors=NULL, @FirstOnsetDtTm=NULL, @BucketID=0, @CreatedByUserID=138
UPDATE ptSchedule SET EncounterID = @p1 WHERE MRN = @MRN1 AND StartDtTm = @StartDtTm1
SET @MRN2 = @MRN1
END
FETCH NEXT FROM ptSchedule_cursor INTO @ScheduleID, @MRN1, @ResourceID1, @StartDtTm1
END
CLOSE ptSchedule_cursor
DEALLOCATE ptSchedule_cursor
DROP TABLE #MySchedule
November 3, 2008 at 7:49 am
The commented out criteria in your original cursor declaration was
-- ScheduleId IS NULL AND StartDtTm > '10/20/2008'
This would give you a set where all ScheduleID's were null. Then, at the bottom, you were trying to update where ScheduleID = @ScheduleID. IE. value = NULL.
UPDATE ptSchedule SET EncounterID = @p1 WHERE ScheduleID = @ScheduleID
Maybe that was a typo on your part, as I see that now you're using EncounterID IS NULL, but that's what we were talking about. You may think it was overcritical, but I thought it was a very good catch. That's exactly the type of overlooked issue that very often breaks this stuff.
November 3, 2008 at 7:54 am
Yes, I pointed it out in my last post, however, why would commented out code be relevant?
Did you get a chance to look at my last post to see if the code looks OK?
I ran it and it didn't update anything.
November 3, 2008 at 8:06 am
Yes, I pointed it out in my last post, however, why would commented out code be relevant?
I either overlooked that or it wasn't there when I initially read the reply... but in either case... by that same token, why would commented out code be there if it wasn't relevant? 😉
The way I'd troubleshoot that at this point is to make sure that it is passing in everything you think it should be passing in. IE. Instead of exec'ing that, insert all of that into a string and print the string for every cursor iteration. That way you can make sure all the values are getting sent in and the cursor is doing exactly what you think it should be doing.
November 3, 2008 at 8:57 am
Ok, here's a modification to the last script.
There is only one problem, however, it only updates approx 200 rows at a time.
It won't do all of them.
Any ideas?
CREATE TABLE #MySchedule (
[ScheduleID] [int]
, [MRN] [varchar] (25)
, [ResourceID] [int]
, [StartDtTm] [datetime]
)
INSERT INTO #MySchedule
SELECT ScheduleID, MRN, ResourceID, StartDtTm
FROM ptSchedule
WHERE EncounterID IS NULL
AND StartDtTm > '10/20/2008'
DECLARE @ScheduleID int
DECLARE @MRN1 varchar(25)
DECLARE @MRN2 varchar(25)
DECLARE @ResourceID1 int
DECLARE @StartDtTm1 datetime
DECLARE @StartDtTm2 datetime
DECLARE @SQL1 varchar(max)
DECLARE ptSchedule_cursor CURSOR FAST_FORWARD
FOR SELECT * FROM #MySchedule
OPEN ptSchedule_cursor
FETCH NEXT FROM ptSchedule_cursor INTO @ScheduleID, @MRN1, @ResourceID1, @StartDtTm1
SET @MRN2 = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF RTRIM(@MRN2) <> RTRIM(@MRN1)
BEGIN
-- Get the next bill number
DECLARE @p3 nvarchar(1000)
exec rr_Get_coPracticeSettings_Counter @Category=N'ENCOUNTERBILLNUMBER',@NumLength=0,@ID=@p3 output
-- Insert the Encounter
DECLARE @p1 int
exec rr_Insert_ptEncounter @EncounterID=@p1 output
, @BillNumber=@p3, @ClaimNumber=NULL, @MRN=@MRN1, @SubscriberContactID=0
, @LocationID=1, @AuthID=0, @BillerUserID=0, @PostingDtTm=NULL, @SiteID=@ResourceID1
, @DeptID=0, @InsuranceID=0, @RefPhysID=0, @ProviderID=0, @DateOfService=@StartDtTm1
, @DateOfIllness=NULL, @TotalAmt=0, @PaidAmt=0, @OpenAmt=0, @PaidInFull=0
, @ClaimSubmissionDtTm=NULL, @ClaimStatus=0, @Diag1=NULL, @Diag2=NULL, @Diag3=NULL
, @Diag4=NULL, @StatementMessage1=NULL, @StatementMessage2=NULL, @BillComment1=NULL
, @BillComment2=NULL, @AdmitDtTm=NULL, @DischargeDtTm=NULL, @AutoAccidentRelated=0
, @EmploymentAccidentRelated=0, @OtherAccidentRelated=0, @State=NULL, @PreAuthNumber=NULL
, @AcceptAssignment=0, @Errors=NULL, @FirstOnsetDtTm=NULL, @BucketID=0, @CreatedByUserID=138
UPDATE ptSchedule SET EncounterID = @p1 WHERE MRN = @MRN1 AND StartDtTm = @StartDtTm1
SET @MRN2 = @MRN1
END
FETCH NEXT FROM ptSchedule_cursor INTO @ScheduleID, @MRN1, @ResourceID1, @StartDtTm1
END
CLOSE ptSchedule_cursor
DEALLOCATE ptSchedule_cursor
DROP TABLE #MySchedule
November 3, 2008 at 9:43 am
I keep getting
[font="Courier New"]The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
[/font]
Because the call:
exec rr_Get_coPracticeSettings_Counter @Category=N'ENCOUNTERBILLNUMBER',@NumLength=0,@ID=@p3 output
is returning a value to the results grid.
How to get rid of that?
The first time I run the script, I have 4682 records
where Encounterid is null and StartDtTm > '10/20/2008'
After running the script, I have 2086 records.
If I run it again, I have 922 records.
IF I run it a third time, I have 503 rows.
Any ideas?
November 3, 2008 at 9:54 am
When you say, 'I have xxxx records', do you mean that this is the count of rows that match the IS NULL criteria in your cursor?
Any ideas? - it's hard to say without knowing what else your SPs are doing. If your code is written correctly, you should only have to run it once for it to process all of the rows that match the cursor criteria. There may be something happening in your SPs or in a trigger somewhere that is causing your cursor to not process everything in one run.
Just curious, why populate a temp table and then cursor through the temp table? I thought your goal here was to come up with a 'quick' way to do this? You don't need the temp table.
So how many rows does this query produce:
SELECT ScheduleID, MRN, ResourceID, StartDtTm
FROM ptSchedule
WHERE EncounterID IS NULL
AND StartDtTm > '10/20/2008'
November 3, 2008 at 10:05 am
John Rowan (11/3/2008)
When you say, 'I have xxxx records', do you mean that this is the count of rows that match the IS NULL criteria in your cursor?
Yes
John Rowan (11/3/2008)
Any ideas? - it's hard to say without knowing what else your SPs are doing. If your code is written correctly, you should only have to run it once for it to process all of the rows that match the cursor criteria. There may be something happening in your SPs or in a trigger somewhere that is causing your cursor to not process everything in one run.
Without having "step by step" debugging for SQL code, it will be very difficult to determine if something in the SP is causing the cursor to not process everything. I don't know enough about cursors to determine what even could cause that to happen.
All I know is that I need to get this thing working fast because we need these Encounters created and the ID's in the ptSchedule table.
John Rowan (11/3/2008)
Just curious, why populate a temp table and then cursor through the temp table? I thought your goal here was to come up with a 'quick' way to do this? You don't need the temp table.
I guess I *could* take out the temp table and work on the cursor only but I thought the comment on updating the row that was IN the cursor was to not do it and that is the only way I knew how. Using a CTE won't work at all.
John Rowan (11/3/2008)
So how many rows does this query produce:
4686 before the script is run, just as I said.
November 3, 2008 at 10:31 am
4686 before the script is run, just as I said
Then your code should go through 4686 iterations, one for each row. If you are left with rows with NULL EncounterIDs at the end, something is not right.
What is the purpose of this code:
IF RTRIM(@MRN2) <> RTRIM(@MRN1)
Do you realize that you are not handling NULLs? If one of the values are NULL, your main block of code will not execute.
November 3, 2008 at 11:33 am
IF RTRIM(@MRN2) <> RTRIM(@MRN1)
keep from updating the same MRN on the same DOS.
Each MRNs should have only one encounter record for each dateofservice.
Hmmm, never thought about that one.
Let me make a slight mod and get back..
Thanks for the heads up.
November 3, 2008 at 12:28 pm
Made this modification:
INSERT INTO #MySchedule
SELECT ScheduleID, MRN, ResourceID, StartDtTm
FROM ptSchedule
WHERE EncounterID IS NULL
AND MRN IS NOT NULL
AND StartDtTm > '10/20/2008'
The number of records fell to 4672 but after running the first time, there are still
2076 rows with NULL EncounterIDs
November 3, 2008 at 12:38 pm
OK, then let's build out a test case to reproduce the problem. Can you post some sample data of rows that were successfully updated and some that were not using the following format:
DECLARE @ptSchedule TABLE (ScheduleID int, MRN varchar(25), ResourceID int, StartDtTm datetime)
INSERT INTO @ptSchedule
SELECT .......... UNION ALL
SELECT .......... UNION ALL
SELECT ...........
November 3, 2008 at 12:49 pm
what do you mean here:
SELECT .......... UNION ALL
SELECT .......... UNION ALL
SELECT ...........
I don't know what to put as the ...........
November 3, 2008 at 12:51 pm
I'm not sure if I can post a complete set of test data.
There are 4076 records from ptSchedule and 9359 records from ptEncounter that would
have to be de-identified to post.
November 3, 2008 at 2:15 pm
Right. Just put together a combination of handful of the rows that worked and a handful of rows that the update missed.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply