Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 [Resolved] Query Problems (based on prior post)

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-08-14 : 09:14:06
I have previously posted this issue and got some good help but I apparently left out a piece that was important to getting the correct result from my query.

This is the old query I had prior to getting assistance:

select		dbo.employeetimeeventtype.name, dbo.employeetimeevent.time, dbo.employeetimeevent.employeeguid,
		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,
                           CASE dbo.EmployeeTimeEventType.Name WHEN 'Start' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END AS Start,
                           CASE dbo.EmployeeTimeEventType.Name WHEN 'Stop' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END AS Stop,
                           CASE dbo.EmployeeTimeEventType.Name WHEN 'Lunch' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END AS Lunch,
                           CASE dbo.EmployeeTimeEventType.Name WHEN 'Break' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END AS 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 dbo.Event e2 on e2.BatchGuid = dbo.Batch.BatchGuid
                 left join EmployeeTimeEvent on dbo.EmployeeTimeEvent.EventGuid = e2.EventGuid AND EmployeeTimeEvent.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
	         inner join EmployeeTimeEventType ON EmployeeTimeEventType.EmployeeTimeEventTypeGuid = dbo.EmployeeTimeEvent.EmployeeTimeEventTypeGuid
        

WHERE           dbo.Job.CompanyJobId = 3505048 
                and dbo.Item.CompanyItemId = '05001' 
                and dbo.EmployeeLaborEvent.Hours <> 0
		and dbo.Employee.Name = 'Vinson, Ronnie'
		and dbo.Batch.ReportDate = '7-28-2008'
ORDER by        dbo.Batch.ReportDate


Result is 2 records:

Name / Job Craft / Lab Hours / Equipm Hours / Start Date_Time / Stop Date_Time
Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / NULL / 2008-07-29 06:30:00
Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / 2008-07-28 20:30:00 / NULL

This query works OK except for that I am not getting the result as one line. Please note that the Start/Stop times: 20:30: 00 and 6:30:00

Now, the new code puts everything on one line and that is what I need:

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 = emp.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
		) AS e ON e.BatchGuid = dbo.Batch.BatchGuid
WHERE             dbo.Job.CompanyJobId = 3505048 
                  and dbo.Item.CompanyItemId = '05001' 
                  and dbo.EmployeeLaborEvent.Hours <> 0
                  and dbo.Employee.Name = 'Vinson, Ronnie'
	          and dbo.Batch.ReportDate = '7-28-2008'
ORDER BY          dbo.Batch.ReportDate


Result:

Name / Job Craft / Lab Hours / Equipm Hours / Start Date_Time / Stop Date_Time
Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / 2008-07-28 21:30:00 / 2008-07-29 07:30:00

Please note that the Start/Stop time are not the same, they are 21:30:00 and 7:30:00 (and should be like the old query 20:30:00 and 6:30:00)
The reason for this is that it is not checking the employee id

The old query has: AND EmployeeTimeEvent.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid which makes sure I match the employee and where the new query in not checking for this. I have tried to put this line into the new query without good results.

Can anyone help getting this line into the new query? Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-14 : 09:48:11
[code]select dbo.employeetimeeventtype.name,
dbo.employeetimeevent.time,
dbo.employeetimeevent.employeeguid,
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,
MAX(CASE dbo.EmployeeTimeEventType.Name WHEN 'Start' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END) AS Start,
MAX(CASE dbo.EmployeeTimeEventType.Name WHEN 'Stop' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END) AS Stop,
MAX(CASE dbo.EmployeeTimeEventType.Name WHEN 'Lunch' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END) AS Lunch,
MAX(CASE dbo.EmployeeTimeEventType.Name WHEN 'Break' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END) AS 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 dbo.Event e2 on e2.BatchGuid = dbo.Batch.BatchGuid
left join EmployeeTimeEvent on dbo.EmployeeTimeEvent.EventGuid = e2.EventGuid AND EmployeeTimeEvent.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
inner join EmployeeTimeEventType ON EmployeeTimeEventType.EmployeeTimeEventTypeGuid = dbo.EmployeeTimeEvent.EmployeeTimeEventTypeGuid
WHERE dbo.Job.CompanyJobId = 3505048
and dbo.Item.CompanyItemId = '05001'
and dbo.EmployeeLaborEvent.Hours <> 0
and dbo.Employee.Name = 'Vinson, Ronnie'
and dbo.Batch.ReportDate = '7-28-2008'
GROUP BY dbo.employeetimeeventtype.name,
dbo.employeetimeevent.time,
dbo.employeetimeevent.employeeguid,
dbo.Batch.ReportDate,
dbo.Item.CompanyItemId,
substring(dbo.Equipment.CompanyEquipmentId, 1, 20),
substring(dbo.Equipment.Name, 1, 20),
substring(dbo.Employee.Name, 1, 20),
substring(dbo.JobCraft.Name, 1, 20),
dbo.EmployeeLaborEvent.Hours labor_hours,
dbo.EquipmentLaborEvent.Hours as equipment_hours
ORDER by dbo.Batch.ReportDate[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-08-14 : 10:32:58
peso,

I know I am becoming a real pain .....

I changed line 45/46 from:

dbo.EmployeeLaborEvent.Hours labor_hours,
dbo.EquipmentLaborEvent.Hours as equipment_hours

gave me syntax error

to:

dbo.EmployeeLaborEvent.Hours,
dbo.EquipmentLaborEvent.Hours

Ran the query and still gets 2 line output:

Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / 2008-07-28 20:30:00 / NULL
Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / NULL / 2008-07-29 06:30:00

Data are correct except for the 2 line output
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-14 : 10:40:41
It happens because of the GROUP BY, mnost proabably this line

dbo.employeetimeevent.time,

I think you need to rethink your query design and break it down into smaller derived tables.

or try this
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,
					ete.EmployeeGuid,
					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 = emp.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,
					ete.EmployeeGuid
		) AS e ON e.BatchGuid = dbo.Batch.BatchGuid
			AND e.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
WHERE             dbo.Job.CompanyJobId = 3505048 
                  and dbo.Item.CompanyItemId = '05001' 
                  and dbo.EmployeeLaborEvent.Hours <> 0
                  and dbo.Employee.Name = 'Vinson, Ronnie'
	          and dbo.Batch.ReportDate = '7-28-2008'
ORDER BY          dbo.Batch.ReportDate


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-08-14 : 10:45:58
Peso, look at the output:

Vinson, Ronnie / Roller Oper-Finish : / 5.00 / 4.00 / 2008-07-28 20:30:00 / 2008-07-29 06:30:00

Looks like you did it again !!!! Exactly what I need .... impressive...

I will check it out further and update the post. Thank you.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-14 : 11:12:06
How do you expect me to look at the output?
I don't have sample data, nor do I have the table layouts for all your tables.

Thank you for your feedback!


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-08-14 : 11:40:16
Peso,
What I meant was that the output was correct compared with the 2 line output (now combined into a single line). I do not expect you to know what is right or wrong since you do not have data or table structure.
I have tested the query through my program and output looks very good and response time is good also.
Thanks again, your skill set is impressive and feed back is execellent.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site