<pre>
create procedure sp_profile_pwdate
as
-- Purpose: "Profiles" Sybase users by days since password was changed.
-- Users are grouped into ranges. The first ranges are 0 Days, 1 Day,
-- 2-4 Days, 5-9 Days,
-- 10-19 Days, etc. The ranges become wider as the magnitude increases.

-- Note: pwdate is the number of Days since last sp_password

-- This technique is applicable to other data. You can profile bank
-- accounts by deposit size,
-- customers by number of purchases, etc. Just replace pwdate 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 "14" and "7"
-- 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 "Pwdate Range"= 
   
substring(space(7-char_length(convert(varchar,datediff(dy,pwdate,getdate()))))
+
substring("0122255555",charindex(substring(convert(varchar,datediff(dy,pwdate,getdate())),1,1),"0123456789"),1)
+
replicate("0",char_length(convert(varchar,datediff(dy,pwdate,getdate())))-1)
+ "-" +
substring("0144499999",charindex(substring(convert(varchar,datediff(dy,pwdate,getdate())),1,1),"0123456789"),1)
+
replicate("9",char_length(convert(varchar,datediff(dy,pwdate,getdate())))-1),1,14),

"Users"=count(*)

From master..syslogins
Group by

-- This is the same calculation as "Pwdate Range"
   
substring(space(7-char_length(convert(varchar,datediff(dy,pwdate,getdate()))))
+
substring("0122255555",charindex(substring(convert(varchar,datediff(dy,pwdate,getdate())),1,1),"0123456789"),1)
+
replicate("0",char_length(convert(varchar,datediff(dy,pwdate,getdate())))-1)
+ "-" +
substring("0144499999",charindex(substring(convert(varchar,datediff(dy,pwdate,getdate())),1,1),"0123456789"),1)
+
replicate("9",char_length(convert(varchar,datediff(dy,pwdate,getdate())))-1),1,14)

go




