[Resolved] Multiple records into a single select

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] Multiple records into a single select

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 08:31:43

I have a query that I need to extend (extended lines are marked in bold). The query works but is not giving me the format I want.
Scope: I need to include TimeEvent (like start, stop etc) which are hosted in the EmployeeTimeEvent table (one record for each event) and I need these to appear on a single line when I run the query (now it creates one line for each event found). I assume I need to have a sub select looping through all the events but not sure how to do 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,
                dbo.EmployeeTimeEvent.Time as event_time,

                
                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 
                
               -- this is the section that gets the time events and produces one query record for each event found, I would like to produce a single out line                

                left join dbo.Event e2 on e2.BatchGuid = dbo.Batch.BatchGuid
                left join EmployeeTimeEvent on dbo.EmployeeTimeEvent.EventGuid = e2.EventGuid
                inner join EmployeeTimeEventType ON EmployeeTimeEventType.EmployeeTimeEventTypeGuid =           dbo.EmployeeTimeEvent.EmployeeTimeEventTypeGuid
 
              
WHERE           dbo.Job.CompanyJobId = 3505048 
                and dbo.Item.CompanyItemId = '05001' 
                and dbo.EmployeeLaborEvent.Hours <> 0

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 08:40:23

put a max around each of your case statements, i.e.


max(CASE dbo.EmployeeTimeEventType.Name WHEN 'Start' THEN dbo.EmployeeTimeEvent.Time ELSE NULL END) AS Start

then group by each of your other fields

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 08:42:16

[code]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,
dbo.EmployeeTimeEvent.Time as event_time,
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
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[/code]

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 09:07:36

Great. Get an error and I cannot figure out where:

Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near '('.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:13:59

Put a comma after e.BatchGuid in the derived table e.

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 09:48:41

I put comma here

) AS e ON e.BatchGuid = dbo.Batch.BatchGuid,

also tried this:

SELECT e.BatchGuid,

Sorry I'm so "incompetent"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:50:01

this is what peso meant

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,
                dbo.EmployeeTimeEvent.Time as event_time,
		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
			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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 09:56:22

Then I get error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.EmployeeTimeEvent' does not match with a table name or alias name used in the query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:58:38

[code]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.event_time,
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(ete.Time) AS event_time
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
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[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:58:40

quote:
Originally posted by snufse

Then I get error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.EmployeeTimeEvent' does not match with a table name or alias name used in the query.

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,
                dbo.EmployeeTimeEvente.Time as event_time,
		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
			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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:59:24

beat by 2 seconds

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:59:36

Visakh, where is e.Time exposed?

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 10:00:59

Disregard my latest post. Think I got it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 10:06:52

quote:
Originally posted by Peso

Visakh, where is e.Time exposed?

E 12°55'05.25"
N 56°04'39.16"

ah..sorry just viewed only the wrong alias. didnt check for field

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-08-13 : 10:24:14

Peso and Visakh16:

Great job (like always). I very much appreciate your help and expertise. Works great. I would have been lost !!!!!

Again thank you (tack sa mycket)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources