I am trying to write a flat file report that calls for components of different transactions from the same table to be reported on the same row in a report. I am working in Crystal XI. The transactions I am trying to combine from the same table is the "Amount" with a TransactionType of "P" and "AdjustmentGroupCode1" and "AdjustmentReasonCode1" both with TransactionType of "+" from the TransactionsDistribution table. Each Type of Transaction has it's own unique TransactionID. The items in common are the CPTcode from the PatientVisitProcs table and the CheckDate from the PaymentMethod Table.
My query is below:
SELECT "PatientVisit"."PatientVisitId", "PatientVisit"."TicketNumber", "PatientVisitAgg"."InsBalance", "PatientVisitAgg"."PatBalance", "PatientVisitProcs"."CPTCode", "PatientVisitProcs"."DateOfEntry", "PaymentMethod"."CheckDate", "TransactionDistributions"."AdjustmentGroupCode1", "TransactionDistributions"."AdjustmentReasonCode1", "PatientVisitProcs"."TotalFee", "MedLists"."Code", "Transactions"."Type", "TransactionDistributions"."Amount", "PaymentMethod"."DateOfEntry", "Transactions"."Name"
FROM {oj (((((("CentricityPS"."dbo"."PaymentMethod" "PaymentMethod" LEFT OUTER JOIN "CentricityPS"."dbo"."VisitTransactions" "VisitTransactions" ON "PaymentMethod"."PaymentMethodId"="VisitTransactions"."PaymentMethodId") LEFT OUTER JOIN "CentricityPS"."dbo"."PatientVisit" "PatientVisit" ON "VisitTransactions"."PatientVisitid"="PatientVisit"."PatientVisitId") INNER JOIN "CentricityPS"."dbo"."Transactions" "Transactions" ON "VisitTransactions"."VisitTransactionsId"="Transactions"."VisitTransactionsId") INNER JOIN "CentricityPS"."dbo"."TransactionDistributions" "TransactionDistributions" ON "Transactions"."TransactionsId"="TransactionDistributions"."TransactionsId") INNER JOIN "CentricityPS"."dbo"."PatientVisitProcs" "PatientVisitProcs" ON "TransactionDistributions"."PatientVisitProcsId"="PatientVisitProcs"."PatientVisitProcsId") LEFT OUTER JOIN "CentricityPS"."dbo"."MedLists" "MedLists" ON "PatientVisitProcs"."Modifier1MId"="MedLists"."MedListsId") INNER JOIN "CentricityPS"."dbo"."PatientVisitAgg" "PatientVisitAgg" ON "PatientVisit"."PatientVisitId"="PatientVisitAgg"."PatientVisitId"}
WHERE "Transactions"."Name"<>' ' AND ("Transactions"."Type"='+' OR "Transactions"."Type"='P')
ORDER BY "PatientVisit"."TicketNumber", "PaymentMethod"."CheckDate"
Right now my results look like this:
PatientVisitId TicketNumber CPTCode Modifier Charge DateOfEntry CheckDate DateOfEntry Amount Type CARC1
1137 ASH000053 99214 115.73 07/02/10 2/16/12 (6.05) P
[highlight #FCE94F]1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 0.00 + CO45
1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 41.30 P[/highlight]
1137 ASH000053 99214 115.73 07/02/10 08/19/10 8/31/10 0.00 + CO45
1137 ASH000053 99214 115.73 07/02/10 08/19/10 8/31/10 26.05 P
1137 ASH000053 99214 115.73 07/02/10 10/06/11 10/10/11 0.00 + PR3
1137 ASH000053 99214 115.73 07/02/10 10/06/11 10/10/11 17.70 P
1137 ASH000053 99214 115.73 07/02/10 04/26/12 6/13/12 6.05 P
The highlighed rows demonstrate two rows that need to be combined to one to look like the following:
1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 41.30 CO45
I've tried utilizing a crosstab report, and while combining the two, the layout was not appropriate for a flat file. i'm sure a SQL expression is my solution, but i'm having problems coming up with what it would be.
Thank you in advance.
My query is below:
SELECT "PatientVisit"."PatientVisitId", "PatientVisit"."TicketNumber", "PatientVisitAgg"."InsBalance", "PatientVisitAgg"."PatBalance", "PatientVisitProcs"."CPTCode", "PatientVisitProcs"."DateOfEntry", "PaymentMethod"."CheckDate", "TransactionDistributions"."AdjustmentGroupCode1", "TransactionDistributions"."AdjustmentReasonCode1", "PatientVisitProcs"."TotalFee", "MedLists"."Code", "Transactions"."Type", "TransactionDistributions"."Amount", "PaymentMethod"."DateOfEntry", "Transactions"."Name"
FROM {oj (((((("CentricityPS"."dbo"."PaymentMethod" "PaymentMethod" LEFT OUTER JOIN "CentricityPS"."dbo"."VisitTransactions" "VisitTransactions" ON "PaymentMethod"."PaymentMethodId"="VisitTransactions"."PaymentMethodId") LEFT OUTER JOIN "CentricityPS"."dbo"."PatientVisit" "PatientVisit" ON "VisitTransactions"."PatientVisitid"="PatientVisit"."PatientVisitId") INNER JOIN "CentricityPS"."dbo"."Transactions" "Transactions" ON "VisitTransactions"."VisitTransactionsId"="Transactions"."VisitTransactionsId") INNER JOIN "CentricityPS"."dbo"."TransactionDistributions" "TransactionDistributions" ON "Transactions"."TransactionsId"="TransactionDistributions"."TransactionsId") INNER JOIN "CentricityPS"."dbo"."PatientVisitProcs" "PatientVisitProcs" ON "TransactionDistributions"."PatientVisitProcsId"="PatientVisitProcs"."PatientVisitProcsId") LEFT OUTER JOIN "CentricityPS"."dbo"."MedLists" "MedLists" ON "PatientVisitProcs"."Modifier1MId"="MedLists"."MedListsId") INNER JOIN "CentricityPS"."dbo"."PatientVisitAgg" "PatientVisitAgg" ON "PatientVisit"."PatientVisitId"="PatientVisitAgg"."PatientVisitId"}
WHERE "Transactions"."Name"<>' ' AND ("Transactions"."Type"='+' OR "Transactions"."Type"='P')
ORDER BY "PatientVisit"."TicketNumber", "PaymentMethod"."CheckDate"
Right now my results look like this:
PatientVisitId TicketNumber CPTCode Modifier Charge DateOfEntry CheckDate DateOfEntry Amount Type CARC1
1137 ASH000053 99214 115.73 07/02/10 2/16/12 (6.05) P
[highlight #FCE94F]1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 0.00 + CO45
1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 41.30 P[/highlight]
1137 ASH000053 99214 115.73 07/02/10 08/19/10 8/31/10 0.00 + CO45
1137 ASH000053 99214 115.73 07/02/10 08/19/10 8/31/10 26.05 P
1137 ASH000053 99214 115.73 07/02/10 10/06/11 10/10/11 0.00 + PR3
1137 ASH000053 99214 115.73 07/02/10 10/06/11 10/10/11 17.70 P
1137 ASH000053 99214 115.73 07/02/10 04/26/12 6/13/12 6.05 P
The highlighed rows demonstrate two rows that need to be combined to one to look like the following:
1137 ASH000053 99214 115.73 07/02/10 07/28/10 8/3/10 41.30 CO45
I've tried utilizing a crosstab report, and while combining the two, the layout was not appropriate for a flat file. i'm sure a SQL expression is my solution, but i'm having problems coming up with what it would be.
Thank you in advance.