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] Select by total

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-05-29 : 13:18:30
I have a query that renders detailed equipment hours by equipment id. Query works fine.

My question is: Need to be able to select only records where the total hours for each equipment ID is less that a given number. Not sure if that is possible using the existing query.

SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                     
  	                        
FROM       dbo.Equipment
                 inner join dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                

WHERE  (@Division IS Null OR dbo.Region.CompanyRegionID = @Division) and 
                (@JobNumber IS Null or dbo.Job.CompanyJobID = @JobNumber) and
                (@DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom) and
                (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo) and
	        dbo.EquipmentLaborEvent.Hours > 0


I tried to add this:


GROUP BY    dbo.Job.CompanyJobId, dbo.Equipment.CompanyEquipmentID, dbo.Equipment.Name

HAVING        (@Hours IS Null or  sum(dbo.EquipmentlaborEvent.Hours) < @Hours)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-29 : 13:28:18
Just making a guess based on your query:
SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                     
  	                        
FROM       dbo.Equipment
                 inner join 
				(
					 SELECT EquipmentGuid
					 FROM dbo.EquipmentLaborEvent 
					 GROUP BY EquipmentGuid
					 HAVING SUM(Hours) > @Hours
				) AS EquipmentLaborEvent
                 on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                

WHERE  (@Division IS Null OR dbo.Region.CompanyRegionID = @Division) and 
                (@JobNumber IS Null or dbo.Job.CompanyJobID = @JobNumber) and
                (@DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom) and
                (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo)
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-05-29 : 14:05:08
Getting error:

SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                     
  	                        
FROM       dbo.Equipment
                 inner join 
				(
					 SELECT EquipmentGuid
					 FROM dbo.EquipmentLaborEvent 
					 GROUP BY EquipmentGuid
					 HAVING SUM(Hours) < 6
				) AS EquipmentLaborEvent
                 on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                

WHERE            dbo.Region.CompanyRegionID = '00060'


The column prefix 'dbo.EquipmentLaborEvent' does not match with a table name or alias name used in the query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-29 : 14:45:05
[code]
SELECT
substring(dbo.Job.CompanyJobId, 1, 10) as job,
substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
dbo.Batch.ReportDate as jobdate,
dbo.Equipment.CompanyEquipmentID as equipmentID,
substring(dbo.Equipment.Name, 1, 20) as equipmentname,
m.hours,
substring(dbo.EventStatusType.Name, 1, 1) as event_code,
dbo.EventStatusType.Name as event_name,
substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
substring (e2.UserName, 1, 10) as approver


FROM dbo.Equipment
inner join dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
INNER JOIN
(SELECT dbo.Equipment.CompanyEquipmentID,SUM(dbo.EquipmentLaborEvent.Hours) AS hours
FROM dbo.Equipment
inner join dbo.EquipmentLaborEvent
on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
GROUP BY dbo.Equipment.CompanyEquipmentID
HAVING SUM(dbo.EquipmentLaborEvent.Hours)>0)m
ON m.CompanyEquipmentID= dbo.Equipment.CompanyEquipmentID
inner join dbo.Region on dbo.Region.RegionGuid = dbo.Equipment.RegionGuid
inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid


WHERE (@Division IS Null OR dbo.Region.CompanyRegionID = @Division) and
(@JobNumber IS Null or dbo.Job.CompanyJobID = @JobNumber) and
(@DateFrom IS Null OR dbo.Batch.Reportdate >= @DateFrom) and
(@DateTo IS Null OR dbo.Batch.ReportDate <= @DateTo)
[/code]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-29 : 15:24:40
quote:
Originally posted by snufse

Getting error:

SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                     
  	                        
FROM       dbo.Equipment
                 inner join 
				(
					 SELECT EquipmentGuid
					 FROM dbo.EquipmentLaborEvent 
					 GROUP BY EquipmentGuid
					 HAVING SUM(Hours) < 6
				) AS EquipmentLaborEvent
                 on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                

WHERE            dbo.Region.CompanyRegionID = '00060'


The column prefix 'dbo.EquipmentLaborEvent' does not match with a table name or alias name used in the query.
Remove the "dbo." from the derived table alias as shown above. (not sure I got them all)
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-06-01 : 08:53:12
Great. Worked. Thank you.
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