ISNULL not working in TSQL Select

Question

I have a TSQL SELECT that can return a null. I tried using ISNULL to replace it with 0 but for some reason it is not working. The table selecting from has the following columns:

• storeID --> int
• penaltyPercent --> decimal(9,2)

• penaltyDate --> dateTime

  SELECT ISNULL(penaltyPercent, 0.0) AS penaltyPercent
  FROM dbo.Penalty
  WHERE (penaltyDate = (SELECT MAX(penaltyDate) AS date
  FROM dbo.Penalty AS Penalty_1
  WHERE (penaltyDate <= @date) AND (storeID = @storeID))) AND (storeID = @storeID)

When the date is before the first penalty date (when there should be 0 penalty), no result is returned. Not sure why this doesn't work. I have a work around but it is bugging me.

Thanks

As asked, here is a sample of the data being used:

storeID penaltyDate penaltyPercent
182 10/1/2008 12:00:00 AM 0.020000
182 11/1/2008 12:00:00 AM 0.040000
182 12/1/2008 12:00:00 AM 0.070000

Answer 1

When you say "When the date is before the first penalty date", do you mean when the value of @date is less than the value returned from this query?

SELECT MIN(penaltyDate)
FROM Penalty

Because then your inner query is going to return null, and (if you're using ANSI nulls) this part will return false,

WHERE (penaltyDate = ...

Because comparisons with null always return null. So instead of selecting one row with a null value, you're selecting no rows.

Addendum:

To confirm this is the problem, change your outer query's first line to

SELECT ISNULL(MAX(penaltyPercent),0.0) AS penaltyPercent

It will work because of what I've described above.

Answer 2

If @date < penaltyDate, then there is no row returned, so there is no value for ISNULL to act upon. You might want to select the penalty percent into a variable, and then select out to the result set ISNULL(@pentaltyPercent).

Answer 3

Do you acctually have data that it's date is before the first penaly date? If your query doesn't return records, your IsNull clause will do anything because it works against anything.