Condition the "HAVING SUM.."

[snufse1]

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)

 

[gmmastros]

Try this:

HAVING (@Hours < 0 or SUM(dbo.EquipmentLaborEvent.Hours) < @Hours))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snufse1]

Did not work....

If hours selected by user is nothing, then query does not return any rows when it should. Should I test on Null instead of Zero?


(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)m
HAVING (@Hours < 0 or SUM(dbo.EquipmentLaborEvent.Hours) < @Hours))m

 

[snufse1]

I tried this and seems to work:

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

 

[markros]

You can try

having (ISNULL(@Hours,0) <= 0 or ...)