Combining components of multiple transactions from the same table into one row on a report

[GayleC]

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.

 

[pmax9999]

Hi GayleC

The first things that need to be established are:
1. What is it about these two records that identified them as needing to be combined; and
2. What is the basis for combining them.

If you can explain that, we whould be able to assist.


Cheers
Pete.

 

[GayleC]

This is a medical company and the "AdjustmentGroupCode1" and "AdjustmentReasonCode1" are industry standard codes that are supplied by an insurance company to explain the payment "Amount" that they issued for billed services. The paper document that would have been received from the insurance company is called an Explanation of Benefits and would look similar in nature to the following with a few additional fields that are not needed in this report:

99214 115.73 41.30 CO45

The information is needed primarily for two reasons: First some of these codes are used to indicate a denial of payment and the reason for that denial, we need to report on denial trends. Second, to provide additional information to our AR follow up team. The information is available in the AR system but it is not easily accessible and requires the user to access multiple screens in order to obtain the information. The flat file report provides all the information in on place for each transaction posted.

I hope this helps

 

[pmax9999]

What we need to understand is exactly what it is about the 2 lines you highlighted that identifies them as needing to be combined. For example, it can't be any of the first 5 fields in your example, because all have exacly the same data for every record.

Once we understand that, you need to be able to explain the logic for combining those two lines.

It would also help if you could more clearly identify the field names in your sample data as they do not match with the SQL Code you provided. For example your sample seems to suggest columns called "Modifier" and "Charge" (or possibly a column called "Modifier Charge"), but the SQL code does not include such column names.

Cheers.

 

[GayleC]

I'm struggling to explain this. You are correct that the two lines are identical for most of the fields, this is because in reality the two transactions are actually one, but the way this database is written they have broken out components of the transactions into two seperate ones. The CPTCode must match, the Charge (actually called "PatientVisitProcs"."TotalFee" in the database) must match, the "PatientVisitProcs"."DateOfEntry", "PaymentMethod"."CheckDate", and "PaymentMethod"."DateOfEntry" must match. The dollar amount "TransactionDistributions"."Amount", for "Transactions"."Type" = "P" is actually the true "TransactionDistributions"."Amount", for "Transactions"."Type" = "+", but because they have broken the transaction into two distinct transactions, the Amount for Type "+" is reporting as $0 instead of the amount associated with "P".

You can ignore Modifier for this, it doesn't factor into the issue at hand and I should have removed it from my example.

 

[AndyMc]

Create a formula
@unique:
PatientVisitId & TicketNumber & CPTCode & Modifier & Charge & DateOfEntry & CheckDate

Group on the @unique formula

Sum the Amount field for the group and supress the details, and in the group footer you will be able to show the results of both records (as if one record)

 

[GayleC]

Unfortunately this solution did not work. when I summed the Amount field for the group the result was the sum of all the transaction amounts instead of those that met the unique characteristics of the formula. So using the example data above, the sum was $85.05. Also the formula resulted in a blank field when inserted into the report. Any other suggestions?

Here is the formula I created called @unique:

{PatientVisit.PatientVisitId}&{PatientVisit.TicketNumber}&{PatientVisitProcs.CPTCode}&{MedLists.Code}&{PatientVisitProcs.TotalFee}&{PaymentMethod.DateOfEntry}&{PaymentMethod.CheckDate}

 

[fisheromacse]

since you received a 'blank' when you placed the formula in the details sections, my first guess would be that one of the values is null.
try this (you may need to change the numbervar/stringvar/datevar to match the type of field it will be referencing):

{@UniqueUsingVariables}
numbervar pvid;
stringvar pvtn;
numbervar pvpc;
numbervar mlc;
numbervar pvpt;
datevar pmdoe;
datevar pmcd;

IF isnull({PatientVisit.PatientVisitId}) then pvid := 0 else pvid := {PatientVisit.PatientVisitId};
IF isnull({PatientVisit.TicketNumber}) then pvtn := "blank" else pvtn := {PatientVisit.TicketNumber};
IF isnull({PatientVisitProcs.CPTCode}) then pvpc := 0 else pvpc := {PatientVisitProcs.CPTCode};
IF isnull({MedLists.Code}) then mlc := 0 else mlc := {MedLists.Code};
IF isnull({PatientVisitProcs.TotalFee}) then pvpt := 0 else pvpt := {PatientVisitProcs.TotalFee};
IF isnull({PaymentMethod.DateOfEntry}) then pmdoe := dateserial(1900,01,01) else pmdoe := {PaymentMethod.DateOfEntry};
IF isnull({PaymentMethod.CheckDate}) then pmcd := dateserial(1900,01,01) else pmcd := dateserial(1900,01,01);

pvid &" " & pvtn &" " & pvpc &" " & mlc &" " & pvpt &" " & pmdoe &" " & pmcd


also, my usual disclaimer applies double today (i have poured my coffee but not drank it yet)...i do not have crystal in front of me so apologize in advance for any typos, errors, misunderstandings or false assumptions i may have made.

 

[GayleC]

That worked, I do have nulls in the Modifier field so with a few other modifications for formating this is pulling correctly now. Thank you very much. This solution is only causing one small new issue and that is that when i export to excel it lumps it all into one cell than i have to run a text to column on the data, but i think that's a small issue compared to what i was dealing with before.

Again, thank you very much.

 

[fisheromacse]

i think you could drop your fields into the group footer instead of the formula and it should export to excel a little more like you need.

 

[GayleC]

Thanks, i'll try that when my stupid server comes back up.