April 10, 2013 at 1:18 pm
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
April 10, 2013 at 1:26 pm
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/
April 10, 2013 at 1:38 pm
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
April 10, 2013 at 1:58 pm
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
from #tempo1
group by ActNo
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply