pulling Data from 3 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

pulling Data from 3 different tables

Author

Topic

BreathShadow
Starting Member

1 Post

Posted - 2007-11-02 : 15:37:28

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

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:57:46

not sure if i got all of that but it sounds like you may want to create a function to return the current bill cycle then a view from the rules for where to pull from. something along the lines of...

select *
from billing_revenue
where bill_cycle = dbo.current_bill_cycle()
union all
select *
from billing_revenue_revision
where bill_cycle <> dbo.current_bill_cycle()
union all
select *
from billing_revenue_history
left join billing_revenue_revision on billing_revenue_history.id = billing_revenue_revision.id
where billing_revenue_revision.id is null

sorry if that is completely off the mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources