This does not directly answer David's question, but here is an example
using rollup in SQL Server.
SQL Server provides a grouping function which indicates the "level" of
grouping, 0 for detail, 1 for (sub)total. For each column in [group by
with rollup], a grouping() function is available. The last bit of SQL in
the HAVING clause chooses which grouping levels to display. I put
together the groupings so that the selection is easier to grasp:
grouping 000000 means all detail rows, 011111 means subtotal by date,
and 111111 is the final total.
-- SQL Server 2000/2005 syntax using ROLLUP and GROUPING functions
-- Provides subtotals by labor date and final totals.
create procedure bw_chargedtodepartment
(@indept varchar(5),
@infromdate varchar(10),
@inthrudate varchar(10)
) as
--declare @infromdate varchar(10)
--declare @inthrudate varchar(10)
--declare @todept varchar(5)
--select @infromdate = '3/1/2008'
--select @inthrudate = '4/30/2008'
--select @todept = '229'
declare @fromdate datetime
declare @thrudate datetime
set nocount on
select @fromdate = convert(datetime,@infromdate)
select @thrudate = convert(datetime,@inthrudate)
select
case when a.adjustedapplydate is null then 'Grand' else
convert(varchar(10),a.adjustedapplydate,101) end as labor_date,
case when a.personnum is null then 'Total' else a.personnum end as
clock_number,
a.personfullname as name,
a.laborlevelname2 as work_dept,
b.homelaborlevelname2 as home_dept,
a.paycodename as pay_code,
sum(1.0*a.timeinseconds/3600) as timeinhours,
-- Added for clarity, not necessary for processing.
cast(grouping(a.adjustedapplydate) as char(1)) +
cast(grouping(a.personnum) as char(1)) +
cast(grouping(a.personfullname) as char(1)) +
cast(grouping(a.laborlevelname2) as char(1)) +
cast(grouping(b.homelaborlevelname2) as char(1)) +
cast(grouping(a.paycodename) as char(1)) as groupings
from wfcdb.dbo.vp_totals a
join wfcdb.dbo.vp_employee b on (a.personnum=b.personnum)
where a.paycodename = 'WORKED'
and a.adjustedapplydate between @fromdate and @thrudate
and upper(left(b.payrulename,6))='HOURLY'
and a.laborlevelname2 = @indept
-- The query is sorted by the fields in the GROUP BY.
group by a.adjustedapplydate, a.personfullname, a.personnum,
a.laborlevelname2,
a.paycodename, b.homelaborlevelname2
with rollup
having sum(a.timeinseconds) <> 0 and
cast(grouping(a.adjustedapplydate) as char(1)) +
cast(grouping(a.personnum) as char(1)) +
cast(grouping(a.personfullname) as char(1)) +
cast(grouping(a.laborlevelname2) as char(1)) +
cast(grouping(b.homelaborlevelname2) as char(1)) +
cast(grouping(a.paycodename) as char(1))
in ('000000','011111','111111')
--grant execute on bw_chargedtodepartment to kronread
--drop procedure bw_chargedtodepartment
GO
Loyd Goodbar
Business Systems
BorgWarner Shared Services
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Wednesday, August 06, 2008 8:01 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL : subtotals and total
Walden H. Leverich wrote:
I'm not in a position to check, does db2 have the rollup function?
That's what you're looking for.
ROLLUP and CUBE came with 6.1.
Joe
midrange.com
