March 21, 2019 at 3:35 pm
Hi guys, I have this code :
SELECT CC.PERIODO,
CC.ID,
CC.FACTURA,
CC.FECHA,
CC.MONTO,
Q.FECHA_PAGO,
Q.METODO,
MIN(ABS(DATEDIFF(DAY,CC.FECHA, Q.FECHA_PAGO))) AS DIAS_PAGO
FROM CuentasXCobrar CC
LEFT JOIN ( SELECT PERIODO,
ID,
MONTO,
FECHA, 'WEB' AS METODO
FROM Tablas Ventas_web
UNION PERIODO,
ID,
MONTO,
FECHA,
'ATC' AS METODO
FROM Ventas_presenciales
) Q ON CC.ID = Q.ID AND CC.PERIODO = Q.PERIODO
This query return this :
PERIODO | ID | FACTURA | FECHA | MONTO | PAGO | METODO | DIAS_PAGO |
201902 | 490 | 160175 | 14/02/2019 | 522,830 | 28/02/2019 | WEB | 14 |
201902 | 490 | 160175 | 14/02/2019 | 954,190 | 28/02/2019 | ATC | 14 |
201902 | 402 | 241288 | 21/02/2019 | 78,966 | 26/02/2019 | ATC | 5 |
201902 | 411 | 165849 | 21/02/2019 | 415,966 | 26/02/2019 | ATC | 5 |
201902 | 415 | 165041 | 18/02/2019 | 102,465 | 20/02/2019 | ATC | 2 |
201902 | 415 | 165041 | 18/02/2019 | 200,985 | 19/02/2019 | WEB | 1 |
I need to create a new colum "MONTO" in order to put de values of repeat ID, in this way
PERIODO | ID | FACTURA | FECHA | MONTO | MONTO 2 | PAGO | METODO | DIAS_PAGO |
201902 | 490 | 160175 | 14/02/2019 | 522,830 | 954,190 | 28/02/2019 | WEB | 14 |
201902 | 402 | 241288 | 21/02/2019 | 78,966 | 0 | 26/02/2019 | ATC | 5 |
201902 | 411 | 165849 | 21/02/2019 | 415,966 | 0 | 26/02/2019 | ATC | 5 |
201902 | 415 | 165041 | 18/02/2019 | 102,465 | 200,985 | 19/02/2019 | ATC | 1 |
I mean, I need to put the repeats records on another column and delete the record who has the highed "DIAS_PAGO" number.
I will preciated.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy