November 2, 2007 at 1:39 pm
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
November 5, 2007 at 9:57 am
If I'm understanding your issue correctly, I believe that UNION is going to be your friend. This is the keyword that attaches two datasets vertically, allowing you to combine Billing_Revenue and Billing_Revenue_History into a single view. Presumably, the two tables have very similar schema, so the UNION should be simple.
SELECT *
FROM Billing_Revenue
WHERE {insert criteria here}
UNION
SELECT *
FROM Billing_Revenue_History
WHERE {insert criteria here}
As a critical note, you need to make sure that the columns line up. So, honestly, do NOT actually use "SELECT *". And, you may need to alias some column names, so that the dataset from the first query and the dataset from the second query have identical schemas.
As a secondary note, UNION by itself sorts the records, and drops identical records. This can be expensive, if you have large queries. UNION ALL does not sort or filter, and hence is much quicker.
Now, how you deal with Billing_Revenue_Revision is a bit trickier, and depends heavily on how it is actually set up. It may be easier to bring the revised records in with another UNION, and simply sort for the most recent "version". Or, you may want to join it, and check for NOT NULL in the Revision side of the join (make sure that the sub-query of the Revision table that you join has at most one record for any given Bill, or you'll get duplicate rows that you'll then have to filter out).
November 5, 2007 at 12:17 pm
Thank you SSC,
really appreciate your help and I understood your point.
The requirements were changed later and they didn't want to use the Revision table any more.
Here's what I did.
as i was going to use this view in conjuction with the Crystal Reports, following are the new columns i added using the sub queries in the same view
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,
(select max(CYCLE_ID) from Billing_Cycle) as Current_Cycle_ID,
--(select Period_ID from Billing_Cycle where Billing_Cycle.Cycle_ID = BILLING_SUMMARY.Cycle_ID) as User_Period_ID,
(select dbo.fx_GetPeriodID (Billing_Summary.Cycle_ID)) as User_Period_ID,
(select dbo.fx_GET_PERIOD_FIRST_CYCLE (
dbo.fx_GetPeriodID(Billing_Summary.Cycle_ID
))) AS PERIOD_FIRST_CYCLE,
(SELECT dbo.fx_PERIOD_FIRST_CYCLE_EXISTS_IN_HISTORY (
BILLING_SUMMARY.FILER_ID,
dbo.fx_GET_PERIOD_FIRST_CYCLE (
dbo.fx_GetPeriodID(Billing_Summary.Cycle_ID
)))) AS PERIOD_FIRST_CYCLE_EXISTS_IN_BRH,
(SELECT
dbo.fx_Get_BRH_INTERSTATE_REVENUE (
BILLING_SUMMARY.FILER_ID, dbo.fx_GET_PERIOD_FIRST_CYCLE (
dbo.fx_GetPeriodID(Billing_Summary.Cycle_ID
)))) AS BRH_INTERSTATE_REVENUE,
(SELECT
dbo.fx_Get_BRH_INTERNATIONAL_REVENUE (
BILLING_SUMMARY.FILER_ID, dbo.fx_GET_PERIOD_FIRST_CYCLE (
dbo.fx_GetPeriodID(Billing_Summary.Cycle_ID
)))) AS BRH_INTERNATIONAL_REVENUE
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'))))
)
and than I added some formulas in Crystal Reports to show/hide the correct column.
I know it is not the most efficient way but as long as it works, it works 🙂
Thank you for your time
Deepak
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
