RE: SQL : subtotals and total

[<lgoodbar@xxxxxxxxxxxxxx>]

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