September 8, 2008 at 11:06 am
In the code below, Comment is coming up NULL even though we have data being returned
from *some* of the fields needed to build the Comment. We have tracked it down to
there not being any matching records in the LynxBilling table. How would I build the
comment even if there are no matching records in the LynxBilling table?
Here's resulting data from the code below:
2008-08-20 00:00:00.000, 51, 0, 277494, J1100, INFUSIONDRUG, NULL, NULL, IV, NULL, NULL, NULL, 4, mg, NULL
2008-08-20 00:00:00.000, 51, 0, 277495, J1200, INFUSIONDRUG, NULL, NULL, IV, NULL, NULL, NULL, 25, mg, NULL
2008-08-20 00:00:00.000, 51, 0, 277496, J1752, INFUSIONDRUG, NULL, NULL, IV, NULL, NULL, NULL, 100, mg, NULL
Script:
DECLARE @MRN varchar(25)
DECLARE @dos datetime
DECLARE ChemoOrders_Cursor CURSOR FOR
SELECT DISTINCT MRN, DateofService FROM ptChemoOrders;
OPEN ChemoOrders_Cursor;
FETCH NEXT FROM ChemoOrders_Cursor INTO @MRN, @dos
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print 'Inserting records into ptEncounterDetailsCharges from ptChemoOrderDetails (1)
INSERT INTO ptEncounterDetailsCharges
SELECT
o.DateOfService AS [DOS]
, o.MRN AS [MRN]
, 0 AS [ChargeTypeCode]
, d.LineID AS [ChargeSourceID]
, m.CPT AS [ChargeCode]
, 'INFUSIONDRUG' AS [ChargeSource]
--======================================
-- These columns are included just to show the data
-- that is supposed to build the Comment
, d.StartTime
, d.StopTime
, d.Push
, l.Unit as LUnit
, l.DrugDescription
, d.AdminDetails
, d.Dosage
, d.Unit as DUnit
--==========================
, '1. ' + ISNULL(d.StartTime,'?') + ' - '
+ ISNULL(d.StopTime,'?') + ' '
+ ISNULL(d.Push,'') + char(13) + char(10) +
'2. ' + CAST(ISNULL(l.Unit, 0.00) AS varchar)
+ ' ' + ISNULL(l.DrugDescription, '?') + char(13) + char(10) +
'3.' + d.AdminDetails + char(13) + char(10) +
'4.' + d.Dosage + ' ' + d.Unit
AS [Comment]
FROM ptChemoOrders o
INNER JOIN ptChemoOrderDetails d ON o.OrderID = d.OrderID
INNER JOIN coRegimenMeds m ON d.MedID = m.ID
FULL OUTER JOIN LYNXBilling l ON m.CPT = l.CPT
AND o.DateOfService = l.DateOfService
AND o.MRN = l.PID
LEFT OUTER JOIN coChargeCodes c ON c.ChargeCode = m.CPT
WHERE (o.MRN = @MRN)
AND (o.Deleted = 0 OR o.Deleted IS NULL)
AND (d.Deleted = 0 OR d.Deleted IS NULL)
AND (d.Given = 0)
AND o.DateOfService = @dos
AND ((m.DilutionMedID IS NULL) OR (m.DilutionMedID = 0))
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
FETCH NEXT FROM ChemoOrders_Cursor INTO @MRN, @dos
END;
CLOSE ChemoOrders_Cursor;
DEALLOCATE ChemoOrders_Cursor;
So here's what I was expecting for the first record:
1. ? - ? IV
2. 0.00?
3.
4. 4 mg
September 8, 2008 at 11:19 am
Could you provide the DDL for the tables (CREATE TABLE statements, if it is hugh just the key fields used in the query plus any indexed fields), some sample data (INSERT statements to load the tables, so we can cut, paste, and run to load the tables you provide), and then expected results based on the sample data.
Also, I think this can be done without using a cursor.
😎
September 8, 2008 at 11:50 am
First, why a Full Outer Join? I'd make it a left join. That won't affect your issue, but it will improve performance.
Second, why a cursor? Why not a simple Insert...Select?
Try this, see if it helps find what's wrong:
-- Print 'Inserting records into ptEncounterDetailsCharges from ptChemoOrderDetails (1)
--INSERT INTO ptEncounterDetailsCharges -- Commented out for testing
SELECT
o.DateOfService AS [DOS]
, o.MRN AS [MRN]
, 0 AS [ChargeTypeCode]
, d.LineID AS [ChargeSourceID]
, m.CPT AS [ChargeCode]
, 'INFUSIONDRUG' AS [ChargeSource]
--======================================
-- These columns are included just to show the data
-- that is supposed to build the Comment
, d.StartTime
, d.StopTime
, d.Push
, l.Unit as LUnit
, l.DrugDescription
, d.AdminDetails
, d.Dosage
, d.Unit as DUnit
--==========================
, '1. ' + ISNULL(d.StartTime,'?') + ' - '
+ ISNULL(d.StopTime,'?') + ' '
+ ISNULL(d.Push,'') + char(13) + char(10) +
'2. ' + CAST(ISNULL(l.Unit, 0.00) AS varchar)
+ ' ' + ISNULL(l.DrugDescription, '?') + char(13) + char(10) +
'3.' + d.AdminDetails + char(13) + char(10) +
'4.' + d.Dosage + ' ' + d.Unit
AS [Comment]
FROM ptChemoOrders o
INNER JOIN ptChemoOrderDetails d ON o.OrderID = d.OrderID
INNER JOIN coRegimenMeds m ON d.MedID = m.ID
FULL OUTER JOIN LYNXBilling l ON m.CPT = l.CPT
AND o.DateOfService = l.DateOfService
AND o.MRN = l.PID
LEFT OUTER JOIN coChargeCodes c ON c.ChargeCode = m.CPT
WHERE (o.Deleted = 0 OR o.Deleted IS NULL)
AND (d.Deleted = 0 OR d.Deleted IS NULL)
AND (d.Given = 0)
AND ((m.DilutionMedID IS NULL) OR (m.DilutionMedID = 0))
and l.drugdescription is null -- testing only
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 8, 2008 at 12:13 pm
GSquared,
Looks good except I need to filter by the o.MRN and o.DateOfService.
Also many records come over with Comments and several others have NULL Comments.
The particular DOS that I am querying by does have the NULL Comments.
September 8, 2008 at 12:22 pm
Lynn Pettis (9/8/2008)
Also, I think this can be done without using a cursor.
😎
It's because we have MRN/DOS combinations that we have to do this for.
It is to populate an intermediate table from legacy data.
September 8, 2008 at 12:28 pm
Still, I think this could be done without a cursor. From the logic in the code provided, I can't see any reason that requires a cursor. A set-based approach has the potential of being much more efficient.
Not having the DDL or sample data for the tables, I don't have the enough to work with to see what could be done.
😎
September 8, 2008 at 2:02 pm
Can you show me what you mean, Lynn?
September 8, 2008 at 2:34 pm
Think when Lynn says:
Not having the DDL or sample data for the tables, I don't have the enough to work with to see what could be done.
she means what is described in the article in my signature block
September 8, 2008 at 2:41 pm
I *know* what was meant concerning that part of the reply.
What I was referring to was to show me how to do it without the cursor,
September 8, 2008 at 3:31 pm
bitbucket (9/8/2008)
Think when Lynn says:Not having the DDL or sample data for the tables, I don't have the enough to work with to see what could be done.
she means what is described in the article in my signature block
He, I am a he.
😎
September 8, 2008 at 3:39 pm
MrBaseball34 (9/8/2008)
I *know* what was meant concerning that part of the reply.What I was referring to was to show me how to do it without the cursor,
I need the DDL and and sample data to show you I mean. A little difficult to show you otherwise.
😎
September 8, 2008 at 9:03 pm
Since I didn't know, I was very careful about how I said what I did in my last post. 😉
September 8, 2008 at 9:34 pm
Okay, I can try and guess at the DDL for the tables, and I "make up" some data, but I have no idea if what I do will be close to what you actually have in your data base.
It would really help if you could provide the DDL for the tables and some sample data to work with, but I will give it a shot.
😎
September 8, 2008 at 10:09 pm
Okay. With no tables or test data, here is something for you to try. Before running the code, comment the INSERT line so that all you are doing is a select.
Let us know how it works (or not).
with ChemoOrders (
MRN,
DOS
) as (
SELECT DISTINCT
MRN,
DateofService
FROM
ptChemoOrders
)
INSERT INTO ptEncounterDetailsCharges
SELECT
o.DateOfService AS [DOS]
, o.MRN AS [MRN]
, 0 AS [ChargeTypeCode]
, d.LineID AS [ChargeSourceID]
, m.CPT AS [ChargeCode]
, 'INFUSIONDRUG' AS [ChargeSource]
--======================================
-- These columns are included just to show the data
-- that is supposed to build the Comment
, d.StartTime
, d.StopTime
, d.Push
, l.Unit as LUnit
, l.DrugDescription
, d.AdminDetails
, d.Dosage
, d.Unit as DUnit
--==========================
, '1. ' + ISNULL(d.StartTime,'?') + ' - '
+ ISNULL(d.StopTime,'?') + ' '
+ ISNULL(d.Push,'') + char(13) + char(10) +
'2. ' + CAST(ISNULL(l.Unit, 0.00) AS varchar)
+ ' ' + ISNULL(l.DrugDescription, '?') + char(13) + char(10) +
'3.' + ISNULL(d.AdminDetails,'') + char(13) + char(10) +
'4.' + d.Dosage + ' ' + d.Unit
AS [Comment]
FROM
ChemoOrders co
INNER JOIN ptChemoOrders o
ON (co.MRN = o.MRN
AND co.DOS = o.DateOfService)
INNER JOIN ptChemoOrderDetails d
ON (o.OrderID = d.OrderID)
INNER JOIN coRegimenMeds m
ON (d.MedID = m.ID)
FULL OUTER JOIN LYNXBilling l
ON (m.CPT = l.CPT
AND o.DateOfService = l.DateOfService
AND o.MRN = l.PID)
LEFT OUTER JOIN coChargeCodes c -- This join does nothing, no fields from
ON (c.ChargeCode = m.CPT) -- from coChargeCodes are even used in the query
WHERE
(o.Deleted = 0 OR o.Deleted IS NULL)
AND (d.Deleted = 0 OR d.Deleted IS NULL)
AND (d.Given = 0)
AND ((m.DilutionMedID IS NULL) OR (m.DilutionMedID = 0))
-- AND (o.MRN = @MRN) -- Commented due to use of CTE
-- AND o.DateOfService = @dos -- Commented due to use of CTE
ORDER BY
co.MRN,
co.DOS;
Edit: I wouldn't be surprised, actually, if the CTE at the beginning couldn't also be removed and simply order the query on the ptChemoOrder fields o.MRN and o.DateOfService.
😎
September 9, 2008 at 9:19 am
MrBaseball34 (9/8/2008)
GSquared,Looks good except I need to filter by the o.MRN and o.DateOfService.
Also many records come over with Comments and several others have NULL Comments.
The particular DOS that I am querying by does have the NULL Comments.
The query you presented at first just has a cursor that goes through the whole table (each distinct pair of values) and then queries that table one set at a time. There aren't any filters in it, at least not that I can see. That's why I didn't filter them.
But that's not the main point of the query I modified. The main point was to see what results it comes up with, what nulls, etc. If you need to filter it, please do so, but run it to see what strings it comes up with.
I'm just trying to break the problem down into smaller parts. That's how I debug.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply