July 20, 2010 at 2:45 pm
Hi
I want to do the following calculation on 'Total Pageview' Column in a view.
My View is:
CP CODETotal Pageviews
5101412153196
5303732026
601994882655
602002928602
602013519922
602026139534
602031992608
602043493821
602051491539
602062709078
602071088093
602081457114
60209609472
60210722575
6021198393
60213337013
60214798699
60215196766
60216259407
60217946318
60219203858
60220509853
6022177314
60222287034
6022330934
60224145373
6022512095
700861982
7072527869683
79999782785
I want to do COST = ((Total Pageviews/1000000)*5)
After this I want to create a view with all three feilds CP CODE,Total Pageviews and COST.
How can I do this with T-SQL query?
Thanks
July 20, 2010 at 3:01 pm
Why don't just add a "computed column" to the original table?
Just put your math in the computed column definition.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 3:12 pm
agree, computed column is the easiest way...
If you really want to run a query, then try this:
select cp_code, total_pageviews, (1.0*total_pageviews/1000000)*5
from <view_name>
July 20, 2010 at 3:19 pm
Wouldn't this be a simpler calculation?
COST = [Total Pageviews] * 0.000005
July 20, 2010 at 3:27 pm
Michael Valentine Jones (7/20/2010)
Wouldn't this be a simpler calculation?
COST = [Total Pageviews] * 0.000005
Yes indeed but - always a "but" huh? - the other "more complex" way is better for documentation.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 3:37 pm
PaulB-TheOneAndOnly (7/20/2010)
Michael Valentine Jones (7/20/2010)
Wouldn't this be a simpler calculation?
COST = [Total Pageviews] * 0.000005Yes indeed but - always a "but" huh? - the other "more complex" way is better for documentation.
I don't see any basis for either way to be good documentation. The unit of measure is not even documented.
I suppose it could be cents or dollars or euros, but who really knows? If it is dollars, then a cost of $0.000005 per page view seems at least as straight forward as $5/1000000 per page view.
July 20, 2010 at 3:46 pm
Michael Valentine Jones (7/20/2010)
PaulB-TheOneAndOnly (7/20/2010)
Michael Valentine Jones (7/20/2010)
Wouldn't this be a simpler calculation?
COST = [Total Pageviews] * 0.000005Yes indeed but - always a "but" huh? - the other "more complex" way is better for documentation.
I don't see any basis for either way to be good documentation. The unit of measure is not even documented.
I suppose it could be cents or dollars or euros, but who really knows? If it is dollars, then a cost of $0.000005 per page view seems at least as straight forward as $5/1000000 per page view.
It better resembles the way "the business" would specify the calculation.
Moreover I'm sure you don't want to be at 2AM in the morning counting zeros to check if it's Okay or not 😀
Either way, God created different flavours so to let everyone of us have a say, isn't it? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 4:00 pm
It always has pros and cons.:-)
If it’s in my coding, I would use 0.000005 in the SQL statement to save some calculation time, but put comments with detailed calculation formula for an easy understanding. Otherwise no one would know why 0.000005 after several months.
July 20, 2010 at 4:57 pm
Thankyou very much guys ..... got it to work ..... You all were a great help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply