El query es asi solo que hay aun mas union pero con solo llegar hasta aqui ya no me funciona:
select
usuarios.FullName asesor,ISNULL(rutacu.New_nombre,'') ruta_cliente,count(usuarios.FullName) total_visitas_comerciales
,0 as total_clientes_pedidos,0 as clientes_con_pedidos_visitas,
0 as nro_pedidos,0 as pedidos_dolares,0 as promedio_pedidos,0 as clientes_nuevos,0 as llamadas_sgto,0 as coti_mes,0 as cotidolares,0 as cotitotales
,0 as cotidolatotales, 0 as cotiza_abiertas,0 coti_abiertas_dolares,0 as cotiz_perdidas,0 as cotiz_perdolares,0 as cotiza_Efectivas,0 as cotiza_Efectivasdolares
,DATEPART(year ,acti.CreatedOn) ano,DATEPART(month ,acti.CreatedOn) mes
from
ActivityPointerBase acti
inner join Appointment citas on acti .ActivityId= citas.ActivityId
inner join SystemUserBase usuarios on citas .OwnerId= usuarios.SystemUserId and usuarios.IsDisabled=0
INNER JOIN [AppointmentExtensionBase] citascustom ON citascustom.ActivityId=citas.ActivityId
inner join [dbo].[ActivityPartyBase] part on part.ActivityId=acti.ActivityId
left join AccountBase clie on clie.AccountId=part.PartyId
left join AccountExtensionBase cliecust on clie.AccountId=cliecust.AccountId
left join [dbo].[New_vendedorBase] vend on vend.New_vendedorId=cliecust.new_vendedorid
left join New_vendedorExtensionBase vendcust on vend.New_vendedorId=vendcust.New_vendedorId
left join New_rutaBase ruta on ruta.New_rutaId=cliecust.new_rutaid
left join New_rutaExtensionBase rutacu on ruta.New_rutaId=rutacu.New_rutaId
where
(citascustom.[GBI_ActividadMacro]) like ${parameter_tipo_vista}
and DATEPART(year ,acti.CreatedOn) = ${parameter_ano}
and DATEPART(month,acti.CreatedOn ) = ${parameter_mes}
and [PartyIdName] is not null
and part.PartyObjectTypeCode=1
group by usuarios.FullName,rutacu.New_nombre, DATEPART(year ,acti.CreatedOn),DATEPART(month ,acti.CreatedOn)
having count(usuarios.FullName)<>0
union
select
usu.FullName asesor ,isnull(rutacu.New_nombre,'') ruta_cliente,0 as total_visitas_comerciales
,count(distinct clie.Name) as total_clientes_pedidos,0 as clientes_con_pedidos_visitas,0 as nro_pedidos,0 as pedidos_dolares,0 as promedio_pedidos,0 as clientes_nuevos,0 as llamadas_sgto
,0 as coti_mes,0 as cotidolares,0 as cotitotales,0 as cotidolatotales, 0 as cotiza_abiertas,0 coti_abiertas_dolares,0 as cotiz_perdidas
,0 as cotiz_perdolares,0 as cotiza_Efectivas,0 as cotiza_Efectivasdolares,DATEPART(year ,pedidos.CreatedOn) ano,DATEPART(month ,pedidos.CreatedOn) mes
from
SalesOrder pedidos
left join SalesOrderBase pedc on pedidos .SalesOrderId= pedc.SalesOrderId
inner join SystemUser usu on usu .SystemUserId= pedc.OwningUser and usu.IsDisabled=0
inner join AccountBase clie on clie.AccountId=pedidos.[AccountId]
inner join AccountExtensionBase cliec on clie.AccountId=cliec.AccountId
left join New_vendedorBase vend on vend.New_vendedorId=cliec.New_vendedorId
left join New_vendedorExtensionBase vendcust on vend.New_vendedorId=vendcust.New_vendedorId
left join New_rutaBase ruta on ruta.New_rutaId=cliec.new_rutaid
left join New_rutaExtensionBase rutacu on ruta.New_rutaId=rutacu.New_rutaId
where
DATEPART(year ,pedidos.CreatedOn) = ${parameter_ano}
and DATEPART(month,pedidos.CreatedOn ) = ${parameter_mes}
group by usu.FullName,rutacu.New_nombre, DATEPART(year ,pedidos.CreatedOn),DATEPART(month ,pedidos.CreatedOn)
having count(distinct clie.Name)<>0
union
select
usu.FullName asesor ,isnull(rutacu.New_nombre,'') ruta_cliente
,0 as total_visitas_comerciales,0 as total_clientes_pedidos
,count(usu.fullName) as clientes_con_pedidos_visitas,0 as nro_pedidos,0 as pedidos_dolares,0 as promedio_pedidos,0 as clientes_nuevos,0 as llamadas_sgto,0 as coti_mes,0 as cotidolares,0 as cotitotales,0 as cotidolatotales
,0 as cotiza_abiertas,0 coti_abiertas_dolares,0 as cotiz_perdidas,0 as cotiz_perdolares,0 as cotiza_Efectivas,0 as cotiza_Efectivasdolares
,DATEPART(year ,pedidos.CreatedOn) ano, DATEPART(month,pedidos.CreatedOn) mes
from
SalesOrder pedidos left join SalesOrderBase pedc on pedidos .SalesOrderId= pedc.SalesOrderId
inner join SystemUser usu on usu .SystemUserId= pedc.OwningUser and usu.IsDisabled=0
inner join AccountBase clie on clie.AccountId=pedidos.[AccountId]
inner join AccountExtensionBase cliec on clie.AccountId=cliec.AccountId
left join New_vendedorBase vend on vend.New_vendedorId=cliec.New_vendedorId
left join New_vendedorExtensionBase vendcust on vend.New_vendedorId=vendcust.New_vendedorId
left join New_rutaBase ruta on ruta.New_rutaId=cliec.new_rutaid
left join New_rutaExtensionBase rutacu on ruta.New_rutaId=rutacu.New_rutaId
where
DATEPART(year ,pedidos.CreatedOn) = ${parameter_ano}
and DATEPART(month,pedidos.CreatedOn ) = ${parameter_mes}
and clie.name not in
(
select
clie.Name
from
ActivityPointerBase acti
inner join Appointment citas on acti .ActivityId= citas.ActivityId
inner join SystemUserBase usuarios on citas .OwnerId= usuarios.SystemUserId and usuarios.IsDisabled=0
INNER JOIN [AppointmentExtensionBase] citascustom ON citascustom.ActivityId=citas.ActivityId
inner join [dbo].[ActivityPartyBase] part on part.ActivityId=acti.ActivityId
left join AccountBase clie on clie.AccountId=part.PartyId
left join AccountExtensionBase cliecust on clie.AccountId=cliecust.AccountId
left join [dbo].[New_vendedorBase] vend on vend.New_vendedorId=cliecust.new_vendedorid
left join New_vendedorExtensionBase vendcust on vend.New_vendedorId=vendcust.New_vendedorId
left join New_rutaBase ruta on ruta.New_rutaId=cliecust.new_rutaid
left join New_rutaExtensionBase rutacu on ruta.New_rutaId=rutacu.New_rutaId
where
left(CONVERT(VARCHAR(24),citas.CreatedOn,112),4) = ${parameter_ano}
and DATEPART (month ,citas .CreatedOn)=${parameter_mes}
and citascustom.[GBI_ActividadMacro] like ${parameter_tipo_vista}
and [PartyIdName] is not null and part.PartyObjectTypeCode=1
)
group by usu.FullName,isnull(rutacu.New_nombre,''),DATEPART(year ,pedidos.CreatedOn),DATEPART(month,pedidos.CreatedOn)
union
select
usu.FullName,isnull(rutacu.New_nombre,'') ruta_cliente,
0 total_visitas_comerciales, 0 as total_clientes_pedidos,0 as clientes_con_pedidos_visitas,
count(usu.FullName) as nro_pedidos,0 as pedidos_dolares,0 as promedio_pedidos,0 as clientes_nuevos,0 as llamadas_sgto
,0 as coti_mes,0 as cotidolares,0 as cotitotales,0 as cotidolatotales, 0 as cotiza_abiertas,0 coti_abiertas_dolares
,0 as cotiz_perdidas,0 as cotiz_perdolares,0 as cotiza_Efectivas,0 as cotiza_Efectivasdolares,DATEPART(year ,pedidos.CreatedOn) ano,DATEPART(month,pedidos.CreatedOn) mes
from
SalesOrder pedidos
left join SalesOrderBase pedc on pedidos .SalesOrderId= pedc.SalesOrderId
inner join SystemUser usu on usu .SystemUserId= pedc.OwningUser and usu.IsDisabled=0
inner join AccountBase clie on clie.AccountId=pedidos.[AccountId]
left join AccountExtensionBase cliecust on clie.AccountId=cliecust.AccountId
left join [dbo].[New_vendedorBase] vend on vend.New_vendedorId=cliecust.new_vendedorid
left join New_vendedorExtensionBase vendcust on vend.New_vendedorId=vendcust.New_vendedorId
left join New_rutaBase ruta on ruta.New_rutaId=cliecust.new_rutaid
left join New_rutaExtensionBase rutacu on ruta.New_rutaId=rutacu.New_rutaId
where
DATEPART(year ,pedidos.CreatedOn) = ${parameter_ano}
and DATEPART(month,pedidos.CreatedOn ) = ${parameter_mes}
group by usu.FullName,DATEPART(year ,pedidos.CreatedOn),DATEPART(month,pedidos.CreatedOn)
,isnull(rutacu.New_nombre,'')
having COUNT(clie.AccountId)<>0
Lo mas extraño es que si yo le quemo los filtros en vez de poner los parametros si me funciona
Gracias por tu aporte
