Group by question

salesrep dollars months
a1 $1.00 4/1/2004
a1 $2.00 5/2/2004
a1 $3.00 5/3/2004
a1 $4.00 6/4/2004
a1 $5.00 6/5/2004
a1 $6.00 6/6/2004
b1 $7.00 3/7/2004
b1 $8.00 3/8/2004
b1 $9.00 4/9/2004
b1 $10.00 5/10/2004
b1 $11.00 5/11/2004
b1 $12.00 6/12/2004
b1 $13.00 6/13/2004

Given the above data, I need to come up with a report that displays atotal by salesrep by month.

Any help would be greatly appreciated. Getting a total by month is where I'm hung up.Do you mean something like this?

drop table test
create table test(salem varchar(10),amount money, saledate datetime)
insert test values('a',1,'10/1/2003')
insert test values('a',1,'10/11/2003')
insert test values('b',1,'1/11/2003')
insert test values('c',1,'8/11/2003')

select salem,datepart(yyyy,saledate) 'Year',datepart(mm,saledate) 'Month',sum(amount)
from test
group by salem,datepart(yyyy,saledate),datepart(mm,saledate )|||snail - thanks for the reply.

this is one step in the process. actually what i'm looking for is a way to create a cross-tabbed view of the resulting data... I've since figured out that I'll need to create a few more tables to get the desired results.|||Just to send Brett and Blindman into fits, I'd try something like:CREATE TABLE tToeJam (
salesrep VARCHAR(5) NOT NULL
, dollars MONEY NOT NULL
, months DATETIME NOT NULL
)

INSERT INTO tToeJam (salesrep, dollars, months)
SELECT 'a1', $1.00, '4/1/2004'
UNION SELECT 'a1', $2.00, '5/2/2004'
UNION SELECT 'a1', $3.00, '5/3/2004'
UNION SELECT 'a1', $4.00, '6/4/2004'
UNION SELECT 'a1', $5.00, '6/5/2004'
UNION SELECT 'a1', $6.00, '6/6/2004'
UNION SELECT 'b1', $7.00, '3/7/2004'
UNION SELECT 'b1', $8.00, '3/8/2004'
UNION SELECT 'b1', $9.00, '4/9/2004'
UNION SELECT 'b1', $10.00, '5/10/2004'
UNION SELECT 'b1', $11.00, '5/11/2004'
UNION SELECT 'b1', $12.00, '6/12/2004'
UNION SELECT 'b1', $13.00, '6/13/2004'

DECLARE @.cSQL VARCHAR(2000)
, @.cRep VARCHAR(5)

SELECT @.cSQL = 'SELECT Convert(CHAR(6), months, 112)'

DECLARE zReps CURSOR FOR SELECT DISTINCT
salesrep
FROM tToeJam
ORDER BY salesrep

OPEN zReps
FETCH zReps INTO @.cRep
WHILE 0 = @.@.fetch_status
BEGIN
SELECT @.cSQL = @.cSQL + ', Sum(CASE WHEN ''' + @.cRep + ''' = salesrep THEN dollars END) AS ''' + @.cRep + ''''
FETCH zReps INTO @.cRep
END

CLOSE zReps
DEALLOCATE zReps

SELECT @.cSQL = @.cSQL + ' FROM tToeJam GROUP BY Convert(CHAR(6), months, 112)'

EXECUTE (@.cSQL)

DROP TABLE tToeJam-PatP|||Bluh. Spurgle-pop! Glubbity glubbity glubbity sneerp!

P', pu', puh', puhblagablech!

select salesrep,
sum(case when month(months) = 1 then dollars else 0 end) as Jan,
sum(case when month(months) = 2 then dollars else 0 end) as Feb,
sum(case when month(months) = 3 then dollars else 0 end) as Mar,
sum(case when month(months) = 4 then dollars else 0 end) as Apr,
sum(case when month(months) = 5 then dollars else 0 end) as May,
sum(case when month(months) = 6 then dollars else 0 end) as Jun,
sum(case when month(months) = 7 then dollars else 0 end) as Jul,
sum(case when month(months) = 8 then dollars else 0 end) as Aug,
sum(case when month(months) = 9 then dollars else 0 end) as Sep,
sum(case when month(months) = 10 then dollars else 0 end) as Oct,
sum(case when month(months) = 11 then dollars else 0 end) as Nov,
sum(case when month(months) = 12 then dollars else 0 end) as Dec
from yourtable
where year(month) = 2004
group by salesrep

...aaahhhhh. Now I feel better...|||Ohhhhhh! That was good! Definitely worth it!

-PatP