I am new to sql and trying to solve a very specific scenario.
I need to build financial statements like profit & loss, balance sheet, cashflow.
Consider these 2 following tables
- financials
| id | company_id | dateEnd | field_id | field_value |
|---|---|---|---|---|
| 1 | 101 | 2010-01-01 | sales | 100 |
| 2 | 101 | 2011-01-01 | sales | 110 |
| 3 | 101 | 2012-01-01 | sales | 120 |
| 4 | 101 | 2010-01-01 | sales_cost | 50 |
| 5 | 101 | 2011-01-01 | sales_cost | 60 |
| 6 | 101 | 2012-01-01 | sales_cost | 70 |
2. statement_sequences
| id | statement_id | sequence | label | formula |
|---|---|---|---|---|
| 1 | 1 | 1 | Revenue | sales |
| 2 | 1 | 2 | Expenditure | sales_cost |
| 3 | 1 | 3 | Operating Profit | sales - sales_cost |
My question here is, How can I define relationship between these two tables and how to get following output ? I need to evaluate formula stored in statement_sequences table against financials table and need results as below
3. Required Output results
| compnay_id | statement_id | sequence | label | 2010-01-01 | 2011-01-01 | 2012-01-01 |
|---|---|---|---|---|---|---|
| 101 | 1 | 1 | Revenue | 100 | 110 | 120 |
| 101 | 1 | 2 | Expenditure | 50 | 60 | 70 |
| 101 | 1 | 3 | Operating Profit | 50 | 50 | 50 |
Please note following points.
This is a very short set of rows. I have many distinct fields other than sales and sales_cost so storing them in column might not be feasible.
It is okay if query performance is not that good as I will be storing the output results in a materialized view.
I am currently using postgresql but open to switch to another db if required for compatibility.
Any suggestion / help is appreciated.
I believe combining a custom sql function that evaluates formula against rows of financials table, and crosstab function might work but now sure how.