checking for range

hey all,
what's the best way to express this in a query?
for each employee
take the salary and determine which range the particular salary falls in.
for instance:
40k
falls between 35-40k so the category is 1
number of categories are 1-12
thanks,
rodcharSELECT Employee,
CASE
WHEN Salary BETWEEN 35000 AND 40000 THEN 1
WHEN Salary BETWEEN 40001 AND 45000 THEN 2
.
.
.
END AS "Category"
FROM [Your Table]
Or, you could have a table that stores that salary categories, and JOIN to i
t
SELECT e.Employee, c.Category
FROM [Your Table] e INNER JOIN [Salary Categories] c
ON e.Salary BETWEEN c.StartingSalary and c.EndingSalary
"rodchar" wrote:

> hey all,
> what's the best way to express this in a query?
> for each employee
> take the salary and determine which range the particular salary falls in.
> for instance:
> 40k
> falls between 35-40k so the category is 1
> number of categories are 1-12
> thanks,
> rodchar|||select case when salary < 40 and salary > 35 then 1
when salary >= 40 and salary < x then 2
when salary >= x and salary < y then 3
..
when salary > z then 12
end as 'category'|||Besides the CASE examples posted, consider a table of categories with
their ranges, and JOIN to it with the range test:
FROM Employees JOIN Ranges
ON Employees.salary >= Ranges.RangeMin
AND Employees.salary < Ranges.RangeMax
Roy
On Thu, 18 May 2006 14:35:01 -0700, rodchar
<rodchar@.discussions.microsoft.com> wrote:

>hey all,
>what's the best way to express this in a query?
>for each employee
>take the salary and determine which range the particular salary falls in.
>for instance:
>40k
>falls between 35-40k so the category is 1
>number of categories are 1-12
>thanks,
>rodchar|||thanks everyone for the help. i appreciate it a lot.
"rodchar" wrote:

> hey all,
> what's the best way to express this in a query?
> for each employee
> take the salary and determine which range the particular salary falls in.
> for instance:
> 40k
> falls between 35-40k so the category is 1
> number of categories are 1-12
> thanks,
> rodchar|||nice clean solution!! thanks.

Checking for free disk space and getting mail when it falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

Thanks

Hi Vaddi -

Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.

HTH...

checking for free disk space and getting mail , when falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

ThanksHere's what I use:
set nocount on

declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold

set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'

if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message

end

drop table #FreeSpace
go