SELECT	dbo.Batch.ReportDate as job_date, 
	  	dbo.Item.CompanyItemId as cost_code,
		pay_type = '40',
		pay_text = 'Regular Time',
		substring(dbo.Equipment.CompanyEquipmentId, 1, 20) as equipment_id, 
                          substring(dbo.Equipment.Name, 1, 20) as equipment_name,
	    	substring(dbo.Employee.Name, 1, 20) as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 20) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours labor_hours,
	    	dbo.EquipmentLaborEvent.Hours as equipment_hours,
                	e.Start,
		e.[Stop],
		e.Lunch,
		e.Rest
FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
inner join	Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
inner join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid
left join   	dbo.Event e1 on e1.RelatedEventGuid = dbo.Event.EventGuid
left join	             dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EventGuid = e1.Eventguid
left join	             dbo.Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid 
LEFT JOIN	(
			SELECT	e.BatchGuid,
					MAX(CASE etet.Name WHEN 'Start' THEN ete.Time ELSE NULL END) AS Start,
					MAX(CASE etet.Name WHEN 'Stop' THEN ete.Time ELSE NULL END) AS [Stop],
					MAX(CASE etet.Name WHEN 'Lunch' THEN ete.Time ELSE NULL END) AS Lunch,
					MAX(CASE etet.Name WHEN 'Break' THEN ete.Time ELSE NULL END) AS Rest
			FROM		dbo.Event AS e
			INNER JOIN	EmployeeTimeEvent AS ete ON ete.EventGuid = e.EventGuid and ete.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid			INNER JOIN	EmployeeTimeEventType AS etet ON etet.EmployeeTimeEventTypeGuid =  ete.EmployeeTimeEventTypeGuid
			GROUP BY	e.BatchGuid
		) AS e ON e.BatchGuid = dbo.Batch.BatchGuid
WHERE           dbo.Job.CompanyJobId = 3505048 
                and dbo.Item.CompanyItemId = '05001' 
                and dbo.EmployeeLaborEvent.Hours <> 0
ORDER BY	dbo.Batch.ReportDate

FROM		dbo.Event AS e
			INNER JOIN	EmployeeTimeEvent AS ete ON ete.EventGuid = e.EventGuid and ete.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid			
                        
                        INNER JOIN      EmployeeLaborEvent as emp on emp.EmployeeGuid = ete.EmployeeGuid

	                INNER JOIN	EmployeeTimeEventType AS etet ON etet.EmployeeTimeEventTypeGuid =  ete.EmployeeTimeEventTypeGuid

			GROUP BY	e.BatchGuid