[Resolved] Two queries - different results

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] Two queries - different results

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-05-11 : 16:52:58

I have 2 queries that should render the same result (number of hours) but they do not.

Query 1:

SELECT           dbo.Batch.reportDate,
                 sum(dbo.EquipmentLaborEvent.Hours) as hours
   	                        
FROM             dbo.EquipmentLaborEvent
                 inner join dbo.Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 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
                 inner join dbo.Region on dbo.Region.RegionGuid = Batch.RegionGuid 

WHERE           dbo.Region.CompanyRegionID = 00035 and 
                dbo.Equipment.CompanyEquipmentID = '03.60240'
                               
GROUP BY        dbo.EquipmentLaborEvent.Hours, dbo.Batch.ReportDate 

ORDER BY        dbo.Batch.ReportDate

Result:

2009-05-04 00:00:00.000 8.00
2009-05-05 00:00:00.000 16.00
2009-05-06 00:00:00.000 5.00
2009-05-07 00:00:00.000 8.00
2009-05-08 00:00:00.000 5.00

This is correct result.

Now when I do not summarize the hours, I get different result (look ate date 2009-5-5)

Query 2:

SELECT           dbo.Batch.reportDate,
                 dbo.EquipmentLaborEvent.Hours as hours
                    	                        
FROM             dbo.EquipmentLaborEvent
                 inner join dbo.Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 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
                 inner join dbo.Region on dbo.Region.RegionGuid = Batch.RegionGuid 

WHERE           dbo.Region.CompanyRegionID = 00035 and 
                dbo.Equipment.CompanyEquipmentID = '03.60240'
                               
GROUP BY        dbo.EquipmentLaborEvent.Hours, dbo.Batch.ReportDate 

ORDER BY        dbo.Batch.ReportDate

Result:

2009-05-04 00:00:00.000 8.00
2009-05-05 00:00:00.000 8.00
2009-05-06 00:00:00.000 5.00
2009-05-07 00:00:00.000 8.00
2009-05-08 00:00:00.000 5.00

Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-11 : 17:01:32

You are doing a group by on Hours. So, I'd guess that you have two rows each with 8 hours. Thus, the SUM show 16 (8+8). For example:

DECLARE @Foo TABLE(EventDate DATETIME, Hours TINYINT)

INSERT @Foo
SELECT GETDATE(), 8
UNION ALL SELECT GETDATE(), 8
UNION ALL SELECT GETDATE(), 9

SELECT *, COUNT(*)
FROM @Foo
GROUP BY Hours, EventDate


SELECT SUM(Hours), EventDate, COUNT(*)
FROM @Foo
GROUP BY Hours, EventDate

SELECT SUM(Hours), EventDate, COUNT(*)
FROM @Foo
GROUP BY EventDate

EDIT: Added COUNT(*) to help demonstate.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-05-12 : 09:25:06

You put me on the right track. Using temp table it works. Thank you.

CREATE PROCEDURE dbo.sp_Vecellio_EquipmentHoursDetail

(
         @Division CHAR(20) = Null,	
         @JobNumber INT = 0,
         @EquipmentID CHAR(20),
	 @DateFrom DATETIME = NULL,
	 @DateTo DATETIME = NULL
)

AS

SET NOCOUNT ON

If Object_ID('tempdb..#TempTable') Is Not Null    Drop Table #TempTable

CREATE TABLE #TempTable (job nvarchar(10), costcode nvarchar(10), jobdate DateTime,
                                                  equipmentID nvarchar(20), equipmentname nvarchar(20), 
                                                  hours int, foreman nvarchar(10), approver nvarchar(10))

INSERT INTO           #TempTable
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.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
  	                        
FROM             dbo.EquipmentLaborEvent
                 inner join dbo.Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid
                 inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                 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
                 inner join dbo.Region on dbo.Region.RegionGuid = Batch.RegionGuid 
                 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
                (@EquipmentID IS Null or dbo.Equipment.CompanyEquipmentID = @EquipmentID) and
                (@DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom) and
                (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo) 
              
SELECT  * FROM #TempTable order by jobdate, job, costcode, equipmentid

DROP TABLE #TempTable

GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-12 : 13:26:00

Hu? How does a temp table change anything (other than adding overhead)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources