September 11, 2009 at 9:21 am
I have a table to that is updated from a history table. The update happens because on a join of tables linked by patient id and order number shown below. In a perfect world this works.
update
pharm.dbo.cms_dcmedorderimport
set cms_dcmedorderimport.rxno =
h.rxno,
cms_dcmedorderimport.rono =
h.rono
from rx..hrxs h
where cms_dcmedorderimport.cms_order_number =
h.orderid
and cms_dcmedorderimport.patientid = h.patid
Now the complex part, and orderid and a patient id are only unique within a group of locations. Both of the tables ablove have a 3rd field which is a facility id which joins to a facility table. And since some of these patients can move around within a location there is no 1 to 1 match for the facility id, just that the facility is part of that contract.
On the facility table there is a contractid if which several facilities will belong to. So I need my update where clause to also include to make sure that the orderid and patientid for the first table (cms_dcmedorderimport) link to the hrxs table and that for both tables the contractid would be the same
September 11, 2009 at 9:32 am
A little hard to help you. It would help if you could provide the DDL for the tables, sample data that is representative (made up) of your actual data, expected results based on this data.
As this appears to be medical related, I will emphasize using data that you make up but is representative of your problem.
If you need some help with this request, please read the first article in my signature block regarding asking for assistance.
September 11, 2009 at 2:39 pm
No problem I get the HIPPA world.
patient table
patid
facid
hrxs table
patid
facid
rxno
orderid
facilities table
facid
contractid
in patients you could have
patid facid
122 100
122 234
facilities
facid contract
122 OKL
234 MIC
100 OKL
HRXS
patid facid orderid rxno
122 122 100 555
122 234 100 444
update
pharm.dbo.cms_dcmedorderimport
set cms_dcmedorderimport.rxno =
h.rxno,
cms_dcmedorderimport.rono =
h.rono
from rx..hrxs h
where cms_dcmedorderimport.cms_order_number =
h.orderid
and cms_dcmedorderimport.patientid = h.patid
September 11, 2009 at 2:58 pm
Please read the article I suggested. Unless I want to take the time to write CREATE TABLE and INSERT statements, there really isn't much in your latest post that I can use directly.
September 12, 2009 at 11:28 am
If I've pieced this all together it looks like you want update a table using orderid and patientid however this give erroneous results due to the fact that combination (orderid/patientid) is only unique under a facilityid. In addition the facilityid has it's own issues because a patient could be moved to other facilities under a contractid.
So you couldn't you add the contractid to both your dcmedorderimport and hrxs tables, thereby updating the correct records?
If that's not a possibility, then you could cross join the facilities table to itself where the contractids are equal get all valid intracontract facility move combinations and add that table to your update statement.
With little to go on in the way of table structures and no final values of what you're expecting from your update statement; this is what I came up with.
------------------------------------------------- setup
DECLARE @patient TABLE
(
patidINT
,facidINT
)
INSERT INTO @patient (patid, facid)
SELECT 122, 100
UNION ALL
SELECT 122, 234
DECLARE @facilities TABLE
(
facidINT
,contractidVARCHAR(30)
)
INSERT INTO @facilities (facid, contractid)
SELECT 122, 'OKL'
UNION ALL
SELECT 234, 'MIC'
UNION ALL
SELECT 100, 'OKL'
DECLARE @dcmorderimport TABLE
(
orderid INT
,patid INT
,facid INT
,rxno INT
,rono INT
)
INSERT INTO @dcmorderimport (patid, facid, orderid, rxno, rono)
SELECT 122, 100, 100, 222, 100 --patient 122 @ facility 100 (part of contract 'okl') order 100 prescription number 222 room number 100
UNION ALL
SELECT 122, 234, 100, 333, 102 --patient 122 @ facility 234 order 100 presecription number 333 room number 102
DECLARE @hrxs TABLE
(
patid INT
,facid INT
,rxno INT
,orderid INT
,rono INT
)
INSERT INTO @hrxs (patid, facid, orderid, rxno, rono)
SELECT 122, 122, 100, 555, 101 --change facility to 122 change roomnumber to 101 and rxno to 555 in reference to dcorderimport
UNION ALL
SELECT 122, 234, 100, 444, 101 --change roomnumber to 101 and rxno to 444 in reference to dcorderimport
----------------------------------------------Work
SELECT
*
FROM
@dcmorderimport -- initial values
;WITH allfacilitiespercontractxreference(contractid,facid1,facid2)
AS (SELECT a.contractid,
a.facid AS facid1,
b.facid AS facid2
FROM @facilities a
CROSS JOIN @facilities b
WHERE a.contractid = b.contractid)
UPDATE @dcmorderimport
SET rxno = h.rxno,
rono = h.rono,
facid = h.facid --Update to new facid?
FROM @dcmorderimport a
JOIN @hrxs h
ON a.orderid = h.orderid
AND a.patid = h.patid
JOIN allfacilitiespercontractxreference facxref
ON facxref.facid1 = h.facid
AND facxref.facid2 = a.facid
SELECT
*
FROM
@dcmorderimport -- updated values
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply