Unanswered: CASE / WHEN in view
I'm trying to sort out what I call a db design issue in our NAV solution, by compensating with a view.
I have three tables
Employee: EmpID ,Name, Addy, Bank account, Dept ID
Dept: Dept ID, Dept Name, Dept Leader
RelocDept: Dept ID, EmpID, FromDate, ToDate
Normally, the Dept ID in employee simply depicts the department the employee is assigned.
BUT, recently the RelocDept has been introduced, which contains special periodes in which an employee can be relocated to a different departmenfor for a period (though never permanently).
So What I need to do is correct the view used against the NAV database, to check the relocdept, determine if the employee is currently recolated, and replace Dept ID with the one in RelocDept instead of the one in Employee.
I was thinking something in the lines of:
I've been haggling at this for a few days now, and no matter how I twist and turn it, I can't seem to get the CASE clause to work.
Dept = CASE
WHEN (some select query I cant figure out, where currentdate is between from and to) <> null THEN = (some select query I cant figure out, where currentdate is is between from and to)
ELSE [Dept ID]
FROM Employee (NOLOCK)
Cheers and many thanks in advance,
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....