<pre>
create procedure sp_profile_totcpu
as
-- Purpose: "Profiles" Sybase users by Total cpu usage
-- Users are grouped into ranges. The first ranges are 0 CPUs, 1 CPU,
-- 2-4 CPUs, 5-9 CPUs,
-- 10-19 CPUs, etc. The ranges become wider as the magnitude increases.

-- Note: totcpu is the number of CPUs since last sp_clearstats

-- This technique is applicable to other data. You can profile bank
-- accounts by deposit size,
-- customers by number of purchases, etc. Just replace totcpu with the
-- column
-- to group by and add in the other columns to sum. Don't change any of
-- the funny character stuff.
-- The only restriction is the new column must be an integer 0 or
-- greater (no negatives).
-- The width and indentation of the pyramid are set by the "24" and "11"
-- in the calculation,
-- which you can adjust.

-- Written by Bob Munson, 74451.1500@compuserve.com
-- This procedure appears in an article in Exploring Sybase SQL Server
-- magazine.
-- 9420 Bunsen Parkway, Suite 300; Louisville, KY  40220

Select 
  "Totcpu Range"= 
    substring(space(11-char_length(convert(varchar,totcpu))) +
    substring("0122255555",
    charindex(substring(convert(varchar,totcpu),1,1),"0123456789"),1) +
    replicate("0",char_length(convert(varchar,totcpu))-1) +"-" +
    substring("0144499999",
    charindex(substring(convert(varchar,totcpu),1,1),"0123456789"),1) +
    replicate("9",char_length(convert(varchar,totcpu))-1),1,24),
 "Users"=count(*),
 "Sum totcpu"=sum(totcpu),
 "Sum totio"=sum(totio)

From master..syslogins
Group by
   -- This is the same calculation as "Totcpu Range"
   substring(space(11-char_length(convert(varchar,totcpu))) +
   substring("0122255555",
   charindex(substring(convert(varchar,totcpu),1,1),"0123456789"),1) +
   replicate("0",char_length(convert(varchar,totcpu))-1) +"-" +
   substring("0144499999",
   charindex(substring(convert(varchar,totcpu),1,1),"0123456789"),1) +
   replicate("9",char_length(convert(varchar,totcpu))-1),1,24)

