Create a QVD from a master QVD and SQL transaction table

[#1]

Here is my quest…

Step 1:

I have a dbo called "BarChargeTransactions". I have a qvd called "BAR_Patients". I want to use the active accounts (VisitID) in BAR_Patients to build a summary table of charges by Visit_ID, SRV_DT (Service Date), & CAT_CD (Charge Category). I want to summarize the Amount and TransactionCount

Step 2:

Use the results in Step 1 and create a calculation to evaluate reimbursement for the three different payment plans; Per Diem, Per Diem with CAT_CD Carveouts and Per Diem with Stop Loss. In order to do the last two types, I have to keep a running total and daily total of gross charges and CAT_CD specific charges and transaction counts.

Step 3:

Evaluate the VisitID specific plan against the Plan_Matrix and Cat_CD_Carveout_Matrix to determine a CASE calculation. I have constructed the these two matrices along with a master CAT_CD mnemonic table.

Any help getting started on this would be appreciated.