Hi, I am Juan Martin from Argentine. I need to see values from a table. One of the fields I want to bring it in a date range but it returns error in each attempt to combine the queries that separately, work correctly.
Basically I want to see the balance as of today and the balance of the previous month. Could you tell me what resource to use to achieve this. Thank you very much!
This my SQL-SERVER code
SELECT
personas.nombre_visible AS Nombre,
(
SELECT
saldo
FROM
ctacte
WHERE
ctacte.fecha IN (
SELECT
MAX(fecha)
FROM
ctacte
WHERE
ctacte.fecha BETWEEN (
SELECT
CONVERT(
VARCHAR(15),
DATEADD(MONTH, -2, CONVERT(datetime, MAX(fecha))),
106
)
FROM
ctacte
)
AND (
SELECT
CONVERT(
VARCHAR(15),
DATEADD(MONTH, -1, CONVERT(datetime, MAX(fecha))),
106
)
FROM
ctacte
)
GROUP BY
id_cliente
)
) AS [Saldo Anterior],
CASE
WHEN importe > 0 THEN SUM(importe)
ELSE 0
END 'Debito',
CASE
WHEN importe < 0 THEN SUM(importe)
ELSE 0
END 'Credito',
saldo
FROM
ctacte
INNER JOIN personas ON ctacte.id_cliente = personas.id_persona
GROUP BY
nombre_visible,
ctacte.saldo,
importe,
saldo,
ctacte.fecha;
What I have tried:
Try using EXIST and also try all the queries separately and they work correctly.