Data To Get From 3 Different Tables Depending On Some Conditions

Hi,

I know it sounds very simple with the subject line but for me it's not.

BACK GROUND

My Client is using Crystal Reports 8.5 and SQL Server.
a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.

now there are 3 tables in Question
Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision
and the Fields in Question are Interstate_Revenue and International_Revenue.

Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History.
Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.

Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period.
they do it every month. But sometimes they need to generate Invoices for the OLD Periods.

if we put the problem in simple words,

they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle),
else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there
else
look into History Table and if the filer is found there for that cycle than get the data from there

here's the requirement given to me by my system Analyst.

User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID

Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle
Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID
Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle

For each USER_FILER_ID
If USER_CYCLE_ID=CURRENT_CYCLE
Pull revenue from BILLING_REVENUE /*Process stops here*/
ELSE
{
If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID

Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/

ELSE

IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID
Pull revenue information
}

here's the View being used which is the First Case which is happening currently


SELECT dbo.BILLING_PROVIDER.CONT_NAME_FIRST, dbo.BILLING_PROVIDER.CONT_NAME_LAST, dbo.BILLING_PROVIDER.ADD_STREET_LINE2,

dbo.BILLING_PROVIDER.ADD_STREET_LINE1, dbo.BILLING_PROVIDER.ADD_STREET_LINE3, dbo.BILLING_PROVIDER.ADD_CITY,

dbo.BILLING_PROVIDER.ADD_STATE, dbo.BILLING_PROVIDER.ADD_ZIP, dbo.BILLING_PROVIDER.CONTACT_TEL,

dbo.BILLING_SUMMARY.INVOICE_NUM, dbo.BILLING_SUMMARY.BALANCE_DUE, dbo.BILLING_SUMMARY.PREVIOUS_BALANCE,

dbo.BILLING_SUMMARY.SUM_TRANS, dbo.BILLING_SUMMARY.M_BASE, dbo.BILLING_SUMMARY.M_CONTRIBUTION,

dbo.BILLING_SUMMARY.ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.EST_Q_CONTRIBUTION,

dbo.BILLING_SUMMARY.PRE_DM_ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.Q_CONTRIBUTION_BASE,

dbo.BILLING_SUMMARY.Q_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.Q_LIRE_STATUS,

dbo.BILLING_SUMMARY.A_DM_STATUS, dbo.BILLING_SUMMARY.A_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_DM_STATUS,

dbo.BILLING_SUMMARY.A_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.A_LIRE_STATUS, dbo.BILLING_DETAILS.TRANSACTION_TYPE,

dbo.BILLING_DETAILS.LINE_ITEM_TYPE, dbo.BILLING_DETAILS.SUPPORT_MECHANISM_TYPE, dbo.BILLING_DETAILS.LINE_ITEM_NUMBER,

dbo.BILLING_DETAILS.LINE_ITEM_AMT, dbo.BILLING_DETAILS.TRANSACTION_DATE, dbo.BILLING_DETAILS.REASON_CODE,

dbo.BILLING_CYCLE.STATEMENT_DT, dbo.BILLING_CYCLE.PAYMENT_DUE_DT, dbo.BILLING_CYCLE.MAILING_DT,

dbo.BILLING_REVENUE.INTERSTATE_REVENUE, dbo.BILLING_REVENUE.INTERNATIONAL_REVENUE, dbo.BILLING_SUMMARY.CYCLE_ID,

dbo.BILLING_SUMMARY.FILER_ID, dbo.BILLING_PROVIDER.CARRIER_NAME, dbo.BILLING_CYCLE.PERIOD_ID,

dbo.BILLING_PERIOD.PERIOD_TYPE_ID, dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_DESCR, dbo.BILLING_PERIOD.FCC_PERCENTAGE_ACTUAL,

dbo.BILLING_PERIOD.FCC_PERCENTAGE_EST, dbo.BILLING_PERIOD.HC_PERCENTAGE, dbo.BILLING_PERIOD.SL_PERCENTAGE,

dbo.BILLING_PERIOD.RHC_PERCENTAGE, dbo.BILLING_PERIOD.LI_PERCENTAGE, dbo.BILLING_SUMMARY.U_CONTRIBUTION_BASE,

dbo.BILLING_PERIOD.FCC_CIRCULARITY_FACTOR, dbo.BILLING_SUMMARY.A_LIRE_REVENUE, dbo.BILLING_SUMMARY.ANNUAL_BASE,

dbo.BILLING_REVENUE.RECEIVED_DT

FROM dbo.ACCT_LINE_ITEM_TYPE RIGHT OUTER JOIN

dbo.BILLING_DETAILS RIGHT OUTER JOIN

dbo.BILLING_PERIOD INNER JOIN

dbo.BILLING_CYCLE INNER JOIN

dbo.BILLING_SUMMARY ON dbo.BILLING_CYCLE.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.BILLING_PERIOD.PERIOD_ID = dbo.BILLING_CYCLE.PERIOD_ID ON dbo.BILLING_DETAILS.FILER_ID = dbo.BILLING_SUMMARY.FILER_ID AND

dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_TYPE = dbo.BILLING_DETAILS.LINE_ITEM_TYPE LEFT OUTER JOIN

dbo.BILLING_PROVIDER LEFT OUTER JOIN

dbo.BILLING_REVENUE ON dbo.BILLING_PROVIDER.FILER_ID = dbo.BILLING_REVENUE.FILER_ID ON

dbo.BILLING_SUMMARY.FILER_ID = dbo.BILLING_PROVIDER.FILER_ID

WHERE (dbo.BILLING_SUMMARY.FILER_ID NOT IN

(SELECT DISTINCT BILLING_DETAILS.FILER_ID

FROM dbo.BILLING_DETAILS

WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',

'RHCDC', 'RHCDADJ'))))

Current Situation.
1. They use CR 8.5 IDE to enter the parameters
and this version of CR doesn't allow using Stored PRocedures.
2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere.
But i really really need to do this
3. please give me some clue.. some idea how to resolve this.


here's the primary database diagram
http://deepak.palkar.googlepages.com/Billing2.jpg

thanks a lot..
Deepak