question

In your table Student.FactApplicationSnapshot do you have any NULL values in the columns CensusDateSK, AcademicYearStartDateSK, ApplicationDateSK or CourseSK? If so you might be inadvertently knocking out rows you want to see because of your INNER JOINs. If you LEFT JOIN from Student.FactApplicationSnapshot to any of those tables in which NULLs might come into play, you might have better luck. Be careful though to alter your WHERE clause as well though, as it will have the effect of turning the LEFT JOINs back into INNER JOINs where it refers to tables being joined to. SELECT appl.EventCount ,cens.AcademicYearLookup AS CensusYear ,acyr.AcademicYearLookup AS StartYear ,apdt.AcademicYearLookup AS ApplicationYear ,cens.CurrentWeekComparisonFlag ,cens.AcademicWeekOfYear ,CASE WHEN appl.ApplicationCensusSK = 1 THEN 'Same Year' WHEN appl.ApplicationCensusSK = 2 THEN 'Next Year' WHEN appl.ApplicationCensusSK = 5 THEN 'Last Year' ELSE 'Other' END AS CensusYearDescription ,cens.CurrentAcademicYear ,appl.StudentCodeBK ,appl.ApplicationSequenceNoBK ,appl.CourseSK ,appl.CourseGroupSK ,appl.CourseMoaSK ,appl.CboSK ,appl.CourseTaughtAbroadSK ,appl.ApplicationStatusSK ,appl.ApplicationFeeStatusSK ,appl.DecisionResponseSK ,appl.NationalityCountrySK ,appl.DomicileCountrySK ,appl.TargetRegionSK ,appl.InternationalSponsorSK INTO dbo.BaseTable3yrs FROM Student.FactApplicationSnapshot AS appl LEFT JOIN Conformed.DimDate AS cens ON appl.CensusDateSK = cens.DateSK AND cens.CurrentAcademicYear In (0,-1) LEFT JOIN Conformed.DimDate AS acyr ON appl.AcademicYearStartDateSK = acyr.DateSK LEFT JOIN Conformed.DimDate AS apdt ON appl.ApplicationDateSK = apdt.DateSK LEFT JOIN Student.DimCourse cors ON appl.CourseSK = cors.CourseSK AND cors.DepartmentShortName = 'TEACH ED' WHERE appl.ApplicationCensusSK In (1,2,5) ;