Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL
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.
Posted
Updated 1-Dec-22 4:22am
v2
Comments
OriginalGriff 1-Dec-22 10:06am    
Instead of code that doesn't work, provide a basic sample of the input data and a separate sample of the output you are trying to get along with and explanation of what is wrong with the code you have.

We don't have access to your data, so we don't really know what you are trying to achieve!



Use the "Improve question" widget to edit your question and provide better information.
Richard Deeming 1-Dec-22 10:25am    
All those CONVERT calls on the fecha column makes me suspect you're storing dates as strings. Don't do that. Use one of the Date and time types[^] instead.

Storing dates as strings not only wastes space and makes your queries more complicated, but it also leads to errors. For example, if your column contains "1/2/22", it's not clear whether that's meant to be 1st February 2022, 2nd January 2022, or 22nd February 0001. All three of those would be valid interpretations in different cultures.

1 solution

You can use the Union command according to your needs, search on Google, many examples are given
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900