get data from a table, transact sql

Question

get data from a table, transact sql

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

Dear, I need your help with this problem that I encountered when trying to obtain certain data from a table. It happens that I have a table that stores the telephone numbers of the customers who call to make a certain purchase, the purchase is directly related to the customer, the phone number and the time I call. What I want is to obtain only the data of the calls that ended in a sale. EJ:

SELECT distinct lla.año_periodo,
                lla.mes_periodo,
                lla.dFecha,
                lla.T_hora,
                lla.dFechaHora,
                lla.dFin,
                lla.tiempo as tiempo_segundos,
                lla.sNumeroTelefono,
                num.descripcion as Cliente_llamada,
                cli.Cod_Cliente as cod_cliente_llamada,
                lla.tipo,
                lla.Agente as cod_ejecutiva,
                bor.nom_ejecutiva,
                new.Nom_Cliente as Cliente_venta,
                new.cod_cliente as cod_cliente_venta,
                new.cod_vendedora,
                new.nom_vendedora,
                new.Cod_Promotora,
                new.Nom_Promotora,
                new.val_precio_pesos_sum

FROM (#LLAMADAS LLA
left JOIN #BORRADOR_EJECUTIVA BOR
on bor.cod_ejecutiva = lla.Agente
left join #NUMEROS_CLIENTES num
on lla.sNumeroTelefono = num.num_cliente
left join #NUEVA_TABLA new
on bor.nom_ejecutiva = new.nom_vendedora
and num.descripcion = new.Nom_Cliente
and lla.T_hora = new.T_hora
and lla.dFecha = new.regis_Fecha
left join Analisis.dbo.CLIENTES cli
)where tiempo > 30

order by dFechaHora

this query generates something like this:

hora          Numero    Cliente     Venta
-------------------------------------------
18:56:02    87736452    camilo      NULL
19:32:24    67890247    alfaro      $12000
19:09:37    93487201    jorge       $789
20:23:00    65578909    cristian    NULL
20:23:00    65578909    cristian    $456
20:23:00    65578909    cristian    NULL
22:56:48    81940592    alvaro      NULL

What I need is for me to generate the table but only with the Cristian data that has a sale.

Thank you very much already.

sql

asked by g.cifuentes 02.03.2017 в 03:43

source

3 answers

Export data sql server to excel C # window form TypeError: only length-1 arrays can be converted to Python scalars

score 0 · Answer 1

I'm not sure I understood you perfectly, but I think you just need another condition, like this:

SELECT distinct lla.año_periodo,
                lla.mes_periodo,
                lla.dFecha,
                lla.T_hora,
                lla.dFechaHora,
                lla.dFin,
                lla.tiempo as tiempo_segundos,
                lla.sNumeroTelefono,
                num.descripcion as Cliente_llamada,
                cli.Cod_Cliente as cod_cliente_llamada,
                lla.tipo,
                lla.Agente as cod_ejecutiva,
                bor.nom_ejecutiva,
                new.Nom_Cliente as Cliente_venta,
                new.cod_cliente as cod_cliente_venta,
                new.cod_vendedora,
                new.nom_vendedora,
                new.Cod_Promotora,
                new.Nom_Promotora,
                new.val_precio_pesos_sum

FROM (#LLAMADAS LLA
left JOIN #BORRADOR_EJECUTIVA BOR
on bor.cod_ejecutiva = lla.Agente
left join #NUMEROS_CLIENTES num
on lla.sNumeroTelefono = num.num_cliente
left join #NUEVA_TABLA new
on bor.nom_ejecutiva = new.nom_vendedora
and num.descripcion = new.Nom_Cliente
and lla.T_hora = new.T_hora
and lla.dFecha = new.regis_Fecha
left join Analisis.dbo.CLIENTES cli
)where tiempo > 30
and new.val_precio_pesos_sum is not null

order by dFechaHora

Note the part that says and new.val_precio_pesos_sum is not null at the end, this way you would be discarding all calls that did not end in sales.

score 0 · Answer 2

You could compare

and Venta is not null

that if you want to exclude all nulls, what happens with the left join is that they tend to multiply the records, but obviously one of the tables contains more than one record that is not being excluded, it can be the union by the description and the client name, maybe you could consider generating unique access codes for the tables.

score 0 · Answer 3

"What I need is for me to generate the table but only with the Cristian data that has a sale."

If you want me to generate a table with what you mention above you can use:

SELECT 
    c1,c2 
INTO 
    #TablaTemporal 
FROM 
    (toda tu consulta) 
where 
    Cliente='cristian' and Venta IS NOT NULL

All your answer would be in #TablaTemporal obviously this table is created at the moment of doing your SELECT as it has the columns and data types.