Two tables with no direct relationship

Question

I have 2 tables with no relation between them. I want to display the data in tabular format by month. Here is a sample output:

There are 2 different tables

• 1 for income
• 1 for expense

Problem is that we have no direct relation between these. The only commonality between them is month (date). Does anyone have a suggestion on how to generate such a report?

here is my union queries:

    SELECT TO_DATE(TO_CHAR(PAY_DATE,'MON-YYYY'), 'MON-YYYY') , 'FEE RECEIPT',     NVL(SUM(SFP.AMOUNT_PAID),0) AMT_RECIEVED
FROM STU_FEE_PAYMENT SFP, STU_CLASS SC, CLASS C
WHERE SC.CLASS_ID = C.CLASS_ID
AND SFP.STUDENT_NO = SC.STUDENT_NO
AND PAY_DATE BETWEEN '01-JAN-2014' AND '31-DEC-2014' 
AND SFP.AMOUNT_PAID >0
GROUP BY  TO_CHAR(PAY_DATE,'MON-YYYY')
UNION 
SELECT TO_DATE(TO_CHAR(EXP_DATE,'MON-YYYY'), 'MON-YYYY') , ET.DESCRIPTION,  SUM(EXP_AMOUNT)
FROM EXP_DETAIL ED, EXP_TYPE ET, EXP_TYPE_DETAIL ETD
WHERE ET.EXP_ID = ETD.EXP_ID
AND ED.EXP_ID = ET.EXP_ID
AND ED.EXP_DETAIL_ID = ETD.EXP_DETAIL_ID
AND EXP_DATE BETWEEN '01-JAN-2014' AND '31-DEC-2014' 
GROUP BY TO_CHAR(EXP_DATE,'MON-YYYY'), ET.DESCRIPTION
ORDER BY 1

Regards:

Answer 1

In order to do this you probably want to make the Income and Expenses into separate sub-queries.

I have taken the two parts of your union query and separated them into sub-queries, one called income and one called expense. Both sub-queries summarise the data over the month period as before, but now you can use a JOIN on the Months to allow the data from each sub-query to be connected. Note: I have used an OUTER JOIN, because this will still join month where there is no income, but there is expense and vice versa. This will require some manipulation, because you probably are better off returning a set of zeros for the month if no transaction occur.

In the top level SELECT, replace the use of *, with the correct listing of fields required. I simply used this to show that each field can be reused from the sub-query in the outer query, by referring to the alias as the table name.

SELECT Income.*, Expenses.*
FROM  (SELECT TO_DATE(TO_CHAR(PAY_DATE,'MON-YYYY'), 'MON-YYYY') as Month, 'FEE RECEIPT', NVL(SUM(SFP.AMOUNT_PAID),0) AMT_RECIEVED
       FROM STU_FEE_PAYMENT SFP, STU_CLASS SC, CLASS C
       WHERE SC.CLASS_ID = C.CLASS_ID
       AND SFP.STUDENT_NO = SC.STUDENT_NO
       AND PAY_DATE BETWEEN '01-JAN-2014' AND '31-DEC-2014' 
       AND SFP.AMOUNT_PAID >0
       GROUP BY  TO_CHAR(PAY_DATE,'MON-YYYY') Income
OUTER JOIN (SELECT TO_DATE(TO_CHAR(EXP_DATE,'MON-YYYY'), 'MON-YYYY') as Month, ET.DESCRIPTION,  SUM(EXP_AMOUNT)
            FROM EXP_DETAIL ED, EXP_TYPE ET, EXP_TYPE_DETAIL ETD
            WHERE ET.EXP_ID = ETD.EXP_ID
            AND ED.EXP_ID = ET.EXP_ID
            AND ED.EXP_DETAIL_ID = ETD.EXP_DETAIL_ID
            AND EXP_DATE BETWEEN '01-JAN-2014' AND '31-DEC-2014' 
            GROUP BY TO_CHAR(EXP_DATE,'MON-YYYY'), ET.DESCRIPTION) Expenses
ON Income.Month = Expenses.Month

There are still many calculations that you will have to insert, to get your final result, which you will have to work on separately. The resulting query to perform what you expect above will likely be a lot longer than this, I am just trying to show you the structure.

However the final tricky part for you is going to be the BBF. Balance Bought Forward. SQL is great a joining tables and columns, but each row is treated and handled separately, it does not read and value from the previous row within a query and allow you to manipulate that value in the next row. To do this you need another sub-query to SUM() all the changes from a point in time up until the start of the month. Financial products normally store Balance at points in time, because it is possible that not all transaction are accurately recorded and there needs to be a mechanism to adjust the Balance. Using this theory, you you need to write your sub-query to summarise all changes since the previous Balance.

IMO Financial applications are inherently complex, so the solution is going to take some time to mould into the right one.

Final Word: I am not familiar with OracleReports, but there may be something in there which will assist with maintaining the BBF.