IF we had an IF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF we had an IF

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-11-24 : 22:33:07

I want to display a list of all employees.

Employees are of different types:

type#1 Can only work a certain # of days in a month
type#2 Are on emergency call, and can only be called a certain # of times per month.

In addition, ALL employees have a start and end work period, so regardless of their type, if they have 'expired' they should not be returned in the select.

Table:

Employees
employeeID firstname typeID maxDays daysWorked maxCalls daysCalled startDate, endDate

I seem to require an IF statement, IF the user is of type#1 I need to make sure he hasn't worked more than 'maxDays' and IF he is type#2 I have to make sure he hasn't been called more than 'maxCalls', all the while making sure the users start/end work period is during the current day, getDate().

SELECT *
FROM EMployees
WHERE
getDATE() between startDate and endDate

How do I check for the type#1 and type#2 in 1 select?

would the 'case' statement work here?

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 23:32:24

CASE statement (see BOL)

Not at all sure what you want to do to the retrieved data based on the type, but it's something like:


SELECT EmployeeID, firstname, 
  CASE  WHEN typeID = 1 AND daysWorked >= maxDays THEN 'Overworked' 
        WHEN typeID = 1 AND daysWorked < maxDays THEN 'Underworked'
        WHEN typeID = 2 AND .... etc.
  END AS MyTypeCatagory

Alternatively, you could test for the condition in the where statement for each result set you need. If you need more than 1 result set, you could union the results

SELECT employeeID, firstname, 'Overworked' AS MyTypeCatagory
FROM EMployees
WHERE typeID = 1 AND daysWorked >= maxDays
UNION ALL
SELECT employeeID, firstname, 'Underworked' AS MyTypeCatagory
FROM EMployees
WHERE TYPEid = 1 and DAYSwORKED < maxDays

and so on....

HTH,

Sam

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-11-24 : 23:46:45

Sam,

Thanks for the help.

yes I need to get the rows, so the case statement I guess will be of no use to me since I don't need to return 'Overworked' etc in the resulting row, rather I need FILTER the results which your UNION method does.

The query I have to make is going to be fairly big so I don't really want to repeat it 3 times using UNION..hmmm..

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 23:55:15

I'm not sure what kind of rowset you would like to return.

Can you post a sample rowset that you would like the query to return?

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 23:58:46

How about returning the rows that meet the availability criteria?


SELECT *
FROM  Employees
WHERE GETDATE() BETWEEN startDate AND endData
AND (
    (typeID = 1 AND daysWorked <= maxDays)
     OR
    (typeID = 2 AND daysCalled <= maxCalls)
     )

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-11-25 : 00:10:02

bingo!

Ok that solution will really make me sleep ALLOT better now!

Thanks a bundle SamC!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources