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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-14 : 10:09:47
|
Eric writes "Hello there. I'm developing an ASP site that allows users to create new logins for a certain third-party system. I'm running SQL Server 7 (SP3). None of my forum searches have turned up an answer.
I'm running into problems dynamically assinging employee numbers depending on department. For instance, if the user is a customer service rep, their employee numbers will range from 10000-15000, while if they work in the corporate offices, their numbers will range from 80000-80500, and so on.
The query will insert data from one table into another, and will select the largest number currently assigned to an employee and add 1 to it. The query I have in mind would look something like this (simplified):
INSERT EMP_TABLE1 (EMP_NUM1, USER_NAME1, DEPT1)
SELECT EMP_NUM2=CASE DEPT2 WHEN 'CSR' THEN (SELECT MAX(EMP_NUM1) FROM EMP_TABLE1
WHERE DEPT1 = 'CSR' AND EMP_NUM1 BETWEEN 100 AND 200) + 1
WHEN 'CORP' THEN (SELECT MAX(EMP_NUM1) FROM EMP_TABLE1
WHERE DEPT1 = 'CORP' AND EMP_NUM1 BETWEEN 500 AND 600) + 1
WHEN 'TECH' THEN (SELECT MAX(EMP_NUM1) FROM EMP_TABLE1
WHERE DEPT1 = 'TECH' AND EMP_NUM1 BETWEEN 800 AND 900) + 1
ELSE 999
END,
USER_NAME2, DEPT2
FROM EMP_TABLE2
Of course, this example doesn't work -- it just grabs the biggest number and adds one, or tries to throw in nulls. I need to come up with a query that recognizes what number range goes with which department. Should there be a third table referenced that contains the employee number ranges and their respective departments? How would that work?
Any thoughts?" |
|
|
|
|
|
Complex query involving CASE