Help with SQL query - complex issue with producing a concatenated string
I have to produce a fixed width column file for my client. Issue is in this particular section of code.
Let me try to explain this the best way I can. A patient can have a single form of insurance coverage and even have up to three or more insurance coverage plans. With that being said, I need to return in a concatenated string, along with other patient visit values the list of insurance coverage. Problem I am having is right now I am getting empty spaces for my secondary and third coverage when I want it to not report in my output string if the patient did not have this additional coverage.
Let me try to explain this the best way I can. A patient can have a single form of insurance coverage and even have up to three or more insurance coverage plans. With that being said, I need to return in a concatenated string, along with other patient visit values the list of insurance coverage. Problem I am having is right now I am getting empty spaces for my secondary and third coverage when I want it to not report in my output string if the patient did not have this additional coverage.
SET NOCOUNT ON;
DECLARE @VDATETYPE VARCHAR(3) = 'DOS'
DECLARE @VSTARTDATE DATETIME = '01/01/2013'
DECLARE @VENDDATE DATETIME = '03/31/2013'
DECLARE @OUTPUT TINYINT = '1'
CREATE TABLE #Visits
(
PatientVisitId INT
, PatientProfileId INT
, CompanyId INT
, DoctorId INT
, FacilityId INT
);
INSERT INTO #Visits
SELECT DISTINCT
pv.PatientVisitId
, pv.PatientProfileId
, pv.CompanyId
, pv.DoctorId
, pv.FacilityId
FROM
PatientVisitProcs pvp
JOIN PatientVisit pv ON pv.PatientVisitId = pvp.PatientVisitId
WHERE ISNULL(pvp.Voided , 0) = 0 --filter out voided visits
AND
(@VDATETYPE = 'DOS'
AND pvp.DateofServiceFrom >= @VSTARTDATE
AND pvp.DateofServiceFrom < DATEADD(D , 1 , @VENDDATE)
OR @VDATETYPE = 'DOE'
AND pvp.DateofEntry >= @VSTARTDATE
AND pvp.DateofEntry < DATEADD(D , 1 , @VENDDATE)
);
CREATE TABLE #Carriers
(
OP3001_RecordType CHAR(2)
, OP3002_SequenceNumber CHAR(2)
, OP3003_PatientControlNumber CHAR(20)
, OP3095_Filler1 CHAR(1)
, OP3004_PayerIdNumber CHAR(5)
, OP3096_Filler2 CHAR(4)
, OP3005_SocialSecurityNumber CHAR(19)
, OP3097_Filler3 CHAR(2)
, OP3006_PayerName CHAR(23)
, OP3098_Filler4 CHAR(1)
, OP3007_InsuredGroupNumber CHAR(17)
, OP3008_InsuredUniqueIdentifier CHAR(20)
, OP3099_Filler5 CHAR(76)
, OP3001_RecordType_Sec CHAR(2)
, OP3002_SequenceNumber_Sec CHAR(2)
, OP3003_PatientControlNumber_Sec CHAR(20)
, OP3095_Filler1_Sec CHAR(1)
, OP3004_PayerIdNumber_Sec CHAR(5)
, OP3096_Filler2_Sec CHAR(4)
, OP3005_SocialSecurityNumber_Sec CHAR(19)
, OP3097_Filler3_Sec CHAR(2)
, OP3006_PayerName_Sec CHAR(23)
, OP3098_Filler4_Sec CHAR(1)
, OP3007_InsuredGroupNumber_Sec CHAR(17)
, OP3008_InsuredUniqueIdentifier_Sec CHAR(20)
, OP3099_Filler5_Sec CHAR(76)
, OP3001_RecordType_Ter CHAR(2)
, OP3002_SequenceNumber_Ter CHAR(2)
, OP3003_PatientControlNumber_Ter CHAR(20)
, OP3095_Filler1_Ter CHAR(1)
, OP3004_PayerIdNumber_Ter CHAR(5)
, OP3096_Filler2_Ter CHAR(4)
, OP3005_SocialSecurityNumber_Ter CHAR(19)
, OP3097_Filler3_Ter CHAR(2)
, OP3006_PayerName_Ter CHAR(23)
, OP3098_Filler4_Ter CHAR(1)
, OP3007_InsuredGroupNumber_Ter CHAR(17)
, OP3008_InsuredUniqueIdentifier_Ter CHAR(20)
, OP3099_Filler5_Ter CHAR(76)
, PatientVisitId INT
, PatientProfileId INT
, PatientId VARCHAR(20)
, PatientName VARCHAR(120)
, TicketNumber VARCHAR(20)
);
INSERT INTO #Carriers
(
PatientVisitId
, PatientProfileId
, PatientId
, PatientName
, TicketNumber
)
SELECT
pv.PatientVisitId
, pp.PatientProfileId
, pp.PatientId
, dbo.FormatName ( pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix )
, pv.TicketNumber
FROM PatientVisit pv
JOIN #Visits v ON pv.PatientVisitId = v.PatientVisitId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE
pv.PatientVisitId IN ( 1032394 , 969414 , 978285 )
--- three visits - 1032394 has all 3 carriers, 969414 has primary and secondary, no tertiary and 978285 is primary only
UPDATE C
SET
OP3001_RecordType = '30'
, OP3002_SequenceNumber = '01'
, OP3003_PatientControlNumber = UPPER(pv.TicketNumber)
, OP3095_Filler1 = SPACE(1)
, OP3004_PayerIdNumber = SPACE(5)
, OP3096_Filler2 = SPACE(4)
, OP3005_SocialSecurityNumber = SPACE(19)
, OP3097_Filler3 = SPACE(2)
, OP3006_PayerName = COALESCE(LEFT(primic.ListName , 23),SPACE(23))
, OP3098_Filler4 = SPACE(1)
, OP3007_InsuredGroupNumber = COALESCE(LEFT(primpi.GroupId , 17),SPACE(17))
, OP3008_InsuredUniqueIdentifier = COALESCE(LEFT(primpi.InsuredId , 20),SPACE(20))
, OP3099_Filler5 = SPACE(76)
FROM #Carriers C
JOIN PatientVisit pv ON C.PatientVisitId = pv.PatientVisitId
LEFT JOIN PatientInsurance primpi ON pv.PrimaryPICarrierId = primpi.PatientInsuranceId
JOIN InsuranceCarriers primic ON pv.PrimaryInsuranceCarriersId = primic.InsuranceCarriersId;
UPDATE C
SET
OP3001_RecordType_Sec = '30'
, OP3002_SequenceNumber_Sec = '02'
, OP3003_PatientControlNumber_Sec = UPPER(pv.TicketNumber)
, OP3095_Filler1_Sec = SPACE(1)
, OP3004_PayerIdNumber_Sec = SPACE(5)
, OP3096_Filler2_Sec = SPACE(4)
, OP3005_SocialSecurityNumber_Sec = SPACE(19)
, OP3097_Filler3_Sec = SPACE(2)
, OP3006_PayerName_Sec = COALESCE(LEFT(ic2.ListName , 23),SPACE(23))
, OP3098_Filler4_Sec = SPACE(1)
, OP3007_InsuredGroupNumber_Sec = COALESCE(LEFT(pi2.GroupId , 17),SPACE(17))
, OP3008_InsuredUniqueIdentifier_Sec = COALESCE(LEFT(pi2.InsuredId , 20),SPACE(20))
, OP3099_Filler5_Sec = SPACE(76)
FROM #Carriers C
JOIN PatientVisit pv ON C.PatientVisitId = pv.PatientVisitId
JOIN PatientVisitInsurance pvi2 ON C.PatientVisitId = pvi2.PatientVisitId AND pvi2.OrderForClaims = 2
JOIN PatientInsurance pi2 ON pvi2.PatientInsuranceId = pi2.PatientInsuranceId
JOIN InsuranceCarriers ic2 ON pi2.InsuranceCarriersId = ic2.InsuranceCarriersId;
UPDATE C
SET
OP3001_RecordType_Ter = '30'
, OP3002_SequenceNumber_Ter = '03'
, OP3003_PatientControlNumber_Ter = UPPER(pv.TicketNumber)
, OP3095_Filler1_Ter = SPACE(1)
, OP3004_PayerIdNumber_Ter = SPACE(5)
, OP3096_Filler2_Ter = SPACE(4)
, OP3005_SocialSecurityNumber_Ter = SPACE(19)
, OP3097_Filler3_Ter = SPACE(2)
, OP3006_PayerName_Ter = COALESCE(LEFT(ic3.ListName , 23),SPACE(23))
, OP3098_Filler4_Ter = SPACE(1)
, OP3007_InsuredGroupNumber_Ter = COALESCE(LEFT(pi3.GroupId , 17),SPACE(17))
, OP3008_InsuredUniqueIdentifier_Ter = COALESCE(LEFT(pi3.InsuredId , 20),SPACE(20))
, OP3099_Filler5_Ter = SPACE(76)
FROM #Carriers C
JOIN PatientVisit pv ON C.PatientVisitId = pv.PatientVisitId
JOIN PatientVisitInsurance pvi3 ON C.PatientVisitId = pvi3.PatientVisitId AND pvi3.OrderForClaims = 3
JOIN PatientInsurance pi3 ON pvi3.PatientInsuranceId = pi3.PatientInsuranceId
JOIN InsuranceCarriers ic3 ON pi3.InsuranceCarriersId = ic3.InsuranceCarriersId;
IF @OUTPUT = 1
BEGIN
SELECT * FROM #Carriers ORDER BY PatientName
END
ELSE
BEGIN
SELECT
OP3001_RecordType
+ OP3002_SequenceNumber
+ OP3003_PatientControlNumber
+ OP3095_Filler1
+ OP3004_PayerIdNumber
+ OP3096_Filler2
+ OP3005_SocialSecurityNumber
+ OP3097_Filler3
+ OP3006_PayerName
+ OP3098_Filler4
+ OP3007_InsuredGroupNumber
+ OP3008_InsuredUniqueIdentifier
+ OP3099_Filler5
+ ISNULL(OP3001_RecordType_Sec,'')
+ ISNULL(OP3002_SequenceNumber_Sec,'')
+ ISNULL(OP3003_PatientControlNumber_Sec,'')
+ ISNULL(OP3095_Filler1_Sec,'')
+ ISNULL(OP3004_PayerIdNumber_Sec,'')
+ ISNULL(OP3096_Filler2_Sec,'')
+ ISNULL(OP3005_SocialSecurityNumber_Sec,'')
+ ISNULL(OP3097_Filler3_Sec,'')
+ ISNULL(OP3006_PayerName_Sec,'')
+ ISNULL(OP3098_Filler4_Sec,'')
+ ISNULL(OP3007_InsuredGroupNumber_Sec,'')
+ ISNULL(OP3008_InsuredUniqueIdentifier_Sec,'')
+ ISNULL(OP3099_Filler5_Sec,'')
+ ISNULL(OP3001_RecordType_Ter,'')
+ ISNULL(OP3002_SequenceNumber_Ter,'')
+ ISNULL(OP3003_PatientControlNumber_Ter,'')
+ ISNULL(OP3095_Filler1_Ter,'')
+ ISNULL(OP3004_PayerIdNumber_Ter,'')
+ ISNULL(OP3096_Filler2_Ter,'')
+ ISNULL(OP3005_SocialSecurityNumber_Ter,'')
+ ISNULL(OP3097_Filler3_Ter,'')
+ ISNULL(OP3006_PayerName_Ter,'')
+ ISNULL(OP3098_Filler4_Ter,'')
+ ISNULL(OP3007_InsuredGroupNumber_Ter,'')
+ ISNULL(OP3008_InsuredUniqueIdentifier_Ter,'')
+ ISNULL(OP3099_Filler5_Ter,'') AS ExportTestFile
FROM #Carriers
END
DROP TABLE #Visits , #Carriers ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you please post these in a sample table and the expected output.?
ASKER
Got with another colleague and got solution to this question. Rewarding points for your time to look and offer insight. Thanks again.
ASKER
Examples.
A patient with 3 insurances should have a file with 576 characters. A patient that has two insurance should only pull in 384 characters (576 - 192). The third set needs to be removed because the patient did not have 3 insurance plans. Finally, a patient with only one insurance should only get the first set and not the other 2. Issue is, I am inserting this as part of a longer overall record set into a final file for all a patients records. I am thinking maybe a recursive CTE may be needed?