Home / Questions / 36486
Accepted
German AT
Asked: 2020-11-29 10:22:30 +0800 CST 2020-11-29 10:22:30 +0800 CST

SQL - Assign a value in a data retrieval operation

  • 772

In a simple query, I have a table with the following structure:

SELECT * FROM [SAMX].[dbo].[REPORTE_ESTADO_CUENTA_GLOBAL] where intencion_id=1657 ORDER BY intencion_id,fecha

enter image description here

I need to fill the account column with a '0' or a '1' depending on whether the query contains an initial charge of the contract as follows: - if it does not, account must be '0' - if it does, you must check the status of that record - if status is 'C', account should still be '0' - if status is '', account should be '1', and all records thereafter, account should be '1' '

I tried using a variable, but I can't combine a SELECTvalue assignment with a data fetch.

DECLARE @var1 int;
select @var1=0;  
SELECT  @var1=(CASE tipomovimiento WHEN 'Cargo Inicial del Contrato' THEN 1 ELSE 0 END)
      ,@var1 AS cuenta,[fecha],[tipomovimiento],[abono],[cargo],[estatus],[intencion_id] FROM REPORTE_ESTADO_CUENTA_GLOBAL  where intencion_id=1657 ORDER BY intencion_id,fecha

Could you suggest me a solution?

The expected result would be like this: enter image description here

sql

1 Answers

  1. Best Answer

    Ok, a LEFT JOINand an expression CASEshould work:

    SELECT  CASE 
            WHEN B.intencion_id IS NULL THEN 0
            WHEN A.estatus = 'C' THEN 0
            WHEN A.estatus = '' THEN 1
        END Cuenta,
        A.fecha,
        A.tipomovimiento,
        A.abono,
        A.cargo,
        A.estatus,
        A.intencion_id
FROM [SAMX].[dbo].[REPORTE_ESTADO_CUENTA_GLOBAL] A
LEFT JOIN ( SELECT *
            FROM [SAMX].[dbo].[REPORTE_ESTADO_CUENTA_GLOBAL]
            WHERE tipomovimiento = 'Cargo Inicial del Contrato') B
    ON A.intencion_id = B.intencion_id
    AND A.fecha >= B.fecha
WHERE A.intencion_id = 1657 
ORDER BY A.intencion_id,fecha;
    • 1