locked
SQL Theory Poser RRS feed

  • Question

  • Hi there,

    SQL Server 2005. MS Dynamics NAV 5.0.

    I have a table in the following format
    NAME - BEGIN - END
    SALES - 1000-2999
    TRADING - 3000-4999
    SERVICE - 5000-7999
    ALL - 1000-7999

    And a second table with each individual of each department and a number code that determines which department they are in so for eg.

    Name - Code
    JonA - 1200
    PaulB - 3200
    ChrisC - 6500.

    These are tables(Views) derived live from the Dynamics NAV Dimension Values table.
    Basically I want to be able to join them and create a view that will allow me to end up effectively with a 3 column table that says

    Name - Code - Dept
    JonA - 1200 - SALES
    PaulB - 3200 - TRADING
    ChrisC - 6500 - SERVICE

    In english I would say that "If Code for JonA is greater than the begin code for department and less than End code then return that department else move to next department"

    But I have no idea how I would get to this in SQL - I've though about a case statement but I can't get it to reference 2 columns plus I'm having to reference differing rows.
    I could do all this manually of course but it needs to be dynamic as any manager can add new salespeople or move them into different depts at any time.

    I'll be giving this some thought this evening and if I have a solution I'll post it here  - Although I wouldn't even know what tags to put in - what do you call this type of query/question?

    Any ideas from all you brain boxes out there greatly appreciated.

    Wednesday, September 9, 2009 4:05 PM

Answers

  • Here's some thought to ponder. Hope this will give you some idea. Notice I commented out All row from @t1 because if you run this query, each code will falls into each department and as well as All code range.

    declare

     

    @t1 table
    (   t1Dept varchar(10),
        t1Begin int,
        t1End int
    )
    insert into @t1
        select 'SALES',1000,2999 union all
        select 'TRADING',3000,4999 union all
        select 'SERVICE',5000,7999 --union all
    -- select 'ALL',1000,7999
    --select * from @t1

    declare

     

    @t2 table
    (   t2Name varchar(10),
        Code int
    )
    insert into @t2
        select 'JonA',1200 union all
        select 'PaulB',3200 union all
        select 'ChrisC',6500
    --select * from @t2

    select
    t2.*,t1.t1Dept
    from @t2 t2
    inner join @t1 t1 on t2.Code between t1.t1Begin and t1.t1End


    Chicagoan ...
    • Marked as answer by Jonnythedrum Thursday, September 10, 2009 4:13 PM
    Wednesday, September 9, 2009 4:22 PM
    Answerer

All replies

  • Here's some thought to ponder. Hope this will give you some idea. Notice I commented out All row from @t1 because if you run this query, each code will falls into each department and as well as All code range.

    declare

     

    @t1 table
    (   t1Dept varchar(10),
        t1Begin int,
        t1End int
    )
    insert into @t1
        select 'SALES',1000,2999 union all
        select 'TRADING',3000,4999 union all
        select 'SERVICE',5000,7999 --union all
    -- select 'ALL',1000,7999
    --select * from @t1

    declare

     

    @t2 table
    (   t2Name varchar(10),
        Code int
    )
    insert into @t2
        select 'JonA',1200 union all
        select 'PaulB',3200 union all
        select 'ChrisC',6500
    --select * from @t2

    select
    t2.*,t1.t1Dept
    from @t2 t2
    inner join @t1 t1 on t2.Code between t1.t1Begin and t1.t1End


    Chicagoan ...
    • Marked as answer by Jonnythedrum Thursday, September 10, 2009 4:13 PM
    Wednesday, September 9, 2009 4:22 PM
    Answerer
  • That's brilliant, I think...

    I've ran that and it does exactly what I wanted, now I just need to figure ouot how to apply it to my data dynamically but the "between" function is the killer that I've been looking for, thanks.
    Thursday, September 10, 2009 4:13 PM