October 24, 2008 at 10:02 am
OK, we are querying a table called ptSchedule. In this table is data from
a schedule of Appointments. A Patient may have one appointment but two
schedule records due to the patient undergoing two different "treatments"
during the same appointment.
This results in two records when queried but I'd like to be able to get
one record for the appointment and concatenate the Description column
of both ptSchedule records into one.
How can I do that? I will try to post some data/schemas as I know may be
difficult to figure this one out.
October 24, 2008 at 10:10 am
Would JOIN not be enough?
-- CK
October 24, 2008 at 10:19 am
Nope, as it is joining different other tables for other data.
October 24, 2008 at 10:27 am
The sooner you can provide the schema and some sample data the better. Suggestion for the schema and data, primary key(s) on the table, and the additional columns needed for this issue and then some sample (read bogus but representative) data. Don't need a lot just 6 to 10 rows.
😎
October 24, 2008 at 10:32 am
You'd still have to join in data somehow. Your joins don't necessarily have to relate to each other, meaning that you just have to qualify which rows you want from the table, not necessarily mean that table A has to match table B. But you have to use WHERE or ON to ensure that you only get those rows.
Concatenation is just listing more fields in the SELECT clause.
October 24, 2008 at 11:22 am
Lynn Pettis (10/24/2008)
The sooner you can provide the schema and some sample data the better. Suggestion for the schema and data, primary key(s) on the table, and the additional columns needed for this issue and then some sample (read bogus but representative) data. Don't need a lot just 6 to 10 rows.😎
Am building data/schema scripts now. Is it OK to provide data as Excel?
I have all data for one day of schedules, 28 records.
October 24, 2008 at 11:29 am
Better if you provide it as INSERT statements so that whoever decides to give it a go just has to cut, paste and execute. Some people don't have the time (or won't take the time) to reformat the data to create the inserts. I usually fall into the first camp, but when I do reformat data, I have a nice tool to help me, UltraEdit.
😎
October 24, 2008 at 11:43 am
Sounds to me like what you want is to select from ptSchedule and join in the results from a pivot on the description field from Appointments. If so, then you will need to lookup PIVOT in BOL and also look at the PIVOT article by Jeff Moden on this site.
Either way - you will be stuck with a fixed amount of description from the Appointment table unless you resort to dynamic SQL to build the pivot table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2008 at 1:15 pm
Could you post too 10 rows from each table and your desired results?
-- CK
October 24, 2008 at 1:35 pm
Here we go......
Problem description:
After running the creation scripts and then running the SP...
If you look at MRN 1821, you will see that they have 4 insurance plans but only two of
them were in effect at the DateOfService (ptSchedule.StartDtTm) (InsuranceLevel=1 and
Sequence=1 mean Primary Insurance, InsuranceLevel=2 and Sequence=2 mean
Secondary Insurance).
I wrote the functions GetPrimaryInsuranceCarrierID and GetSecondaryInsuranceCarrierID
to be able to get the currently effective Insurance Plan. I use them in another version of
the stored procedure called rr_Collect_EncounterReport2 below.
This patient had 3 schedule records, two had the same Description but different
resourceid the other had a different Description.
What we want for this patient would be one record returned in the stored procedure
rr_Collect_EncounterReport with the Description concatenated like this (wrapped):
'ESTABLISHED PATIENT; OV-LAB-CXR / Cancer Antigen 27-29, CBC w/ Differential, CEA,
Comprehensive Metabolic Panel, LDH, Uri'
I'm not a newbie to SQL Server but I don't have the knowledge to do this kind of thing
and am intimidated a little bit when it comes to CTEs aspecially recursive CTEs
and Cursors.
Schema creation and Data Insert scripts attached:
October 24, 2008 at 2:09 pm
Well, after fixing a couple of inconsistencies in the code (the system I am using is case sensitive so CoPay <> Copay, DateofBirth <> DateOfBirth, etc) I get the following error running the code as is:
Msg 512, Level 16, State 1, Procedure rr_Collect_EncounterReport2, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Not sure which subquery this is from (yet), but I'm conceerned with the number of subqueries in the select statement. This is really RBAR at the moment, so the first thing that should probably be done is modify the query in the stored proc to use joins.
I'm sure I won't be the only one looking at this, so we may get other opinions on that.
I do want to thank you for taking the time and effort to put together everything you did. This will make it easier for folks on this site to help you with this particular issue.
If some one else doesn't come up with a solution first, I'll be sure to let you know what I come up with.
Only other thing that may help, can you put together what the results from all this should look like if everything ran correctly based on the sample data you have provided? This will give us something to compare to as well.
😎
October 24, 2008 at 2:28 pm
Lynn Pettis (10/24/2008)
Well, after fixing a couple of inconsistencies in the code (the system I am using is case sensitive so CoPay <> Copay, DateofBirth <> DateOfBirth, etc) I get the following error running the code as is:Msg 512, Level 16, State 1, Procedure rr_Collect_EncounterReport2, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Not sure which subquery this is from (yet), but I'm conceerned with the number of subqueries in the select statement. This is really RBAR at the moment, so the first thing that should probably be done is modify the query in the stored proc to use joins.
I'm sure I won't be the only one looking at this, so we may get other opinions on that.
I do want to thank you for taking the time and effort to put together everything you did. This will make it easier for folks on this site to help you with this particular issue.
If some one else doesn't come up with a solution first, I'll be sure to let you know what I come up with.
Only other thing that may help, can you put together what the results from all this should look like if everything ran correctly based on the sample data you have provided? This will give us something to compare to as well.
😎
The first thing I did with this was comment out all the RBAR function subqueries, which fixed the subquery error. Depending on how large your actual data set is, those are likely going to murder your performance... but that's a separate issue.
Thank you for posting all the definition, but if possible, could you put it all into a .txt file and attach that to your post (and delete it out of the post itself)? Not only would it make it a ton easier for people to select and copy that much text, but it takes me about a minute to load this post with that much text in it. (My work connection isn't the greatest).
October 24, 2008 at 4:13 pm
Garadin (10/24/2008)
Thank you for posting all the definition, but if possible, could you put it all into a .txt file and attach that to your post (and delete it out of the post itself)? Not only would it make it a ton easier for people to select and copy that much text, but it takes me about a minute to load this post with that much text in it. (My work connection isn't the greatest).
I thought about doing that, forgot that you could do an attachment, or can you?
Anyway, I will come up with expected results this weekend and have everything ready by
Mon morn.
I was trying to get away from using those really goofy joins with the subqueries but ran into
problems with data. If you look at the first SP, rr_Collect_EncounterReport, It has the joins without the subqueries.
Thanks for helping out on this Lynn, and Seth as this is a mission critical report and the
original developer left 3 wks ago. I really have to get it done by Wed.
October 25, 2008 at 12:29 pm
Ok, first a few notes:
I had to fix a few issues with your CTE, and I added several of my own. This is my first attempt at using CTE's (and I'm already falling in love with them), so if I did anything that could be improved upon, please let me know. I replaced your recursive subqueries /function calls with a few outer joins and a couple more CTE's. CoPay is commented out right now because I'm not sure how you want to filter out the additional results. There are multiple copays per MRN per CarrierID right now, and that's what was causing your subquery error before. If you add a groupID join in there, it will take care of it, but the problem is figuring out which groupID to use. I'll leave that to you. This could still probably be optimized farther if need be, so let us know how it performs / if it does what you want.
Solution:
[font="Courier New"]ALTER PROCEDURE rr_Collect_EncounterReport(
@StartDtTm AS DATETIME)
AS
--Set @StartDtTm= '09/15/2008'
--exec rr_Collect_EncounterReport '09/15/2008'
;WITH UniqueDescript(MRN, Description)
AS (SELECT DISTINCT MRN, Description
FROM ptSchedule),
XMLDescript (MRN, XMLDesc)
AS (SELECT MRN,
(SELECT [Description] AS 'data()'
FROM UniqueDescript U2
WHERE U2.MRN = U.MRN
FOR XML PATH('')) XMLDesc --Thanks Jacob Sebastian
FROM UniqueDescript U
GROUP BY MRN),
PrimaryInsuranceCarrier(MRN, CarrierID)
AS (SELECT MRN, MAX(CarrierID) CarrierID
FROM ptInsurancePlans
WHERE InsuranceLevel = 1
AND Sequence = 1
AND @StartDtTm BETWEEN EffectIveDtTm AND ExpirationDtTm
GROUP BY MRN),
SecondaryInsuranceCarrier(MRN, CarrierID)
AS (SELECT MRN, MAX(CarrierID) CarrierID
FROM ptInsurancePlans
WHERE InsuranceLevel = 2
AND Sequence = 2
AND @StartDtTm BETWEEN EffectIveDtTm AND ExpirationDtTm
GROUP BY MRN),
MyContacts (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,
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,
IPP.SubscriberID PrimaryInsSubscriberID,
-- IPP.CoPay PrimaryInsCopay,
CICS.CarrierCode SecondaryIns,
CICS.CarrierName SecondaryInsPlanName,
IPS.SubscriberID SecondaryInsSubscriberID,
-- IPS.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 MyContacts ptContacts ON ptContacts.MRN = d.MRN
LEFT JOIN PrimaryInsuranceCarrier PIC ON ptSchedule.MRN = PIC.MRN
LEFT JOIN ptInsurancePlans IPP ON PIC.CarrierID = IPP.CarrierID AND ptSchedule.MRN = IPP.MRN
LEFT JOIN coInsCarriers CICP ON PIC.CarrierID = CICP.CarrierID
LEFT JOIN SecondaryInsuranceCarrier SIC ON ptSchedule.MRN = SIC.MRN
LEFT JOIN ptInsurancePlans IPS ON SIC.CarrierID = IPS.CarrierID AND 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)
[/font]
October 27, 2008 at 9:14 am
Good job, Seth
I 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.
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.
One MRN is duplicated because one of their insuranceplans has an invalid ExpirationDtTm but other than that, looks like it will work.
Thanks a million.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply