Need Help for the following problem

Question

Need Help for the following problem

mdsharif532

Old Hand

Points: 367
More actions
March 26, 2014 at 1:46 pm

#285021

I have the Baseline number and I have to populate the next columns based on the following formula
2013 = Baseline + Baseline*0.055
2014 = 2013 + 2013*0.055
2015 = 2014 + 2014*0.055
2016 = 2015 + 2015*0.055
PROGRAM Baseline 2013 2014 2015 2016
App 8 8 9 9 10
Prog 165174184194204
Thanks,
Mohammed

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

mdsharif532 (3/26/2014)
I have the Baseline number and I have to populate the next columns based on the following formula
2013 = Baseline + Baseline*0.055
2014 = 2013 + 2013*0.055
2015 = 2014 + 2014*0.055
2016 = 2015 + 2015*0.055
PROGRAM Baseline 2013 2014 2015 2016
App 8 8 9 9 10
Prog 165174184194204
Thanks,
Mohammed

Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 2

mdsharif532 (3/26/2014)
I have the Baseline number and I have to populate the next columns based on the following formula
2013 = Baseline + Baseline*0.055
2014 = 2013 + 2013*0.055
2015 = 2014 + 2014*0.055
2016 = 2015 + 2015*0.055
PROGRAM Baseline 2013 2014 2015 2016
App 8 8 9 9 10
Prog 165174184194204
Thanks,
Mohammed

Hi --

I updated your answer to make it more readable, but there is nowhere enough information here for us to figure out what is going on, much less help you to solve the issue.

It looks as if you want to multiply the previous year by 1.055 to get a number for the current year, and you want to pivot the information? the "?" is there because I don't have enough information in front of me to actually determine if you want that.

Sean Lange has often posted in his signature how to present a question to get a good answer. Please do so and we'll be happy to try to help.

Thanks

John.

PS Edited to note that Sean and I typed at the same time!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

This is what I understood from your requirement. I agree with Sean and John that you need to post more details. Along with the solution, there's DDL and sample data, but it might not resemble your data. That's why it's important for you to provide it.

CREATE TABLE #SampleData(

PROGRAM varchar(10),

Baseline int,

[2013]int,

[2014]int,

[2015]int,

[2016]int)

INSERT #SampleData(PROGRAM, Baseline)

SELECT 'App', 8 UNION ALL

SELECT 'Prog', 165

SELECT * FROM #SampleData

--Option1

UPDATE #SampleData SET

[2013]= ROUND( Baseline * 1.055, 0),

[2014]= ROUND( (Baseline * 1.055) * 1.055, 0),

[2015]= ROUND( ((Baseline * 1.055) * 1.055) * 1.055, 0),

[2016]= ROUND( (((Baseline * 1.055) * 1.055) * 1.055) * 1.055, 0)

--Option2

UPDATE #SampleData SET

[2013]= ROUND( (Baseline * POWER(1.055,1)), 0),

[2014]= ROUND( (Baseline * POWER(1.055,2)), 0),

[2015]= ROUND( (Baseline * POWER(1.055,3)), 0),

[2016]= ROUND( (Baseline * POWER(1.055,4)), 0)

SELECT * FROM #SampleData

GO

DROP TABLE #SampleData

If your table design looks like this, a DB redesign should be considered.;-)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mdsharif532 Old Hand Points: 367 More actions · Answer 4

Thank you so much for all your help I really appreciate it.