Fields in a table to generate other 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

General SQL Server Forums

New to SQL Server Programming

Fields in a table to generate other fields

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 09:31:49

I am building a table where an advertiser pays for anywhere from one month to three months. So I have two fields one START_DATE and other END_DATE. But Then I also have a TOTAL_Price.

How can I have a field that will generate itself to show me the per month charge.

for example someone does three months of advertising and pays $100, how can I have a pox produce itself 100/3 = 33.3

Any ideas?

thanks for the help!

best,
GG

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 09:48:53

SELECT (TOTAL_Price / (DATEDIFF(m, START_DATE, END_DATE)))

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 11:29:13

that is great thank you.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 11:31:50

Welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-12 : 11:34:45

Two things to look out for

1. if you need to decimal result it should be


SELECT (TOTAL_Price * 1.0 / (DATEDIFF(m, START_DATE, END_DATE)))

2. If you need to calculate actual months elapsed rather you may be better off using


SELECT (TOTAL_Price * 30.0 / (DATEDIFF(dd, START_DATE, END_DATE)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources