[Resolved] SQL script - build new logic

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] SQL script - build new logic

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-30 : 20:56:32

I have a query that I need to expand. I need to change the current select for pay_type and pay_text. The current code selects the pay_type and pay_text the ‘old’ way (from dbo.PayType).

If the dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid field is Null then value ‘40’ need to be inserted into pay_type and value ‘Regular Time’ inserted into pay_text.

Otherwise (if the dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid field is not Null) need to take the dbo. EmployeeLaborEvent.EmployeeLaborAttributeGuid and link to dbo.EmployeeLaborAttribute.EmployeeLaborAttributeGuid and get the value for pay_type = dbo.EmployeeLaborAttribute.CompanyEmployeeLaborAttributeID and value for pay_text = dbo.EmployeeLaborAttribute.Name (something like this:

select distinct(dbo.employeelaborattribute.companyemployeelaborattributeid), dbo.employeelaborattribute.name 
from employeelaborevent
inner join dbo.employeelaborattribute on  dbo.employeelaborattribute.EmployeelaborattributeGuid = EmployeeLaborEvent.EmployeeLaborAttributeGuid

Result
pay_type pay_text
700 Holiday
999 No Show
996 No Work Available
47 Shift Diff.
991 Terminated
997 Too Wet

The EmployeeLaborEvent table:

EmployeeLaborEventGuid
PayTypeGuid
EventGuid
EmployeeGuid
JobCraftGuid
EmployeeGroupGuid
EmployeeLaborAttributeGuid
Hours

The EmployeeLaborAttribute table:

EmployeeLaborAttributeGuid
RegionGuid
DivisionGuid
CompanyEmployeeLaborAttributeID
Name
Active

Current sql script:


select		dbo.Batch.ReportDate as date, 
	  	dbo.Item.CompanyItemId as cost_code,
		substring(dbo.PayType.CompanyPayTypeId, 1, 10) as pay_type,
	    	substring(dbo.PayType.Name, 1, 20) as pay_text,
                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,
	    	substring(dbo.Equipment.CompanyEquipmentId, 1, 20) as equipment_id, 
	   	dbo.EquipmentLaborEvent.Hours as equipment_hours

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.PayType on dbo.PayType.PayTypeGuid = dbo.EmployeeLaborEvent.PayTypeGuid
		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 = Event.EventGuid
		left join dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EventGuid = e1.Eventguid
                left join dbo.Equipment on dbo.Equipment.EquipmentGuid = EquipmentLaborEvent.EquipmentGuid  

WHERE           dbo.Job.CompanyJobId = 3505048 
                and dbo.Item.CompanyItemId = '05001' 
                and dbo.EmployeeLaborEvent.hours > 0

ORDER BY	dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId

Query result:

2008-04-23 05001 40 Regular Time NULL Guzman, Juan Roller Oper-Finish :
2008-04-23 05001 40 Regular Time NULL Morey, Adolfo I. Asphalt Raker : Asph
2008-04-23 05001 OT PT 2 HRS NULL Rooke, Guillermo A. Roller Operator : As
2008-04-23 05001 40 Regular Time NULL Raya, Alfonso Asphalt Screed Opera

Any ideas how I can do this ?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-31 : 12:13:38

Resolved using two queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources