August 16, 2019 at 1:53 am
my requirements is to display the single record by for all the cases associated with patient
i try to resolve this using max and CTE but this is removing records and displaying the latest record only
I need to show the records with in one row by patient .. but if that patient have multiple similar cases with same case type can display the duplicate records for same patient ...but when i tried max or CTE.. it eliminating the records
Please see the attached data that my query is displaying(Sheet1) and also i attached the data that i need to display(sheet 2) .any suggestions/or pointer would help.
August 16, 2019 at 1:55 am
August 16, 2019 at 2:41 pm
Most people here won't open Excel documents from the Internet.
Can you use text files, or simply cut & paste the code, instead?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 16, 2019 at 3:43 pm
I'm not at all sure what your requirement is but if you want to show multiple rows on one row then you need to use a FOR XML PATH statement to concatenate multiple rows for each patient, case type into one row.
August 16, 2019 at 4:49 pm
August 16, 2019 at 5:23 pm
I think you can do it by changing your LEFT JOINs to be OUTER APPLY and selecting TOP(1) within it.
FROM #DataExtract t1
OUTER APPLY(SELECT TOP(1) *
FROM #DataExtract t2
WHERE t2.case_number = t1.Case_Number
AND t2.Case_Record_Type = 'General Inquiry'
AND t2.Case_Sub_Type = 'Escalation') t2
OUTER APPLY(SELECT TOP(1) *
FROM #DataExtract t3
WHERE t3.case_number = t1.Case_Number
AND t3.Case_Record_Type = 'Follow Up 3 Step'
AND t3.Case_Sub_Type = 'Escalation') t3
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t4
WHERE t4.case_number = t1.Case_Number
AND t4.Case_Record_Type = 'Benefits Verification'
AND (t4.BV_Processing_Mode IS NULL OR t4.BV_Processing_Mode = 'Manual')) t4
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t5
WHERE t5.case_number = t1.Case_Number
AND t5.Case_Record_Type = 'Benefits Verification'
AND t5.BV_Processing_Mode = 'Automated') t5
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t6
WHERE t6.case_number = t1.Case_Number
AND t6.Case_Record_Type = 'Missing Information') t6
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t7
WHERE t7.case_number = t1.Case_Number
AND t7.Case_Record_Type = 'Prior Authorization'
AND t7.Case_Sub_Type <> 'Appeal') t7
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t8
WHERE t8.case_number = t1.Case_Number
AND t8.Case_Record_Type = 'Prior Authorization'
AND t8.Case_Sub_Type = 'Appeal') t8
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t9
WHERE t9.case_number = t1.Case_Number
AND t9.Case_Record_Type = 'Commercial Copay') t9
OUTER APPLY (SELECT TOP(1) *
FROM #DataExtract t10
WHERE t10.case_number = t1.Case_Number
AND t10.Case_Record_Type = 'Claims Admin') t10;
August 16, 2019 at 7:13 pm
You haven't said what you are using to display this information. It may be (much) easier to do this in your presentation layer than the database layer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply