How to show variance in column values

Question

How to show variance in column values

vigneshkumart50

Ten Centuries

Points: 1205
More actions
February 19, 2014 at 7:32 am

#396289

Hi,
This is my table structure
--=====
create table calculate(
ID int identity (1,1),
PreviousYear_Profit float not null,
)
insert into calculate values (12500)
insert into calculate values (22700)
insert into calculate values (18500)
insert into calculate values (25800)
--======
I want to calculate variance and expected output is
ID PreviousYear PriorVersion Variance
1 12500 sum(PreviousYear) PreviousYear-PriorVersion
2 22700 sum(PreviousYear) PreviousYear-PriorVersion
3 18500 sum(PreviousYear) PreviousYear-PriorVersion
4 25800 sum(PreviousYear) PreviousYear-PriorVersion
I don't have PriorVersion and Variance columns.
PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)
I can query if I have column, since I don't have these two column how could I possibly achieve the output using my existing table schema. Can anyone assist me in this.

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

vigneshkumart50 (2/19/2014)
Hi,
This is my table structure
create table calculate(
ID int identity (1,1),
PreviousYear_Profit float not null,
)
insert into calculate values (12500)
insert into calculate values (22700)
insert into calculate values (18500)
insert into calculate values (25800)
I want to calculate variance and expected output is
ID PreviousYear PriorVersion Variance
1 12500 12000 5000
2 22700 22500 200
3 18500 18500 0
4 25800 25650 150
I don't have PriorVersion and Variance columns.
PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)
I can query if I have column, since I don't have these two column how could I possibly achieve the output using my existing table schema. Can anyone assist me in this.

Hi and welcome to the forums. Thank you for posting ddl and consumable data. I don't understand how you come up with PriorVersion. You said sum(PreviousYear) but the output you posted doesn't match with that at all. How do you know that PriorVersion for ID 1 is 12000? Also, I think your Variance is a typo for that one (should be 500 not 5,000).

_______________________________________________________________

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/

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 2

Hi,

You are correct. I disclosed some dummy raw data to show the expected output.

But this is the one that should be happening there.

PriorVersion will be like sum(PreviousYear)

Variance will be (PreviousYear - PriorVersion)

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

vigneshkumart50 (2/19/2014)
Hi,
You are correct. I disclosed some dummy raw data to show the expected output.
But this is the one that should be happening there.
PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)

Simply repeating an unclear business rule is not making it more clear.

I don't understand how you come up with PriorVersion. You said sum(PreviousYear) but the output you posted doesn't match with that at all. How do you know that PriorVersion for ID 1 is 12000?

_______________________________________________________________

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/

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 4

vigneshkumart50

Ten Centuries

Points: 1205

February 19, 2014 at 8:13 am

#1690309

Hi Sean,

Updated my post.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

vigneshkumart50 (2/19/2014)
Hi Sean,
Updated my post.

Can you update to something useful?

PriorVersion will be like sum(PreviousYear)

What does that mean? In your sample output your PriorVersion columns all equal PreviousYear_Profit now.

This will produce the results as you stated from your sample data.

select ID, PreviousYear_Profit, PreviousYear_Profit as PriorVersion, 0 as Variance

from calculate

I suspect that isn't really what you want. Help me understand what you are trying to do. The query itself is really not difficult, it is extracting what you want that is proving to be difficult here.

_______________________________________________________________

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/

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 6

Hi Sean,

I wasn't clear what I have to mention for that Priorversion column. So just mentioned the previous year. I have updated my post. Hope it helps.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

My crystal ball says this might work:

SELECT ID,

PreviousYear_Profit,

SUM( PreviousYear_Profit) OVER (PARTITION BY (SELECT NULL)) AS PriorVersion ,

SUM( PreviousYear_Profit) OVER (PARTITION BY (SELECT NULL)) - PreviousYear_Profit AS Variance

FROM calculate

This is a terrible design as you shouldn't store "Previous year" information. You should store information with a date that will identify it. Previous year for today is not the same as previous year for 2 months ago.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

vigneshkumart50 (2/19/2014)
Hi Sean,
I wasn't clear what I have to mention for that Priorversion column. So just mentioned the previous year. I have updated my post. Hope it helps.

NO. What is the business rule? You might have updated your post but it still does not explain what the calculation is based on. Are you trying to calculate a running total? Can you post the actual values of what you expect as output?

_______________________________________________________________

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/

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 9

Hi Sean,

It's not a running total. The business rule for PriorVersion is sum(PreviousYear), so that I get PriorVersion values. And variance should be done for (PreviousYear - PriorVersion).

Lee me know if you need more info.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

vigneshkumart50 (2/19/2014)
Hi Sean,
It's not a running total. The business rule for PriorVersion is sum(PreviousYear), so that I get PriorVersion values. And variance should be done for (PreviousYear - PriorVersion).
Lee me know if you need more info.

OK this is my last attempt. You don't have PreviousYear. The only column available is PreviousYear_Profit. If we take the sum of that column we get 79500. Is that what you want?

You have to remember that I can't see your screen, I can't see any data other than what you posted, I have idea what you are trying to do. I have asked repeatedly for you to post the ACTUAL values you expect as output. If there are intermediate calculations post what those values are and how you came up with it.

We are now nearly a dozen posts and 2 1/2 hours into your issue. If you would post enough information for me to help you would have been 2 hours ago.

_______________________________________________________________

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/

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 11

Hi Sean,

I am sorry and I made this drag a long way. Will post clearly soon on this.