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.
| Author |
Topic |
|
Maverick_
Posting Yak Master
107 Posts |
 Posted - 2011-11-14 : 07:02:41
|
Hi all,I am trying to produce a difference in terms of percentage for the two items in colour below for my calculation: sum(fm1.feature_quantity * ri1.item_total * cic1.bid_rate) - oi1.original_value as Variance_in_poundBut I am not sure how to write the SQL for it. I tried looking on the net but couldn't see a relevant example and I tried my own example.Any help?  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 07:05:11
|
do you mean this?sum(fm1.feature_quantity * ri1.item_total * cic1.bid_rate - oi1.original_value) as Variance_in_pound ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2011-11-14 : 07:07:33
|
| Hi Visakh,Yes that currently gives me the difference in terms of value. I now how to figure out how to show the same difference in terms of percentage. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 07:09:25
|
quote:
Originally posted by Maverick_
Hi Visakh,Yes that currently gives me the difference in terms of value. I now how to figure out how to show the same difference in terms of percentage.
should that besum(fm1.feature_quantity * ri1.item_total * cic1.bid_rate - oi1.original_value)*100.0/nullif(sum(oi1.original_value),0) as Variance_in_pound ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-14 : 07:09:55
|
| Depends on what you have as your data.If you have oi1 table giving the value and are joining to a lot of rows from the other tables to sum the differences and using a group by it would be sum(fm1.feature_quantity * ri1.item_total * cic1.bid_rate) - max(oi1.original_value) as Variance_in_pound==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 07:18:43
|
quote:
Originally posted by nigelrivett
Depends on what you have as your data.If you have oi1 table giving the value and are joining to a lot of rows from the other tables to sum the differences and using a group by it would be sum(fm1.feature_quantity * ri1.item_total * cic1.bid_rate) - max(oi1.original_value) as Variance_in_pound==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
but that wont still give the percentage does it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2011-11-14 : 07:29:58
|
| Hi guys, I will test that out and let you guys know! Thanks :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 07:31:56
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
SQL for percentage (%) differnce between two?