January 14, 2009 at 9:51 am
We have a table called ptInsurancePlans.
It has columns named MRN (Medical Record Number), CarrierID
GroupID, InsuranceLevel, EffectiveDtTm and ExpirationDtTm
I'd like to know how to query to see if any patient (MRN)
has > 1 record where CarrierID and GroupID match another record
for this MRN and the EffectiveDtTm or ExpirationDtTm match and
show the InsuranceLevel along with the MRN.
January 14, 2009 at 10:04 am
Is this the sort of thing you looking for?
DECLARE @ptInsurancePlans TABLE
(
MRN INT,
CarrierID INT,
GroupID INT,
InsuranceLevel INT,
EffectiveDtTm DATETIME ,
ExpirationDtTm DATETIME
)
INSERT INTO @ptInsurancePlans
SELECT 1,1,1,1,'2008-01-01','2008-01-01' UNION ALL
SELECT 1,2,2,2,'2008-01-01','2008-01-01' UNION ALL
SELECT 1,1,1,1,'2008-01-01','2008-01-01' UNION ALL
SELECT 2,2,2,2,'2007-01-01','2008-01-01' UNION ALL
SELECT 2,1,1,1,'2008-01-01','2007-01-01' UNION ALL
SELECT 2,1,1,1,'2007-01-01','2008-01-01'
SELECT *
FROM @ptInsurancePlans m
INNER JOIN
(SELECT MRN,CarrierID,GroupID
FROM @ptInsurancePlans
GROUP BY MRN,CarrierID,GroupID
HAVING COUNT(*) > 1
) dup
ON dup.MRN = m.MRN
AND dup.CarrierID = m.CarrierID
AND dup.GroupID = m.GroupID
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 14, 2009 at 10:36 am
This worked. Had somebody else from the office work on it. 😉
;WITH ip(MRN, CarrierID, PolicyID, InsuranceLevel,ExpirationDtTm,EffectiveDtTm)
AS (SELECT MRN, CARRIERID, PolicyID, InsuranceLevel,ExpirationDtTm,EffectiveDtTm
FROM ptInsurancePlans where InsuranceLevel = 1 and expirationdttm > getdate())
SELECT p.MRN, p.CarrierID, coi.CarrierName, p.InsuranceLevel
, p.PolicyID, p.ExpirationDtTm, p.EffectiveDtTm, i.carrierid
, i.InsuranceLevel
from ptInsurancePlans p
inner join ip i ON p.MRN = i.MRN
inner join coInsCarriers coi on
coi.CarrierID = p.CarrierID
where p.carrierid = i.carrierid
and p.EffectiveDtTm = i.EffectiveDtTm
and p.ExpirationDtTm = i.ExpirationDtTm
and p.PolicyID = i.PolicyID
and p.InsuranceLevel = 2
and p.expirationdttm > getdate()
January 15, 2009 at 2:16 am
Thanks for the reply with the answer 🙂
Looks like the requirements changed a bit 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply