How to Calculate a new field based on exist fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Calculate a new field based on exist fields

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-11-03 : 18:23:58

Hello All,

i tried alot to get a new calculated column,

my present quert is:

Select MonthNameCol, Region, Agent, sum(Acct_WIG * Dollar_prc) as col1, sum(ACCT_WGP * Dollar_prc)as col2
from CompanyCapital,ForignEx
where
MonthNameCol in ('2009-06', '2009-07', '2009-08', '2009-09')
and Region in ('Canada')
and CompanyCapital.Id = ForignEx.ID.id
and "Companies"."Id" = "CompanyPeriodReport"."CompanyId"
group by MonthNameCol, Region, Agent

output:
MonthNameCol,Region,Agent, col1, col2
2009-06, Canada, BBDO, 1401, 1312
2009-07, Canada, BBDO, 1357, 1206
2009-08, Canada, BBDO, 1427, 1032
2009-09, Canada, BBDO, 1363, 1100

based on the above result,
how can i get the another column which is col3,formula as (col1-col2/(previous month col2 value) * 30)

for example: 2009-09 col3= 1363-1100 so 263 then 263/1032 (because previous month 2009-08 col2 value is 1032) * 30 i.e = 7.64

here the trickie thing that i broke my head is how to get previous month col2 value in Calculation

Could you please Help me

i worked a lot , seems to be no use

Thanks in advance

dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-11-03 : 18:38:19

any ideas please i am using Microsoft SQL Server 2005 - 9.00.3042.00 (X64) version

dhani
Posting Yak Master

132 Posts

Posted - 2009-11-03 : 20:08:28

Hello Please....

w1fini
Starting Member

4 Posts

Posted - 2009-11-03 : 20:27:34

Hi,

The question is: Do you really want to calculate in your database? Where do you display the values? Shouldn't your frontend handle the calculation and only base values are stored.
To express myself. If you have value A and B. Why do you want to save value C if you can calculate it from A and B. Thats reduntant with all its possible errors. F.E. What happens if someone Changes col2? Then you need a trigger to recalculate col3.

If you really want to continue on this path you should try sub selects. That means you put the previus month in a sub select to get its value and calculate then. Something like(still need to put conditions):
Select table1.col1, table2.col2 from table1, (Select col2 from table1 as table2)

saran_d28
Starting Member

36 Posts

Posted - 2009-11-04 : 03:39:24

Hi,

Try this. If you facing any performance issue, Please use join instead of corelated sub queries.

SELECT MONTHNAME , REGION, COL1, COL2, CAST((COL1-COL2)AS DECIMAL)* 30 /COL3 FROM
(
SELECT MONTHNAME, REGION, COL1, COL2 ,
COL3 = (SELECT COL2 FROM FORUM1 WHERE MONTH(MONTHNAME) = MONTH(F.MONTHNAME)-1)
FROM FORUM1 F
) AS MAIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources