Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table

Code Block


Hi,

I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:

Table Name: metrics_ladder

id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.

Table Name: metric_details

id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2

Table Name: metric_type_details

id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated

The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:

id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>

Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.

Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,

metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally

metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).

As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:

security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.

which is then unpivoted.

The SQL that achieves this is more or less as follows:

*********
START SQL
*********

declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)


-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;

with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id

*********
END SQL
*********

The result then needs to be unpivoted and stored in metrics_ladder.

And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.

Many thanks (if you've read this far!)

M.