SQL - Assign a value in a data recovery operation

Question

SQL - Assign a value in a data recovery operation

Navigation

#1 by (1 votes)

0

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

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

I tried using a variable, but I can not combine a SELECT of value assignment with one of data acquisition.

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 a solution?

The expected result would be like this:

sql sql-server

asked by German AT 28.11.2016 в 19:22

source

1 answer

Problem with php code error array to string conversion and undefined index Android App with Retrofit consuming of api does not show the list

score 1 · Accepted Answer

Ok, a LEFT JOIN and an expression of CASE should 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;