I know I've done this in the past, which makes being unable to figure it out even more frustrating.
I've got a stored procedure that's meant to return the average of a value between two given dates.
CREATE PROCEDURE [dbo].[stat_MyAverageValue]
SELECT AVG(MyValue) AS AvgValue
WHERE MyDate BETWEEN @Date1 AND @Date2
The problem is that I can't guarantee that there will be any values between the given dates, meaning this could potentially return nothing at all. How do I force it to return zero instead in that case?
That only works if a NULL is returned. In this case the query would return no rows, meaning not even a NULL. Is there perhaps some way to force a NULL value if no rows are returned so that Coalesce() will work?
EDIT: Oops...in my original query there was an unnecessary GROUP BY clause which is why there was no NULL returned. Coalesce() works without that now. Thanks
Last edited by VentureFree; 07-27-10 at 10:06.
Reason: Erroneous reply. Correcting mistake.