[Resolved] Need to expand my query

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] Need to expand my query

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-06-23 : 09:08:13

I have a query that is working fine but needs to be expanded.


SELECT   	dbo.Batch.ReportDate as job_date, 
	  	dbo.Item.CompanyItemId as cost_code,
		case 
                    dbo.PayType.CompanyPayTypeId when '40' then substring(dbo.PayType.CompanyPayTypeId, 1, 5)   
                    dbo.PayType.CompanyPayTypeId when 'OT' then substring(dbo.EmployeeGroup.CompanyEmployeeGroupID, 1, 5)  
                    dbo.PayType.CompanyPayTypeID when 'Dbl' then substring(dbo.EmployeeLaborAttribute.CompanyEmployeeAttributeGuid, 1, 5)  
              end
                   as pay_type,         
                case 
                           dbo.PayType.CompanyPayTypeId when '40' then substring(dbo.PayType.Name, 1, 50)   
                           dbo.PayType.CompanyPayTypeId when 'OT' then substring(dbo.EmployeeGroup.Name, 1, 50)  
                           dbo.PayType.CompanyPayTypeID when 'Dbl' then substring(dbo.EmployeeLaborAttribute.Name, 1, 50)  
              end
                   as pay_text,          
                coalesce(substring(dbo.Employee.Name, 1, 12), '**Unknown**') as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 12) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours as labor_hours,
	    	e.Start,
		e.[Stop],
		e.Lunch,
		e.Rest,
                substring(dbo.EventStatusType.Name, 1, 1) as event_code,  	
                dbo.EventStatusType.Name as event_code  	

FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
inner join      dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join	dbo.Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join    dbo.PayType on dbo.PayType.PayTypeGuid = dbo.EmployeeLaborEvent.PaytypeGuid
inner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
left join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid

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 = @JobNumber 
                            and dbo.Item.CompanyItemId = @ItemNumber 
                            and ( @DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom)
                            and (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo)
                            and dbo.EmployeeLaborEvent.Hours <> 0

I will try to explain what I need (as I think it gets somewhat complicated)

The two fields in question are pay_type and pay_text.

In the clause: "inner join dbo.PayType on dbo.PayType.PayTypeGuid = dbo.EmployeeLaborEvent.PaytypeGuid" the result is 3 possible values: "40", "OT" or "DBL".

I the result is "40" then I need to set pay_type = PayType.CompanyPayTypeID and pay_text = PayType.Name.

If the result is "OT" then I need to check if EmployeeLaborEvent.EmployeeGroupGuid is NOT NULL and then do PayType.EmployeeGroupGuid joined to EmployeeGroup.EmployeeGroupGuid and get fields EmployeeGroup.CompanyEmployeeGroupID which then becomes the "pay_type" and field EmployeeGroup.Name now becomes the "pay_text".

If the result is "DBL" then I need to check if EmployeeLaborEvent.EmployeeLaborAttributeGuid is NOT NULL and then do
PayType.EmployeeGroupGuid joined to
EmployeeLaborAttribute.EmployeeLaborAttributeGuid and get fields
EmployeeLaborAttribute.CompanyEmployeeAttributeGuid which then becomes the "pay_type" and field EmployeeLaborAttribute.Name now becomes the "pay_text"

I understand if this is not clear, then please let me know. Thank you to anyone having the "urge" to look at this....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 12:50:09

So your problem is a syntax error?

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-06-23 : 13:00:04

I have a syntax issue in my "case" at the top and also I do not know to handle my code after the "inner join dbo.PayType on dbo.PayType.PayTypeGuid = dbo.EmployeeLaborEvent.PaytypeGuid" statement depending upon the result (whether I get 40, OT or Dbl), then I need additional code....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:42:44

About syntax error:


		case dbo.PayType.CompanyPayTypeId 
                  when '40' then substring(dbo.PayType.CompanyPayTypeId, 1, 5)   
                  when 'OT' then substring(dbo.EmployeeGroup.CompanyEmployeeGroupID, 1, 5)  
                  when 'Dbl' then substring(dbo.EmployeeLaborAttribute.CompanyEmployeeAttributeGuid, 1, 5)  
              end
                   as pay_type,         
                case dbo.PayType.CompanyPayTypeId 
                  when '40' then substring(dbo.PayType.Name, 1, 50)   
                  when 'OT' then substring(dbo.EmployeeGroup.Name, 1, 50)  
                  when 'Dbl' then substring(dbo.EmployeeLaborAttribute.Name, 1, 50)  
              end
                   as pay_text,

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-06-23 : 14:26:23

Webfred, Vielen Dank, that corrected the syntax, got the rest to work

FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
inner join      dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join	dbo.Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join    dbo.PayType on dbo.PayType.PayTypeGuid = dbo.EmployeeLaborEvent.PaytypeGuid
left join     dbo.EmployeeGroup on   dbo.EmployeeGroup.EmployeeGroupGuid = dbo.EmployeeLaborEvent.EmployeeGroupGuid
left join     dbo.EmployeeLaborAttribute on EmployeeLaborAttribute.EmployeeLaborAttributeGuid = EmployeeLaborEvent.EmployeeLaborAttributeGuidinner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
left join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 01:21:20

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources