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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question