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'
;