multi row aggregates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multi row aggregates

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-01-15 : 20:55:38

My database table stores 0 or more rows for a given company (companyID)

example:

companyID username hours_worked date
1 joe 1 1/12/04
2 mark 3 1/12/04
2 tony 5 1/12/04

I need 2 different types of reports.

Report#1
Display a given months list of hours, grouped by companyID (multi-row per
company allowed)

Report#2
Display a given months list of hours, grouped by companyID, ONE summary row
per company.

DavidD
Yak Posting Veteran

73 Posts

Posted - 2004-01-15 : 21:56:31

Think this is what you are after....

Report#1
create proc MultiRow @startdate datetime, @enddate datetime
as
select CompanyID, hours_worked
from table
where date between @startdate and @enddate
order by companyid

Report#2
create proc SingleRow @startdate datetime, @enddate datetime
as
select CompanyID, sum(hours_worked)
from table
where date between @startdate and @enddate
group by companyid
order by companyid

Regards
David

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-01-16 : 12:43:55

will that combine the results of multi-rows for a given companyID into 1 row? From what it looks like, it will just output all the companyid's, just ordered.

mfemenel
Professor Frink

1421 Posts

Posted - 2004-01-16 : 12:57:14

Unless I'm misreading this you could do it all in 1.
You could also use the same query with rollup to drive a summary line as well.

create table tablename
(
companyid int,
employee varchar(30),
hours_worked int,
thedate smalldatetime
)

insert into tablename(companyid,employee,hours_worked,thedate)
values(2,'tony',5,'1/12/2004')

insert into tablename(companyid,employee,hours_worked,thedate)
values(2,mark,3,'1/12/2004')

insert into tablename(companyid,employee,hours_worked,thedate)
values(1,joe,1,'1/12/2004')

--Report 1 & 2
select companyid,month(thedate) MonthWorked,sum(hours_worked)HoursWorked
from tablename
group by companyid,month(thedate)
--with rollup

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources