August 27, 2013 at 2:28 am
Hi,
I need help for calculaing percentage Increase or Decrease in Price of some Product for last two years.
I have some table gg_Price_T
DateProduct Price
1-01-2013 Bingo 50.07
2-01-2013 Tingo 52.07
3-01-2013 Zingo 56.07
.. .
.. .
27-08-2013Sigo 59.07
It is having different price for each day and I want to know with what percentage this price differs every year, If we take it for last two years 2012 and 2011
August 27, 2013 at 6:46 am
This may be what you are looking for:
USE TEMPDB
GO
IF EXISTS (SELECT TOP 1 1 FROM sys.tables WHERE name = 'test_prices')
DROP TABLE Test_Prices
GO
CREATE TABLE Test_prices (
[Date] date,
Product nvarchar(100),
Price decimal (10,2))
GO
INSERT INTO tempdb.dbo.Test_prices
SELECT '01-01-2013', 'Bingo', 50.07 UNION ALL
SELECT'02-01-2013', 'Bingo', 52.07 UNION ALL
SELECT'01-01-2013', 'Zingo', 56.07 UNION ALL
SELECT'02-01-2013', 'Zingo', 55.07
GO
WITH CTE_NumerateDates AS (
SELECT ROW_NUMBER() OVER (Partition BY Product ORDER BY Date ASC) As RowNum,
Product, [Date],[Price]
FROM tempdb.dbo.test_prices
)
SELECT cte1.*,
CAST(((cte1.Price - cte2.price) / cte1.price) * 100 AS decimal(10,2)) AS [PriceChange%]
FROM CTE_NumerateDates cte1
LEFT JOIN CTE_NumerateDates cte2 ON cte1.RowNum = cte2.RowNum + 1 AND cte1.product = cte2.product
MCITP SQL 2005, MCSA SQL 2012
August 27, 2013 at 8:03 am
Make sure you consistently decide how to perform the increase. Are you using the current price as the basis or the oldest price? It can make a difference.
3 -> 4.
Is this a 33% increase or a 25% increase? Depending on where you measure from.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply