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.
| Author |
Topic |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2008-07-29 : 10:11:02
|
| I have a query that selects two types of hours:1. Labor hours (labor_hours) and2. Equipment hours (equipment_hours)It looks like below query only picks up records where both labor and equipment hours exist. The case is that there will always be labor hours and where the equipment hours could be zero or equal to labor hours or part of labor hours.So I need the query always to pick up labor hours regardless even if equipment hours are zero. This is the line that picks up equipment hours:right join Equipmentlaborevent on dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid and that could be zero.code]select dbo.Batch.ReportDate as date, dbo.Item.CompanyItemId as cost_code, substring(dbo.PayType.Name, 1, 20) as pay_type, 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_hoursfrom Job inner join Event on dbo.Event.Jobguid = dbo.Job.Jobguid inner join Item on dbo.Item.Itemguid = dbo.Event.Itemguid inner join EmployeelaborEvent on dbo.EmployeelaborEvent.EventGuid = dbo.Event.RelatedeventGuid 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 batch on dbo.Batch.Batchguid = dbo.Event.Batchguid right join Equipmentlaborevent on dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid inner join Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid WHERE dbo.Job.CompanyJobId > 3505048 and dbo.EmployeeLaborEvent.Hours > 0ORDER BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-29 : 10:22:39
|
| can you give some sample data from tables and expected o/p please? |
 |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2008-07-30 : 16:35:50
|
| I managed to solve the issue (so far). Thank you. |
|
|
|
|
|
|
|
|
[Resolved] Query - problem with select