Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    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:

    Code:
    SELECT 
    Name,
    Addy,
    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)
    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.

    Cheers and many thanks in advance,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Worrabout:
    Code:
    SELECT      employee.name
            , [Dept ID]    = COALESCE(RelocDept.[Dept ID], Employee.[Dept ID])
    FROM    Employee
    LEFT OUTER JOIN
        RelocDept
    ON    RelocDept.EmpID = Employee.EmpID
            AND GETDATE() BETWEEN RelocDept.FromDate AND RelocDept.ToDate
    You might want to play about with the date expression - it is not quite specific enough but gives you an idea.

    BTW - I am assuming here that you can't sort out that silly design at the source right?

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Quote Originally Posted by pootle flump
    Worrabout:
    BTW - I am assuming here that you can't sort out that silly design at the source right?
    LOL, yeah you're right. I would've loved to have it fixed at the source. Its just another fix applied to fix, without really being a fix, but rather a workaround. Front-end developers doing db design.

    Thanks for the help,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •