SELECT	dbo.Job.CompanyJobID,
		dbo.Job.ChangeDate as date,
		dbo.Job.Name,
		dbo.Region.CompanyRegionID,
		dbo.Job.Active,
		MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'MA' THEN 'Yes' ELSE 'No' END) AS Material,
		MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'PR' THEN 'Yes' ELSE'No' END) AS Production,
                MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'SB' THEN 'Yes' ELSE'No' END) AS Sub,
		MAX(CASE dbo.EquipmentLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Equipment,
		MAX(CASE dbo.EmployeeLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Labor,
		MAX(CASE dbo.Diary.DiaryEntry WHEN 0 THEN 'No' ELSE 'Yes' END) AS Diary
FROM		dbo.Job
			INNER JOIN	dbo.Event ON dbo.Event.JobGuid = dbo.Job.JobGuid
                                        INNER JOIN	dbo.ProductionEvent ON dbo.ProductionEvent.EventGuid = dbo.Event.EventGuid
			LEFT JOIN	dbo.Product ON dbo.Product.ProductGuid = dbo.ProductionEvent.ProductGuid
			LEFT JOIN	dbo.Item ON dbo.Item.ItemGuid = dbo.Event.ItemGuid
			INNER JOIN	dbo.Source ON dbo.Source.SourceGuid = dbo.ProductionEvent.SourceGuid
			INNER JOIN	dbo.SourceType ON dbo.SourceType.SourceTypeGuid = dbo.Source.SourceTypeGuid
			LEFT JOIN	dbo.EquipmentLaborEvent ON dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid
			LEFT JOIN	dbo.EmployeeLaborEvent ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid
                                        LEFT JOIN         dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.EventGuid
			LEFT JOIN         dbo.Diary ON dbo.Diary.BatchGuid = dbo.Batch.BatchGuid
			INNER JOIN	#AuthorityTable AS at ON at.busunit_from <= dbo.Job.CompanyJobId
		    			AND at.busunit_to >= dbo.Job.CompanyJobId
			LEFT JOIN	dbo.Region ON dbo.Region.RegionGuid = dbo.Job.RegionGuid
WHERE	dbo.SourceType.CompanySourceTypeID IN ('PR', 'MA', 'SB')
			AND (dbo.Job.CompanyJobID LIKE CAST(@JobNumber AS VARCHAR(10)) + '%' OR @JobNumber = 0 OR @JobNumber IS NULL)
			AND (dbo.Job.Name LIKE '%' + @Text + '%' OR @Text IS NULL)
			AND (dbo.Job.ChangeDate >= @DateFrom OR @DateFrom IS NULL)
			AND (dbo.Job.ChangeDate <= @DateTo OR @DateTo IS NULL)
GROUP BY	dbo.Job.CompanyJobID,
			dbo.Job.ChangeDate,
			dbo.Region.CompanyRegionID,
			dbo.Job.Name,
			dbo.Job.Active
ORDER BY       dbo.Job.CompanyJobID