Trying to use case when to pivot data

Question

Post reply

Trying to use case when to pivot data

NineIron

SSChampion

Points: 12536
More actions
June 3, 2013 at 7:11 am

#274591

I'm trying to pivot this data but, I can't seem to get rid of the "extra zeros".
Here's my query.
SELECT provider, month_name,
case when WeekNumberOfMonth='1' then ArrivedVisits else 0 end as 'Week1',
case when WeekNumberOfMonth='2' then ArrivedVisits else 0 end as 'Week2',
case when WeekNumberOfMonth='3' then ArrivedVisits else 0 end as 'Week3',
case when WeekNumberOfMonth='4' then ArrivedVisits else 0 end as 'Week4',
case when WeekNumberOfMonth='5' then ArrivedVisits else 0 end as 'Week5'
from VISITS
group by provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits
order by provider, fiscal_month
)
create table VISITS
(
provider varchar(100),
fiscal_month int,
month_name varchar(25),
WeekNumberOfMonth int,
ArrivedVisits int
)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',6)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',6)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',6)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',5)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',6)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',1)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',2)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',3)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',4)
insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',5)

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

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

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

Just add a MAX to your columns.

SELECT provider, month_name,

MAX(case when WeekNumberOfMonth='1' then ArrivedVisits else 0 end) as 'Week1',

MAX(case when WeekNumberOfMonth='2' then ArrivedVisits else 0 end) as 'Week2',

MAX(case when WeekNumberOfMonth='3' then ArrivedVisits else 0 end) as 'Week3',

MAX(case when WeekNumberOfMonth='4' then ArrivedVisits else 0 end) as 'Week4',

MAX(case when WeekNumberOfMonth='5' then ArrivedVisits else 0 end) as 'Week5'

from VISITS

group by provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits

order by provider, fiscal_month

You may want to take a look at these articles:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

PS. I couldn't test the code because you missed the ArrivedVisits value in the inserts, But it was a nice thing that you included it.

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

NineIron SSChampion Points: 12536 More actions · Answer 2

I still get the zeros with MAX. Here's the whole insert.........

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',1,12)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',2,15)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',3,16)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',4,14)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',5,8)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',1,3)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',2,10)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',3,14)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',4,9)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',5,9)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',1,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',2,15)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',3,22)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',4,13)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',5,7)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',6,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',1,13)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',2,18)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',3,17)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',4,8)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',5,13)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',1,6)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',2,8)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',3,15)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',4,15)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',5,16)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',1,3)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',2,19)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',3,20)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',4,9)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',5,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',6,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',1,22)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',2,20)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',3,22)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',4,13)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',5,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',1,41)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',2,67)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',3,43)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',4,68)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',5,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',1,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',2,41)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',3,36)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',4,28)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',5,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',1,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',2,39)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',3,32)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',4,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',5,38)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',6,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',1,43)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',2,48)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',3,36)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',4,33)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',5,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',1,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',2,21)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',3,22)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',4,31)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',5,15)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',1,16)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',2,27)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',3,45)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',4,26)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',5,36)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',6,0)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',1,35)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',2,34)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',3,49)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',4,48)

insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',5,0)

NineIron SSChampion Points: 12536 More actions · Answer 3

I got it now. I needed to exclude WeekOfMonthNumber and ArrivedVisits out of the group by.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is what would help, based on the sample data you provided what are you expecting to see from the query? Please provide that as a separate table and set of insert statements. This will give us something to see and test against.

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

NineIron (6/3/2013)
I got it now. I needed to exclude WeekOfMonthNumber and ArrivedVisits out of the group by.

You're right, I didn't notice you had all the columns in your group by clause.

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

NineIron SSChampion Points: 12536 More actions · Answer 6

NineIron

SSChampion

Points: 12536

June 3, 2013 at 8:16 am

#1620574

Thanx for your help and the articles.