Help with Mysql query?

-1

I have two tables, a Sales, and Other Subscriptions_sales, the sales of the clients are saved with their total value and everything is on credit in the day several sales can be made to a client as no, the client can pay to the total balance you have several times in the day as you may not.

With the following fields a little summarized

TABLA VENTAS
Fecha
Cliente
Valor_Total

TABLA ABONOS_VENTA
Fecha
Cliente
Valor_debe
Valor_Abono
Nuevo_Saldo

What I want to do is a query that brings me grouped by day the total value of the sale made to the client and the total value that I paid on the day, as I said you may buy something or not, or you may pay the debt that day or not.

I want to bring that data in a single row grouped by day in a range of dates

Let it out like this

FECHA | CLIENTE | VALOR_VENTA | VALOR_ABONADO
10-10-2017  ANDRES  $10.000       $5.000   
11-10-2017  ANDRES  $0                 $5.000   
12-10-2017  ANDRES  $100.000     $0   
13-10-2017  ANDRES  $0                  $50.000   
14-10-2017  ANDRES  $10.000        $5.000  

Something like that is what I want to do, but I do not give how, I blocked that there may be records of that day in one table and not in the other and vice versa. The fields of the tables are not only those, I just try to illustrate a bit so as not to entangle more the problem that I have, I thank you for leaving queries that you think can help me.

See more fields and other tables, the above is an illustration

SELECT V.'CONSECUTIVO',
V.'FECHA',
V.'SEMANA',
V.'SEMANAINICIO',
V.'SEMANAFIN',
V.'TIPODCTOCLIENTE',
V.'DOCUMENTOCLIENTE',
'----' AS 'DEBIA',
'----' AS 'ABONO',
V.'CANTIDADPOLLO',
V.'UNIDADPESO',
V.'PESOPOLLO',
V.'VALORUNITARIOPOLLO',
V.'VALORTOTAL',
'----' AS 'DEBE',
'----' AS 'AFAVOR',
V.'CLIENTE' ,
'----' AS 'COBRADOR',
'----' AS 'VALOR_A_FAVOR',
'----' AS 'VALOR_DEBE'
FROM 'vsventasclientegranjasemana' V WHERE V.'FECHA'>=_FECHAINI AND 
V.'FECHA'<=_FECHAFIN 

UNION

SELECT 
V.'ID' AS CONSECUTIVO,
V.'FECHA',
week(V.'FECHA',1) AS 'SEMANA',
(V.'FECHA' - interval (dayofweek(V.'FECHA') - 1) day) AS 'SEMANAINICIO',
(V.'FECHA' + interval (7 - dayofweek(V.'FECHA')) day) AS 'SEMANAFIN',
V.'TIPODCTOCLIENTE',
V.'DOCUMENTOCLIENTE',
V.'DEBIA',
V.'ABONO',
'----' AS 'CANTIDADPOLLO',
'----' AS 'UNIDADPESO',
'----' AS 'PESOPOLLO',
'----' AS 'VALORUNITARIOPOLLO',
'----' AS 'VALORTOTAL',
V.DEBE,
V.'AFAVOR',
P.'NOMBRE' AS CLIENTE,
PC.'NOMBRE' AS COBRADOR,
vs.VALOR_A_FAVOR,
vs.VALOR_DEBE
FROM 'venta_cliente_saldo_abono' V
INNER JOIN 'venta_cliente_saldo' VS ON V.'CORRESPONDE'=VS.'CORRESPONDE' AND                 V.'TIPODCTOCLIENTE'=VS.'TIPODCTOCLIENTE' AND V.'DOCUMENTOCLIENTE'=VS.'DOCUMENTOCLIENTE'
    INNER JOIN 'persona' P ON V.'TIPODCTOCLIENTE'=P.'TIPODCTO' AND V.'DOCUMENTOCLIENTE'=P.'DOCUMENTO'
    INNER JOIN 'persona' PC ON V.'TIPODCTOCOBRADOR'=PC.'TIPODCTO' AND V.'DOCUMENTOCOBRADOR'=PC.'DOCUMENTO'
    WHERE V.'FECHA'>=_FECHAINI AND 
    V.'FECHA'<=_FECHAFIN 
    AND V.CORRESPONDE='1'

    ;
    
asked by SergioArivera 29.10.2017 в 02:13
source

1 answer

-1

Hey, what a Friend!

According to what I understood, you can do that by means of a query with Join , the most successful one would be Inner Join , on the internet there are many tutorials and explanations of it so that understand.

Also to bring everything for a range of dates uses the BETWEEN sentence, and again I tell you the same with this part, on the internet there are many explanations about it.

Here I give you an example, link but so that you understand better I advise you to read before everything I said.

I hope you serve, salu2!

    
answered by 29.10.2017 в 05:29