isnull not working SQL Server

Question

I have the following problem , I'm doing a select with multiple where. example:

select isnull(fechamov,0)
from movimientos
where (provedor='1000' and depto='20') or
      (provedor='1001' and depto='200')

result:

|fecha de movimiento|
|2015-02-20|

the problem is that in some cases do not have dates , consultation with 100 different Where just throws me 90 dates , the other 10 do not exist. I want to do is this:

select isnull(fechamov,0)
from movimientos
where (provedor='1000' and depto='20') or
      (provedor='1001' and depto='200')

result:

|Fechamovimiento|
|0|
|2015-02-20|

and try isnull , coalesce , to replace the null results, with 0 but does not. there any way to fix this?

Answer 1

try this

select isnull(fechamov,0)
from movimientos
where (provedor='1000' and depto='20') or
      (provedor='1001' and depto='200') or fechamov is null

this will add rest of the 10 row which contains null values into fechamov, as you may be looking for

Answer 2

You need a left join:

with p as (
      select '1000' as prevedor, '20' as depto union all
      select '1001', '200'
    )
select coalesce(m.fechamov,0)
from p left join
     movimientos m
     on m.prevedor = p.prevedor and m.depto = p.depto;

Answer 3

try this pls

select isnull(fechamov,0)
from movimientos
where (provedor='1000' and depto='20') or
(provedor='1001' and depto='200') 
union 
select isnull(fechamov,0)
from movimientos
where  fechamov is null