I need get an empty record

  • 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

  • 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.

  • 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?


    - Craig Farrell

    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

  • 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