Create a query that does not duplicate data in SQL SERVER [closed]

Question

Create a query that does not duplicate data in SQL SERVER [closed]

Navigation

#1 by (1 votes)

-1

Good afternoon I would like to know how to make a query that would come out something like this

  SELECT 
    Paciente= HC.IdPaciente ,
    Fecha_Adm = CONVERT(char(10), E.FechaAdmision, 101),
    Activo =  CASE WHEN  E.Estado = 'Activo' THEN 1 END ''end,
    Anulado = CASE WHEN E.Estado = 'Anulado' THEN 1 END '' END,
    Estado =  CASE WHEN E.Estado = 'Anulado' THEN 'Solo Anulado' 
                  WHEN E.Estado = 'Activado' and E.Estado= 'Anulado'  
                  THEN 'Activado'  ELSE 'Activado' END
    FROM dbo.VW_SA_EM_REP_ListadoAtencionEmergencia E
    LEFT JOIN SS_GE_Paciente HC ON  E.CodigoHC = HC.CodigoHC    
    WHERE 
        Year(FechaAdmision)=2018 
        AND Month(FechaAdmision)=08
        AND TipoAtencion= 'Emergencia'
        AND E.Sucursal = 'CEG'
            GROUP BY  HC.IdPaciente, 
                CONVERT(char(10), E.FechaAdmision, 101), 
                CASE WHEN  E.Estado = 'Activo' THEN 1 ELSE ''END,
                CASE WHEN  E.Estado = 'Anulado' THEN 1 ELSE ''END,
                CASE WHEN E.Estado = 'Anulado' THEN 'Solo Anulado' 
                     WHEN E.Estado = 'Activado' and E.Estado= 'Anulado'  THEN 'Activado'  ELSE 'Activado' END
                ORDER BY Paciente, Fecha_Adm

RESULT OF THE QUERY

'Idpaciente' 'fecha_Admision' 'activo' 'anulado'
    '28'         '10/10/2018'    '1'      '0'
    '28'         '10/10/2018'    '0'      '1'
    '29'         '10/10/2018'    '1'      '0'
    '29'         '19/10/2018'    '0'      '1'

- > and what you want is that it should come out like this:

'Idpaciente' 'fecha_Admision' 'activo' 'anulado'
    '28'         '10/10/2018'    '1'      '1'
    '29'         '19/10/2018'    '1'      '1'

Click here to see the example

sql sql-server

asked by Christian Guerra Huertas 30.10.2018 в 20:28

source

1 answer

Sort string array alphabetically RESOLVED Client python server 3

score 1 · Accepted Answer

Well, if I understood correctly, you should do something like this:

SELECT 
Paciente= HC.IdPaciente ,
Fecha_Adm = CONVERT(char(10), E.FechaAdmision, 101),
Activo =  SUM(CASE WHEN E.Estado = 'Activo' THEN 1 else 0 END),
Anulado = SUM(CASE WHEN E.Estado = 'Anulado' THEN 1 else 0 END),
Estado =  CASE WHEN MIN(E.Estado) = 'Anulado' THEN 'Solo Anulado' 
              WHEN MAX(E.Estado) <> MIN(E.Estado) THEN 'Hay anulados y activos' 
              ELSE 'Activado' END
FROM dbo.VW_SA_EM_REP_ListadoAtencionEmergencia E
LEFT JOIN SS_GE_Paciente HC ON  E.CodigoHC = HC.CodigoHC    
WHERE 
    Year(FechaAdmision)=2018 
    AND Month(FechaAdmision)=08
    AND TipoAtencion= 'Emergencia'
    AND E.Sucursal = 'CEG'
        GROUP BY  HC.IdPaciente, 
            CONVERT(char(10), E.FechaAdmision, 101), 
            ORDER BY Paciente, Fecha_Adm

In the last field I did not understand well what you need, but in case you have active and inactive records I placed 'There are voided and active', change it by the message you consider.

I may have some syntax error or something, like I can not prove it ...