You could also create a re-usable function

CREATE FUNCTION dbo.GetJulianDate(
@SomeDate datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT JDate = YEAR(@SomeDate)*1000 + DATEPART(dayofyear,@SomeDate);

This will give you the current date in the format YYYYDDD
SELECT YEAR(GETDATE())*1000 + DATEPART(dayofyear,GETDATE());

Also note that your left join is effectively converted into an inner join by your where clause

What about putting the staging tables in a separate DB on the same instance.
Then ensure that your ETL pushes from the staging DB to the final DB.
That way,...

Without sample data or an expected outcome, I believe that the following SQL statement should do the trick.

DECLARE @campus_cd nvarchar(300)
  , @cmpy_code nvarchar(500)
  , @year1  ...