How to Calculate a column value, based on previous column value

Question

Post reply

How to Calculate a column value, based on previous column value

Zond Sita

SSC Veteran

Points: 253
More actions
April 10, 2013 at 1:18 pm

#292305

Hello All,
Could you please help me here with this sql query,
drop table #tempo1
create table #tempo1 (ActNo int, PlanName varchar(20), ProcessMonth varchar(8))
insert into #tempo1 values (100,'Keeper','2012-Jul')
insert into #tempo1 values (101,'Keeper','2012-Jul')
insert into #tempo1 values (102,'Valuer','2012-Jul')
insert into #tempo1 values (103,'Keeper','2012-Jul')
insert into #tempo1 values (104,'Valuer','2012-Jul')
insert into #tempo1 values (105,'Keeper','2012-Jul')
insert into #tempo1 values (110,'Keeper','2012-Jul')
insert into #tempo1 values (100,'Keeper','2012-Oct')
insert into #tempo1 values (101,'Valuer','2012-Oct')
insert into #tempo1 values (102,'Valuer','2012-Oct')
insert into #tempo1 values (103,'Keeper','2012-Oct')
insert into #tempo1 values (104,'Keeper','2012-Oct')
insert into #tempo1 values (105,'Valuer','2012-Oct')
insert into #tempo1 values (106,'Valuer','2012-Oct')
output will show below threecolumns, first two is straight , but the fourth column shoud be calculate based on second column (if secondvalue is same as fourth then just add literal 'Cnt 2' otherwie 'Cnt 1' so that i.e., if secondcolumn value is 'Keeper' and 4th column value is also 'Keeper' then show as 'Keeper Cnt2' sameway with Valuer as well, if it is different in july and oct then just oct column value with 'Cnt 1' literal ),
for exmple
AcctNo, JulyPtcpt, OctPtcpt
100, 'Keeper', 'Keeper Cnt 2' ---> (Both July and Oct same so added Cnt 2 at the end )
101, 'Keeper','Valuer Cnt 1' ---> (Both July and Oct not same, so added Cnt 1 at the end )
102, 'Valuer','Valuer Cnt 2'
103, 'Keeper','Keeper Cnt 2'
104, 'Valuer','Keeper Cnt 1'
105, 'Keeper','Valuer Cnt 1'
106, '' ,'Valuer Cnt 1' ---> because Not exist in prior month just leave blank or just leave with Cnt1
110, 'Keeper','' --> not exist in oct so leave blank or Just leave it as Cnt1
please help me here, i am stuck at the processmonth separation for jul/oct
Kindly assist me here,
milan

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

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

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

Is it as simple as this?

select ActNo, PlanName, COUNT(*)

from #tempo1

group by ActNo,PlanName

order by ActNo

_______________________________________________________________

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/

Zond Sita SSC Veteran Points: 253 More actions · Answer 2

Thank you for your response,

i tried your sample, it is showing two rows i mean each row is showing twice (because of plan name)please see below

ActNoPlanName(No column name)

100Keeper2

101Keeper1

101Valuer1

102Valuer2

103Keeper2

104Keeper1

104Valuer1

105Keeper1

105Valuer1

106Valuer1

110Keeper1

here if expecting one look like is (see acct no is apears only once)

ActNoJulyResultOctorberResult

-----------------------------

100KeeperKeeper 2

101KeeperValuer 1

102ValuerValuer 2

103KeeperKeeper 2

104Valuer Keeper 1

105KeeperValuer 1

106Valuer 1

110Keeper 1

Thank you again

milano

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

Ahh gotcha. Don't understand the usefulness of something like this but this is how you would do the query.

select ActNo, max(case when ProcessMonth = '2012-Jul' then PlanName else '' end) as JulyResult

, MAX(case when ProcessMonth = '2012-Oct' then PlanName else '' end) as OctResult

, case when max(case when ProcessMonth = '2012-Jul' then PlanName else '' end) = MAX(case when ProcessMonth = '2012-Oct' then PlanName else '' end) then 2 else 1 end