March 5, 2011 at 3:00 am
Hi All in the next query I don't get any result because doesn't exist any record related, but really I need to get a result with 0 value
SELECT cp.nombre,COUNT(*) from ems_cliente as c
left join EMS_CatPais as cp on c.catpais=cp.id
left join WF_Proceso_64 as w64 on c.id=w64.cliente
WHERE Cliente not in (SELECT Padreid FROM EMS_Cliente_Propuestas WHERE Estado =2 )
and left(cast(w64.Fecha as varchar(8)),4)=year(@fecha) and substring(cast(w64.Fecha as varchar(8)),5,2)=month(@fecha)
GROUP BY cp.nombre
Please could you check my query and let me know your pint of view, I can´t use If exists because this query is a part of union main query.
Thanks in advance
Francisco Racionero
twitter: @fracionero
March 7, 2011 at 11:49 am
It's not quite clear what you mean from the description you have written, possibly because of a language barrier.
Do you mean that there are not records in the related tables? You have a group by your left outer join table, which is a little strange to me. Why is this left outer joined? If there isn't a value you won't get a count(*) at all.
March 7, 2011 at 12:04 pm
I'm having a similar difficulty as Steve.
Are you looking to get a list of every entry in ems_cliente, and then get the counts as described by your where clause?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 8, 2011 at 12:27 am
The issue is solved with a workaround, I had to extract data from several tables and the first idea was perform an union query, but I discard that option, and at last I have declared a table variable and with if not exist clause select the correct statement in each case.
if not exists (select c.Nombre,COUNT(*) from EMS_Cliente c
INNER JOIN EMS_Cliente_Facturas cf ON c.Id = cf.PadreId
WHERE cf.Estado not in (3) group by c.nombre)
begin
insert into @tbkpi
SELECT
'Procesos de Administración' as 'Grupo',
'F8' as 'KPI',
'Actividad' as 'Tipo',
'Cobros' as 'Proceso',
'Volumen de cobros pendientes por cliente' as 'Descripción',
datename(month,@fecha) as 'Frecuencia',
'Clientes No Disponible' as 'Objeto',
'0 €' as 'Valor',
0 as 'ValorInt'
end
else
begin
insert into @tbkpi
SELECT
'Procesos de Administración' as 'Grupo',
'F8' as 'KPI',
'Actividad' as 'Tipo',
'Cobros' as 'Proceso',
'Volumen de cobros pendientes por cliente' as 'Descripción',
datename(month,@fecha) as 'Frecuencia',
'Clientes ' + c.Nombre as 'Objeto',
cast(sum(cf.Total) as varchar) + ' €' as 'Valor',
cast(sum(cf.Total) as int) as 'ValorInt'
FROM EMS_Cliente c
INNER JOIN EMS_Cliente_Facturas cf ON c.Id = cf.PadreId
WHERE cf.Estado not in (3)
group by c.Nombre
end
-- Obtener la vista de los KPIs
select * from @tbkpi order by KPI
Francisco Racionero
twitter: @fracionero
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply