Filtering data on date but showing all values except one in query if date doesn't match [SQL & C#]

Question

I'm pretty stuck on this query. First of all, what do I want to achieve? I want to inner join on three tables (already correct) and filter out data depending if the date is in the current month. If the date is not in the current month the field "bedrag" shouldn't be filled in and should be set to zero. I should also see everything from the table, but the 'bedrag' field shouldn't be changed.

If the date IS from the current month it should add to 'bedrag' (note the group by to do that) and show everything too. Now my query does everything correct EXCEPT for when you have data thats not from this month. Then records will go lost / won't be showed.

Table design: https://i.stack.imgur.com/zrFGq.jpg

The query:

SELECT        c.id, c.omschrijving, l.maximumBedrag, SUM(IIF(IsNULL(f.bedrag), 0, f.bedrag)) AS bedrag
FROM            ((Categorie c LEFT OUTER JOIN
                         Financien f ON f.categorieId = c.id) LEFT OUTER JOIN
                         Limiet l ON l.categorieId = c.id)
WHERE        (f.inkomstOfUitgave IS NULL) OR
                         (f.inkomstOfUitgave = 1)  AND (format(f.datum, 'yyyy-mm-dd') > format(NOW(), 'yyyy-mm'))
GROUP BY c.id, f.categorieId, c.omschrijving, l.maximumBedrag

Do note: this is NOT normal SQL, but a varient thats used in C# to get data from an Access DB into an DAL and BLL layer.

An example where you can see what messages with the query and HOW it goes wrong: https://i.stack.imgur.com/vsuy1.jpg

Could anybody tell me how I can get all the data to show but without the bedrag added onto it if the records aren't from this month?

Thanks!

Answer 1

Maybe move some conditions from WHERE to LEFT OUTER JOIN?

SELECT c.id, sum(isnull(f.bedrag, 0))
FROM @cat c
LEFT OUTER JOIN @fin f ON f.catid=c.id AND f.datum > getdate()
LEFT OUTER JOIN @lim l ON l.catid = c.id

Check this SQL Fiddle