Errors trying to do a Left Join

Comments

5 comments

  • Avatar
    Gowtham Senthilkumar

    Hey Mike, 

    The query that isn't working is due to the missing parenthesis. 

    Try this: 

    SELECT b.CompanyCode,b.CustomerCode,b.ContractNumber,b.RevenueDate,b.Revenue,b.ContractKey,amount.amount
    FROM [WO_SC_RevenueSchedule] b
    LEFT JOIN
    (SELECT
    t.CustomerCode
    ,createdate(getyear(t.PostingDate),getmonth(t.PostingDate),1) ) AS date
    ,sum(t.Amount) AS amount FROM [ExpenseActuals] t
    GROUP BY
    t.CustomerCode
    ,createdate(getyear(t.PostingDate),getmonth(t.PostingDate),1) ) AS amount
    ON b.CustomerCode=amount.CustomerCode AND b.RevenueDate=amount.date

    Hope that helps. 

    Thanks, 
    Gowtham Senthilkumar
    BI Consultant

    0
    Comment actions Permalink
  • Avatar
    Mike Flanagan

    Thanks for getting back to me so quickly. That changed the error to below:

    0
    Comment actions Permalink
  • Avatar
    Gowtham Senthilkumar (Edited )

    Mike, 

    The below should fix it.

    SELECT
    b.CompanyCode,
    b.CustomerCode,
    b.ContractNumber,
    b.RevenueDate,
    b.Revenue,
    b.ContractKey,
    amount.amount
    FROM
    [WO_SC_RevenueSchedule] b
    LEFT JOIN
    (
    SELECT
    t.CustomerCode,
    createdate(getyear(t.PostingDate), getmonth(t.PostingDate), 1) AS date,
    sum(t.Amount) AS amount
    FROM
    [ExpenseActuals] t
    GROUP BY
    t.CustomerCode,
    createdate(getyear(t.PostingDate), getmonth(t.PostingDate), 1)
    )
    AS amount
    ON b.CustomerCode = amount.CustomerCode
    AND b.RevenueDate = amount.date

    0
    Comment actions Permalink
  • Avatar
    Mike Flanagan

    Thanks again for the quick response... One last error

     

    0
    Comment actions Permalink
  • Avatar
    Nathan Giusti

    Hey Mike, 

     

    That means one of your fields that you think is a date is actually a string. Look at RevenueDate or ammount.date and verify they are both dates. 

    0
    Comment actions Permalink

Please sign in to leave a comment.