SQL joining with parameter problem

Question

I asked this question before:

http://stackoverflow.com/questions/1921818/how-do-i-bring-back-an-entire-range-of-dates-in-sql-between-two-dates-even-when

but I now need to only select incidents that have a Status of "E" for emergency.

I can't put WHERE status='E' though, because that will stop it returning an entry for every single date.

How can I solve this?

Answer 1

Just add it to the LEFT OUTER JOIN ... ON, since this is a contition for the joined rows as far as I understand the question.

Something like this:

WITH DateRange(date) AS (
 SELECT @dateFrom dt
 UNION ALL
 SELECT DATEADD(dd, 1, date) date FROM DateRange WHERE date < @dateTo
)
SELECT DateRange.date, count(incident.id) 
FROM DateRange
LEFT OUTER JOIN incident
 ON incident.date >= DateRange.date 
 AND incident.date < DATEADD(dd, 1, DateRange.date)
 AND incident.status = 'E'
GROUP BY DateRange.date
ORDER BY DateRange.date

Answer 2

Like you said in your question, putting the condition in your WHERE clause effectively turns your LEFT JOIN into an INNER JOIN.

You should add this to your LEFT JOIN criteria as the intent is that it's a condition of the join.

So something like table1

LEFT JOIN table2 ON table1.field = table2.field AND table1.status='E'