quote:

---

Originally posted by liptonIcedTea

This does not fix the error.

It is still coming up with "Invalid column name 'Category'"

---

select * from
(
your select here without reference to Category
)
where 
Category is not null.

declare	@CRN varchar(100)
set	@CRN = '420631391K'

select * from
(
select		tp.TrainingProviderId as TrainingProvider, 
		tpn.Name as [Provider Name],
		tps.name as [Site Name], 
		tps.TrainingProviderSiteIdentifier as SiteName, 
		ji.CRN as CRN,
		ji.JobseekerIdentifier as JskNumber, 
		tbh.TrainingBlockHistoryTypeCd,
		case 
			when tbh.TrainingBlockHistoryTypeCd = 'Ast' then 'Actual Start Date'
			when tbh.TrainingBlockHistoryTypeCd = 'Sus' then 'Suspension'
			when tbh.TrainingBlockHistoryTypeCd = 'W' then 'Withdrawal'	
			when tbh.TrainingBlockHistoryTypeCd = 'T' then 'Termination'
			when tbh.TrainingBlockHistoryTypeCd = 'Trd' then 'Resumption'	
			when tbh.TrainingBlockHistoryTypeCd = 'Aed' then 'Actual End Date'
		End as Category,
		ar.ReferringProviderReferredDt as ReferralDate,
		tbh.EventDt as EventDate,
		tbh.CreatedDt as DataEntryDate, 
		datediff(day, tbh.EventDt, tbh.CreatedDt) as DaysOver,
		ind.FamilyName + ',' + ind.FirstName + ' (' + ind.AccountLoginCode + ')' as DataEnteredBy
from		tblTrainingBlockHistory as tbh 
inner join	tblTrainingBlock as tb on tb.TrainingBlockId = tbh.TrainingBlockId
inner join	tblActionedReferral as ar on ar.ActionedReferralId = tb.ActionedReferralId
inner join	tblJobseekerIdentifier as ji on ji.JobseekerIdentifierId = ar.JobseekerIdentifierId
inner join	tblTrainingProvider as tp on tp.TrainingProviderId = ar.TrainingProviderId
inner join	tblTrainingProviderSite as tps on tps.TrainingProviderId = tp.TrainingProviderId
inner join	tblTrainingProviderName as tpn on tpn.TrainingProviderId = tps.TrainingProviderId
inner join	tblIndividual as ind on ind.IndividualId = tb.CreatedIndividualId
where		tpn.ProviderNameTypeCd = 'P'
		and ji.CRN = @CRN
		and datediff(day, tbh.EventDt, tbh.CreatedDt) > 7
		and tbh.IsActiveInd = 'Y'
		and ar.TrainingProviderSiteId = tps.TrainingProviderSiteId
) x
where category is not null;