Using Pivot When There are two sum(upload),sum(pending)

Question

Post reply

Using Pivot When There are two sum(upload),sum(pending)

suryam

Mr or Mrs. 500

Points: 569
More actions
June 27, 2013 at 12:08 am

#295083

i used pivot when there was 1 sum(count) and it is working fine, but now the requirement is to do more than 1 sum. below are the required details.
My sample data (Sql server 2005)
regionbranch trdate reportcntpending
BangaloreBG40-BELGAUM06/05/2013 0 1
BangaloreBG40-BELGAUM08/05/2013 2 0
BangaloreBG40-BELGAUM09/05/2013 2 0
BangaloreBG40-BELGAUM14/05/2013 4 1
BangaloreBN03-BANGALORE06/05/2013 6 2
Required out put
Region Branch 03/06/2013 04/06/2013
Report Pending Report Pending
BangaloreBG40-BELGAUM 1000 20 1 200 30
plz help me out
Thanks
SUrya

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

You're not new here & should know the drill by now.

Please see the link in my signature - we need consumable DDL and INSERT statements to make it easier for us to help.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 2

Thanks Phill, for your great suggession.

now here below are my table details

/*************/

create table #Mis

(

region varchar(50),

branch varchar(60),

trdate datetime,

reportcnt int,

pendingcnt int

)

insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-06 00:00:00.000',0,1)

insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-08 00:00:00.000',2,0)

insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-09 00:00:00.000',2,0)

insert into #Mis values ('Chennai','BG40-BELGAUM','2013-05-14 00:00:00.000',4,1)

insert into #Mis values ('Bangalore','BN03-BANGALORE','2013-05-06 00:00:00.000',6,2)

select * from #mis

and my requirement ..i min to say out put should be..?

Region Branch 03/06/2013 04/06/2013

Report Pending Report Pending

BangaloreBG40-BELGAUM 1000 20 1 200 30

plz help..?

Thanks
SUrya

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

suryam (6/27/2013)
Thanks Phill, for your great suggession.
now here below are my table details
/*************/
create table #Mis
(
region varchar(50),
branch varchar(60),
trdate datetime,
reportcnt int,
pendingcnt int
)
insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-06 00:00:00.000',0,1)
insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-08 00:00:00.000',2,0)
insert into #Mis values ('Bangalore','BG40-BELGAUM','2013-05-09 00:00:00.000',2,0)
insert into #Mis values ('Chennai','BG40-BELGAUM','2013-05-14 00:00:00.000',4,1)
insert into #Mis values ('Bangalore','BN03-BANGALORE','2013-05-06 00:00:00.000',6,2)
select * from #mis
and my requirement ..i min to say out put should be..?
Region Branch 03/06/2013 04/06/2013
Report Pending Report Pending
BangaloreBG40-BELGAUM 1000 20 1 200 30
plz help..?

Great - that's much better.

Will the number of columns in the results be variable (because it depends on the number of Region/Branch/trDate combinations)?

Your sample output includes dates which do not appear in the input data - can you explain how these dates are defined?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 4

Actually client is going to spool this report on the basis of between transaction dates how many processed and pending according to branch and region wise. like bangalore for the date <header> 25-06-2013 <header>processed 2 <header>pending 0 same continues up to till date

25-06-2013 processed 2 pending 0 in one row header will be date

Region NameBranch Name03/06/201304/06/2013

Report CountPending CountReport CountPending Count

BangaloreBangalore100020120030

Thanks Phil

Thanks
SUrya

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

suryam (6/27/2013)
Actually client is going to spool this report on the basis of between transaction dates how many processed and pending according to branch and region wise. like bangalore for the date <header> 25-06-2013 <header>processed 2 <header>pending 0 same continues up to till date
25-06-2013 processed 2 pending 0 in one row header will be date
Region NameBranch Name03/06/201304/06/2013
Report CountPending CountReport CountPending Count
BangaloreBangalore100020120030
Thanks Phil

So you want a routine which will accept a start date and an end date and will produce an output column for every day between them? Even if there is no activity on that date?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 6

yeh phill exactly. i m having final data and i can do the same in excel sheet (i min to say manual pivoting) but requirement is to automate in sql ..already i have using pivot in one sum(cnt) and but here is to sum cases.

pivot(avg(IncomeAmount) for incomeday in([Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun])) as avgday

or we can use case statement

pl suggest

Thanks
SUrya

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

OK, you need a dynamic pivot in combination with some sort of date tally - that's a bit of work. I'll try to get back to you a bit later, unless someone beats me to it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 8

suryam

Mr or Mrs. 500

Points: 569

June 27, 2013 at 3:01 am

#1627862

Thanks waiting for....:hehe:

Thanks
SUrya

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 9

suryam

Mr or Mrs. 500

Points: 569

June 27, 2013 at 4:32 am

#1627899

can any body help me out...?

Thanks
SUrya

Phil Parkin SSC Guru Points: 246985 More actions · Answer 10

I just had a look at this. I can build a dynamic pivot to get you either 'report count' or 'report pending' figures - but not both on the same row.

If that is a definite requirement, I think you'll need help from someone else 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

suryam Mr or Mrs. 500 Points: 569 More actions · Answer 11

Yeh Phil ..thanx for ur time and discussion ..

Though i got some more info about Pivot as u(Phill) know we can use crosstab fun for the desired output while only one sum or agregate. but the fact here is two agregate..can we..... do ..or..?

plz help me guys

Thanks in advance

Thanks
SUrya