Post reply

JOIN Question

  • November 25, 2010 at 7:00 am

    #227198

    Hi,

    Need some help with a join.

    In the code below there are two tables.

    table tempAccount holds data for an account over a period of time. The second table holds the AccountOwner and the Date which s/he became the owner.

    I would like to join the two tables to get all the account details and the person who was the owner at the time.

    Any help is appreciated

    G

    CREATE TABLE #tempAccount

    (

    YearMonth int

    , AccountID int

    , AccountName varchar(20)

    , AccountBalance Decimal (18,2)

    )

    INSERT INTO #tempAccount

    SELECT 200801, 1, 'Blue', 500

    UNION ALL

    SELECT 200802, 1, 'Blue', 800

    UNION ALL

    SELECT 200803, 1, 'Blue', 1000

    UNION ALL

    SELECT 200804, 1, 'Blue', 1000

    UNION ALL

    SELECT 200805, 1, 'Blue', 1000

    CREATE TABLE #tempAccountOwner

    (

    YearMonthStart INT

    , AccountID INT

    , OwnerID INT

    , OwnerName VARCHAR(50)

    )

    INSERT INTO #tempAccountOwner

    SELECT 200801, 1, 1, 'Bob'

    UNION ALL

    SELECT 200803, 1, 1, 'Fred'

    SELECT

    a.YearMonth

    , a.AccountID

    , a.AccountName

    , a.AccountBalance

    , b.OwnerName

    FROM #tempAccount a

    LEFT JOIN #tempAccountOwner b

    ON a.AccountID = b.AccountID

    --Cleanup:

    DROP TABLE #tempAccount

    DROP TABLE #tempAccountOwner

  • November 25, 2010 at 7:14 am

    #1255566

    Well if you really want the join you can change your join to

    LEFT JOIN #tempAccountOwner b

    ON a.AccountID = b.AccountID

    AND b.YearMonthStart = (select top 1 b2.YearMonthStart

    from #tempAccountOwner b2

    where b2.AccountId = b.AccountId

    and b2.YearMonthStart <= a.YearMonth

    order by b2.YearMonthStart desc)

    or if you can live without the join. Replace the entire join part with this

    cross apply (select top 1 OwnerName

    from #tempAccountOwner b2

    where b2.AccountId = a.AccountId

    and b2.YearMonthStart <= a.YearMonth

    order by b2.YearMonthStart desc) b

    /T

  • November 25, 2010 at 7:59 am

    #1255580

    Thanks for this.

    Is there a benefit to using the Cross Apply , I've never used it before ...

  • November 25, 2010 at 8:44 pm

    #1255678

    @Grinja,

    I just want to make sure I'm seeing the data correctly. It looks like you have account #1 called "BLUE" and it is owned by [font="Arial Black"]both [/font]"Fred" and "Bob". Is that correct?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 26, 2010 at 2:32 am

    #1255747

    Hi Jef, that is correct.

    The account belongs to Bob for period 200801 - 200802 and to Fred from 200803 onwards.

    G

  • November 26, 2010 at 9:26 am

    #1255842

    Grinja (11/26/2010)

    Hi Jef, that is correct.

    The account belongs to Bob for period 200801 - 200802 and to Fred from 200803 onwards.

    G

    In that case, it looks like TommyH has the code that does the trick. Either way works.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply