Select Active Lot

Here is a suggestion that uses the product table as a starting point.

-- código #1 v3
declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

--
with ctePreços as (
SELECT P.Id, P.Name, T.Name as Tipo, 
       (SELECT top (1) Preco 
          from Lot as L1 
          where L1.ProdutoId = P.Id
                and L1.Fim is NULL
                and @Hoje >= L1.Inicio 
          order by L1.Inicio desc) as Oficial,
       (SELECT Preco 
          from Lot as L2 
          where L2.ProdutoId = P.Id
                and @Hoje between L2.Inicio and L2.Fim) as Oferta
  from Products as P
       inner join Types as T on T.Id = P.Tipo
)
SELECT Id, 
       Name as [Nome do produto], 
       Tipo as [Tipo de produto],
       coalesce(Oferta, Oficial) as [Preço atual]
  from ctePreços;

Here's another approach, which uses the batch table as a starting point. In this case, only the products that have information in the Lot table are listed.

-- código #2
declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

--
with cteAtual as (
SELECT L.*,
       Seq= row_number() over (partition by L.ProdutoId order by L.Inicio desc)
  from Lot as L
  where L.Inicio <= @Hoje
        and (L.Fim is NULL or L.Fim >= @Hoje)
)
SELECT A.ProdutoId, 
       P.Name as [Nome do produto], 
       T.Name as [Tipo de produto],
       A.Preco as [Preço atual]
  from cteAtual as A
       inner join Products as P on P.Id = A.ProdutoId
       inner join Types as T on T.Id = P.Tipo
  where A.Seq = 1;         

I believe the following query will solve your problem: I did not put the types because it is irrelevant to the problem, just be added, and just a question of names, I believe batch is not the correct name for this table. A lot must have an identification, date of manufacture and validity. This table looks more like a price list.

Select distinct
    p.nome,
    coalesce(lp.preco, l.preco,0) as preco
from produtos p 
left outer join lote lp on lp.produtoid = p.id 
                        and lp.id = (select 
                                        max(x.id) 
                                     from lote x 
                                     where x.produtoid = lp.produtoid 
                                     and x.inicio <= getdate() 
                                     and x.fim >= getdate())
left outer join lote l on l.produtoid = p.id 
                       and l.inicio <= getdate() 
                       and l.fim is null

Use the expression WITH to prioritize prices together with the ROW_NUMBER function:

WITH Prices (Id,
             ProdutoID,
             Preco,
             Inicio,
             Fim,
             Prioridade)
AS (
  SELECT lot.Id,
         lot.ProdutoID,
         lot.Preco,
         lot.Inicio,
         lot.Fim,
         ROW_NUMBER() OVER(PARTITION BY lot.ProdutoID ORDER BY lot.Inicio) AS Prioridade
    FROM Lot lot
   WHERE GETDATE() BETWEEN lot.Inicio AND ISNULL(lot.Fim, GETDATE())
)
SELECT prod.Name  AS 'Nome do Produto',
       type.Name  AS 'Tipo do produto',
       pric.Preco AS 'Preço atual'
  FROM Products prod
       INNER JOIN Types type ON type.Id = prod.Tipo
       INNER JOIN Prices pric ON pric.ProdutoID = prod.Id
 WHERE pric.Prioridade = 1;