[Resolved] Condition on "Having Sum.."

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] Condition on "Having Sum.."

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-06-05 : 10:30:48

Have following query below. I only need to execute the line "HAVING SUM(dbo.EquipmentLaborEvent.Hours) < @Hours)" if @Hours being passed in is not zero. How would I do that? Thank you.

@Hours INT 
.
..
...
SELECT dbo.Equipment.CompanyEquipmentID,SUM(dbo.EquipmentLaborEvent.Hours) AS hours
                  FROM dbo.Equipment
                  inner join dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                  inner join dbo.Event on dbo.Event.EventGuid = dbo.EquipmentLaborEvent.EventGuid
                  inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                  WHERE (@DateFrom IS Null or dbo.Batch.Reportdate >= @DateFrom) and
                                 (@DateTo IS Null or dbo.Batch.ReportDate <= @DateTo)
                  GROUP BY dbo.Equipment.CompanyEquipmentID
                  HAVING SUM(dbo.EquipmentLaborEvent.Hours) < @Hours)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-05 : 10:56:40

Try this

HAVING SUM(dbo.EquipmentLaborEvent.Hours) < ISNULL(@Hours,NumberBiggerThan@hoursWillEverBe)

Jim

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-06-05 : 11:32:09

Not sure what you meant but got it working this way:

HAVING (@Hours IS Null or SUM(dbo.EquipmentLaborEvent.Hours) < @Hours))m

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources