Complex query with increment - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Complex query with increment

Author

Topic

laurent_sitbon
Starting Member

1 Post

Posted - 2015-02-09 : 13:09:03

Hello,

I try to do a query but it is complex

here's a quick summary
a customer signs a contract for 2013 for a price of € 1,200
each year, suffered a variable increase customer eg 2014 -> 8% more so 8 * 1200 = 96 = Final price increase so in 1296
the or it gets complicated is that it should increment

I made a little diagram ( I hope it 's understandable )

Thank you for your help.

table subscription
idsubscription, Annual_dues, dt_sign
1, 1200, 2012
2, 978, 2013

table price_adjustment
idsubscription, year, %increase, %Reduction, priceFinal
1, 2012, 10, 10, 1200 --> therefore 10%(Inc) - 10%(Red) = ((0 * 1200(Annual_dues)) /100) = 0 + 1200 = 1200
1, 2013, 8, 0, 1296 --> therefore 8%(Inc) - 0%(Red) = ((8 * 1200(Annual_dues)) /100) = 96 + 1200 = 1296
1, 2014, 9, 0, 1412.64 --> therefore 9%(Inc) - 0%(Red) = ((9 * 1296(Annual_dues)) /100) = 116.64 + 1296 = 1412.64
1, 2015, 7, 0, 1511.52 --> therefore 7%(Inc) - 0%(Red) = ((7 * 1412.64(Annual_dues)) /100) = 98.88 + 1412.64 = 1511.52

2, 2013, 8, 8, 978 --> therefore 8%(Inc) - 8%(Red) = ((0 * 978(Annual_dues)) /100) = 0 + 978 = 978
2, 2014, 9, 0, 1066.02 --> therefore 9%(Inc) - 0%(Red) = ((9 * 978(Annual_dues)) /100) = 88.02 + 978 = 1066.02
2, 2015, 7, 2, 1119.32 --> therefore 7%(Inc) - 2%(Red) = ((5 * 1066.02(Annual_dues)) /100) = 53.30 + 1066.02 = 1119.32

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 13:45:06

Something like this?


declare @table_subscription table(
idsubscription int, Annual_dues decimal(10,2), dt_sign int)

insert into @table_subscription(idsubscription,Annual_dues,dt_sign) values
(1, 1200, 2012),
(2, 978, 2013)

declare @year int = 2014
declare @incr int = 10
declare @red int = 12

select idsubscription
     , @year as [year]
	 , @incr as [%increase]
	 , @red as [%Reduction]
	 , cast(max(Annual_dues) over(partition by idsubscription order by dt_sign asc)
	   * (1.0 + (@incr-@red)/100.0) as decimal(10,2))
	  as priceFinal
from @table_subscription

I used a table variable to simulate your setup. The query takes in a target year, and increments/reductions for that year then computes the new rate. Is that what you're looking for?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources