SELECT L.NOME AS Empresa,
CONVERT(date, L.CREATEDAT, 111) AS DataCadastro,
CONVERT(date, L.UpdatedAt, 111) AS DataAtualizacao,
U.Nome PreVendedor,

CONVERT(date, (select DATEADD(HOUR,-3, max(hl1.CreatedAt)) from Historicolead hl1
where hl1.FaseLeadId = (select top 1 flentr.id from faselead flentr
where flentr.empresaclienteid = l.empresaclienteid and flentr.defase = 'Entrada')
and l.id = hl1.LeadId), 111) DataFiltro1,

(SELECT top 1
ISNULL(DATEDIFF(Mi,l.createdat,hl9.CreatedAt),0) from Historicolead hl9
where hl9.leadid = l.id
and hl9.faseleadid = (select top 1 flentr.id from faselead flentr
where flentr.empresaclienteid = l.empresaclienteid and flentr.defase = 'Entrada')) as 'Intervalo_Cad_x_F1',

CONVERT(date, (select DATEADD(HOUR,-3, max(hl2.CreatedAt)) from Historicolead hl2
where hl2.FaseLeadId = (select top 1 flfiltro1.id from faselead flfiltro1 where flfiltro1.empresaclienteid = l.empresaclienteid and flfiltro1.defase = 'Filtro 1')
and l.id = hl2.LeadId), 111) DataFiltro2,

(SELECT top 1
ISNULL(DATEDIFF(Mi,l.createdat,hl8.CreatedAt),0) from Historicolead hl8
where hl8.leadid = l.id
and hl8.faseleadid = (select top 1 flentr.id from faselead flentr
where flentr.empresaclienteid = l.empresaclienteid and flentr.defase = 'Filtro 1')) as 'Intervalo_Cad_x_F2',


CONVERT(date, (select DATEADD(HOUR,-3, max(b3.DtAgendamento)) from Historicobox b3 where b3.leadid = l.id), 111) DataAgendamento,

(SELECT top 1
ISNULL(DATEDIFF(Mi,l.createdat,hb8.dtagendamento),0) from HistoricoBox hb8
where hb8.leadid = l.id) as 'Intervalo_Cad_x_Agendamento',

CONVERT(date, (select DATEADD(HOUR,-3, max(b4.HrIni)) from Box b4 where l.id = b4.LeadId), 111) DataReuniao,

CONVERT(date, DATEADD(HOUR,0, ve.DtVenda), 111) DataVenda,

(SELECT top 1
ISNULL(DATEDIFF(Mi,l.createdat,v8.dtvenda),0) from venda v8
where v8.leadid = l.id) as 'Intervalo_Cad_x_Venda'

FROM LEAD L

JOIN Usuario U ON u.Id = l.PreVendedorId
LEFT JOIN Venda ve ON ve.LeadId = l.Id

WHERE l.empresaclienteid=@EmpresaClienteId
order by l.nome