how to get calculate data based on other column in same table

Question

Post reply

how to get calculate data based on other column in same table

asita

SSCarpal Tunnel

Points: 4144
More actions
December 17, 2012 at 1:49 pm

#266262

Hello All,
Could you please shed some light on this, please help me it was a question asked by my master,
create table TestTask1 (Name varchar(1), Territory Varchar(1), sales_amount int);
insert into TestTask1 values ('A','X',100)
insert into TestTask1 values ('B','X',200)
insert into TestTask1 values ('C','X',200)
insert into TestTask1 values ('D','X',300)
insert into TestTask1 values ('E','X',400)
insert into TestTask1 values ('F','Y',300)
insert into TestTask1 values ('G','Y',300)
insert into TestTask1 values ('H','Y',500)
insert into TestTask1 values ('I','Y',600)
insert into TestTask1 values ('J','Z',200)
insert into TestTask1 values ('K','Z',700)
select * from TestTask1
would like to get below data from the above data
NameTerritorysales_amountColAColBColCColD
AX1001111
BX2002221
CX2003222
DX3004433
EX4005544
FY3001111
GY3002112
HY5003323
IY6004434
JZ2001111
KZ7002222
is it possible or it is some sort of stupid question
please help me your valuable throughts

Viewing 8 posts - 1 through 7 (of 7 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Can you explain the logic that is used to get the values for the computed columns?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Hi Adi,

Thanks for your response, actually one guy asked me to do it,

i think it will be colA is Ranking on territory,

for col2 it will be based on sales_amount (duplicate value consideration)

same way col3 but some thing is different

not sure about col4

sorry for this kind of poor explain, but this is what the guy told me to hand it to me

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

ColA looks like just a ROW_NUMBER for each Territory.

select *, ROW_NUMBER() over(Partition by Territory order by Name) as ColA

from TestTask1

for col2 it will be based on sales_amount (duplicate value consideration)
same way col3 but some thing is different
not sure about col4

You are going to have to explain the rest of it in some way that can be understood. Something is different and not sure are way too vague to code against. I can make something up but I doubt it would be much use. 🙂

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 4

well my colA and ColB resulsts seem to Me to be correct;

your expected results for ColB, where it's supposed to be by rank, doesn't match the data, i think.

ColC and ColD are not yet defined, not sure what they are supposed to be.

if you cannot define it, we cannot help much.

select

TestTask1.Name,

TestTask1.Territory,

TestTask1.sales_amount,

ROW_NUMBER() OVER (PARTITION BY TestTask1.Territory ORDER BY TestTask1.Territory,TestTask1.sales_amount) As ColA,

RANK() OVER (PARTITION BY TestTask1.Territory ORDER BY TestTask1.sales_amount ) As ColB,

DENSE_RANK() OVER (PARTITION BY TestTask1.Territory ORDER BY TestTask1.sales_amount ) As ColC,

DENSE_RANK() OVER (PARTITION BY TestTask1.Territory ORDER BY TestTask1.sales_amount ) As ColD

from TestTask1

ORDER BY TestTask1.Name,ColA

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

Hi Sean,

yeah you are correct.

but the thing is, we can take any assumption but the out put need to match

just require to get that outcome, it is really a weird question i know, but i don't know what to tell to you

thanks

asittti

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

asita (12/17/2012)
Hi Sean,
yeah you are correct.
but the thing is, we can take any assumption but the out put need to match
just require to get that outcome, it is really a weird question i know, but i don't know what to tell to you
thanks
asittti

Well then you don't even need a table. Just hard code a select statement and you get that exact output. 😛

The point is that without some explanation it is all speculative. So since your first asked only...

is it possible or it is some sort of stupid question

I will answer that question. Yes it is possible, once there are details for the desired output. It not some sort of stupid question, instead it is only a portion of a complete question. There is no clear cut answer because the question itself is incomplete. In other words your "master" has asked you to provide an answer to a question is not well formed.

This thread reminds me of this article. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

_______________________________________________________________

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/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Obviously this is some sort of diabolically silly test of your knowledge of windowing functions:

create table #TestTask1 (Name varchar(1), Territory Varchar(1), sales_amount int);

insert into #TestTask1 values ('A','X',100)

insert into #TestTask1 values ('B','X',200)

insert into #TestTask1 values ('C','X',200)

insert into #TestTask1 values ('D','X',300)

insert into #TestTask1 values ('E','X',400)

insert into #TestTask1 values ('F','Y',300)

insert into #TestTask1 values ('G','Y',300)

insert into #TestTask1 values ('H','Y',500)

insert into #TestTask1 values ('I','Y',600)

insert into #TestTask1 values ('J','Z',200)

insert into #TestTask1 values ('K','Z',700)

SELECT Name, Territory, sales_amount

,ROW_NUMBER() OVER (PARTITION BY Territory ORDER BY Name)

,RANK() OVER (PARTITION BY Territory ORDER BY sales_amount)

,DENSE_RANK() OVER (PARTITION BY Territory ORDER BY sales_amount)

,NTILE(4) OVER (PARTITION BY Territory ORDER BY sales_amount)

FROM #TestTask1

DROP TABLE #TestTask1

You should demand that your "master" (are you a slave or what?) explain what he wants in the last column. If he cannot, then you should get points for arguing that if you cannot make a business case for the computation than by rights he shouldn't be asking.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St