August 19, 2009 at 4:18 pm
Hello,
I need some help please. I have 2 temp tables like so
CREATE TABLE #fSales
(
season int,
Sales Money,
products int,
productpurchase int
)
insert into #fSales
values(1, 200, 300, 150)
insert into #fSales
values(2, 250,500, 200)
insert into #fSales
values(3, 300, 400, 400)
insert into #fSales
values(4, 400,330, 310)
insert into #fSales
values(5, 500, 200, 110)
CREATE TABLE #pSales
(
season int,
Sales Money,
products int,
productpurchase int
)
insert into #pSales
values(1, 200, 250, 100)
insert into #pSales
values(2, 250, 450, 150)
insert into #pSales
values(3, 300, 350, 350)
insert into #pSales
values(4, 400, 280, 260)
insert into #pSales
values(5, 500, 150, 60)
is there a way to calculate the percentage increase/decrease of #fsales.products compared to #psales.products and also the same thing for #fsale.productspurchase compared to #psale.productspurchase. Do i need to change the datatype to decimal? Thanks
August 20, 2009 at 4:09 am
Yes - you will have to change the datatype to decimal (unless you specifically need float).
Assuming you want to track the % increase between products and productspurchase for the same season - you'll have to join the two tables on season and compute the % increase...the code below is based on my understanding of how % increase between two values is computed and will hopefully point you in the right direction
SELECT f.season,f.products,p.products,f.productpurchase,p.productpurchase,
CASE WHEN f.products >= p.products THEN 'FProducts%Increase:'+
CAST((((f.products-p.products)/p.products)*100) as varchar(30))
ELSE 'PProducts%Increase:'+CAST((((p.products-f.products)/f.products)*100) as varchar(30))
END ProductsPercentChanges,
CASE WHEN f.productpurchase >= p.productpurchase THEN 'FProductPurchase%Increase:'+
CAST((((f.productpurchase-p.productpurchase)/p.productpurchase)*100) as varchar(30))
ELSE 'PProductPurchase%Increase:'+CAST((((p.productpurchase-f.productpurchase)/f.productpurchase)*100) as varchar(30))
END ProductsPercentChanges
FROM #fSales f INNER JOIN #pSales p ON
f.season=p.season
August 20, 2009 at 6:18 am
You can "cheat" a bit on this type of code...
SELECT STR((23-20)*100.0/20,5,1)+'%'
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 11:39 am
Nice!! I never thought of using STR()...I can now use this in a lot of places...Thanks Jeff!
August 20, 2009 at 12:03 pm
Your welcome but a word of caution... if a GUI is involved, formatting of this nature should be done in the GUI. And, never store formatted numeric data in a database... it just takes extra space and you have to "unformat" it to use it in mathematical calculations in most cases. That's a huge waste of time.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 1:32 pm
Thanks for the help, I wrote something basic to solve it but this is just very amazing guys. Thanks again!
August 23, 2009 at 1:38 pm
npatel565 (8/20/2009)
Thanks for the help, I wrote something basic to solve it but this is just very amazing guys. Thanks again!
Would you mind sharing the "something basic" please? Two way street here... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply