October 27, 2008 at 9:26 am
MrBaseball34 (10/27/2008)
Good job, SethI fixed the problem with the CoPays by loading them in the CTEs with the
InsPlan stuff I also fixed it to bring over the SubscriberID so I wouldn't have to join ptInsurancePlans and it sped it up considerably.
Good deal. I knew there would be some additional optimization possible, but my laptop battery was dying, so I went ahead and posted it as it was.
I also selected the Descriptions by StartDtTm in the first CTE because I was getting all Descriptions for each patient and I selected DISTINCT descriptions in the second CTE because I was getting dupes.
Heh, oops. I never re-added the date criteria. It did seem odd that you wanted all of their history concatenated into one field. :hehe:
Thanks a million.
You're welcome. Thanks for the feedback on how it worked. If you feel so inclined, post the final query for people who might read this later.
October 27, 2008 at 12:07 pm
Ok, here's the final code:
CREATE PROCEDURE [dbo].[rr_Collect_EncounterReport](
@StartDtTm AS DATETIME)
AS
BEGIN
;WITH UniqueDescript(MRN, Description, StartDtTm, EndDtTm)
AS (SELECT DISTINCT MRN, Description, StartDtTm, EndDtTm
FROM ptSchedule),
XMLDescript (MRN, XMLDesc)
AS (SELECT MRN,
(SELECT DISTINCT [Description] AS 'data()'
FROM UniqueDescript U2
WHERE U2.MRN = U.MRN
AND U2.StartDtTm BETWEEN @StartDtTm AND DATEADD(DAY, 1, @StartDtTm )
FOR XML PATH('')) XMLDesc --Thanks Jacob Sebastian
FROM UniqueDescript U
GROUP BY MRN),
PrimaryInsuranceCarrier(MRN, CarrierID, CoPay, SubscriberID)
AS (SELECT MRN, MAX(CarrierID) CarrierID, CoPay, SubscriberID
FROM ptInsurancePlans
WHERE InsuranceLevel = 1
AND Sequence = 1
AND @StartDtTm BETWEEN EffectiveDtTm AND ExpirationDtTm
GROUP BY MRN, CoPay, SubscriberID),
SecondaryInsuranceCarrier(MRN, CarrierID, CoPay, SubscriberID)
AS (SELECT MRN, MAX(CarrierID) CarrierID, CoPay, SubscriberID
FROM ptInsurancePlans
WHERE InsuranceLevel = 2
AND Sequence = 2
AND @StartDtTm BETWEEN EffectiveDtTm AND ExpirationDtTm
GROUP BY MRN, CoPay, SubscriberID),
Contacts (LName, FName, Address1, City, State, Zip, MRN, ContactID)
AS (SELECT DISTINCT c.LName, c.FName, c.Address1, c.City, c.State, c.Zip, c.MRN, c.ContactID
FROM ptContacts c
INNER JOIN ptInsurancePlans IP ON C.ContactID = IP.SubscriberContactID AND C.MRN = IP.MRN
INNER JOIN PrimaryInsuranceCarrier PIC ON IP.CarrierID = PIC.CarrierID)
SELECT DISTINCT
ptSchedule.MRN,
ptSchedule.StartDtTm,
X.XMLDesc AS Description,
d.LName + ', ' + d.FName AS PatientName,
dbo.rr_GetAge_byMRN(d.MRN, GETDATE()) AS Age,
d.DateOfBirth,
d.Gender,
d.Physician,
ptContacts.LName + ', ' + ptContacts.FName AS GuarantorName,
ptContacts.Address1 AS GuarantorAddress,
ptContacts.City AS GuarantorCity,
ptContacts.State AS GuarantorState,
ptContacts.Zip AS GuarantorZip,
CICP.CarrierCode PrimaryIns,
CICP.CarrierName PrimaryInsPlanName,
PIC.SubscriberID PrimaryInsSubscriberID,
PIC.CoPay PrimaryInsCopay,
CICS.CarrierCode SecondaryIns,
CICS.CarrierName SecondaryInsPlanName,
SIC.SubscriberID SecondaryInsSubscriberID,
SIC.CoPay SecondaryInsCopay,
ptEncounter.BillNumber
FROM ptSchedule
INNER JOIN ptDemographics AS d
ON d.MRN = ptSchedule.MRN
INNER JOIN ptEncounter
ON ptEncounter.MRN = ptSchedule.MRN
INNER JOIN XMLDescript X
ON ptschedule.MRN = X.MRN
LEFT JOIN Contacts ptContacts
ON ptContacts.MRN = d.MRN
LEFT JOIN PrimaryInsuranceCarrier PIC
ON ptSchedule.MRN = PIC.MRN
LEFT JOIN coInsCarriers CICP
ON PIC.CarrierID = CICP.CarrierID
LEFT JOIN SecondaryInsuranceCarrier SIC
ON ptSchedule.MRN = SIC.MRN
LEFT JOIN CoInsCarriers CICS
ON SIC.CarrierID = CICS.CarrierID
WHERE (CONVERT(VARCHAR, ptSchedule.StartDtTm,101) = @StartDtTm)
AND CONVERT(VARCHAR,ptEncounter.DateofService, 10) = CONVERT(VARCHAR,ptSchedule.StartDtTm, 10)
END
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply