• james.ingamells (10/10/2013)


    hi all,

    many thanks for your assistance so far. I have created a units table as mentioned called lchs_ref.dbo.FHL_Units. I have tried to replicate what Jack Corbett has suggested but i keep coming up with an error. Here is my adapted query and the error i get. Any suggestions on what i am missing? I have never used the top part of the query before so apologies if its a simple error.

    With Units AS

    (

    Select Distinct

    unit

    FROM

    dbo.Full_Referrals_Dataset_live

    )

    SELECT

    count(FR.[Unit]) as Actual,

    U.unit,

    FR.[1st_of_month],

    FR.[last_of_month]

    FROM

    lchs_ref.dbo.FHL_Units as U

    LEFT JOIN

    dbo.Full_Referrals_Dataset_live as FR

    WHERE

    FR.RfR1 = '18 month project'

    GROUP BY

    U.unit,

    FR.[1st_of_month],

    FR.[last_of_month];

    Msg 156, Level 15, State 1, Line 18

    Incorrect syntax near the keyword 'WHERE'.

    Oops. My bad. I forgot the ON clause for the JOIN. You need to add ON U.unit = FR.unit before the WHERE.