inner JOIN	dbo.ProductionEvent ON dbo.ProductionEvent.EventGuid = dbo.Event.EventGuid

dbo.Event ON dbo.Event.JobGuid = dbo.Job.JobGuid

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
 
 

Close Window