Topic: Calculating value from two separate rows in the same table and inserting in the same table as a new row.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_ladderid security_id metric_id value1 3 80 125.452 3 81 548.453 3 82 145.144 3 83 123.326 4 80 453.757 4 81 234.238 4 82 675.42...
Table Name: metric_detailsid metric_id metric_type_id metric_name1 80 2 Fiscal Enterprise Value Historic Year 12 81 2 Fiscal Enterprise Value Current Fiscal Year3 82 2 Fiscal Enterprise value Forward Fiscal year 14 83 2 Fiscal Enterprise Value Forward Fiscal Year 25 101 3 Calendar Enterprise value Historic Year 16 102 3 Calendar Enterprise Value Current Fiscal Year5 103 3 Calendar Enterprise value Forward Year 16 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_detailsid metric_type_id metric_type_name1 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 value1 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 finallymetric_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: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.valuefrom metrics_ladder mlwhere 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_averagesselect 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 temppivot( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)) as temppivotinner join company_details cd on temppivot.security_id = cd.security_id
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.
Calculating value from 2 separate rows in same tbl
Posted - 2008-01-19 : 18:22:35