Group record by column including NULL values

Question

Group record by column including NULL values

Navigation

#1 by (0 votes)

1

I have this script

SELECT 
n.Conteo,  MAX(d.IdPaciente) OVER(PARTITION BY d.IdPaciente ORDER BY d.IdPaciente  )IdPaciente , IdPaciente, d.FechaAnalitica, d.GlucosaPre, d.Sodio, d.Calcio
FROM 
    ##Numeros n
LEFT JOIN
(
    SELECT 
        ROW_NUMBER() OVER(ORDER BY pt.IdPaciente ASC) row,
        pt.IdPaciente, FechaAnalitica, GlucosaPre,Sodio, Calcio
    FROM ANA_BIOQUIMICA inner join ##PAC_ID_TEMP pt 
    on ANA_BIOQUIMICA.IdPaciente = pt.IdPaciente 
)d
 ON n.Conteo = d.row
where n.Tabla = 'ANA_BIOQUIMICA'
order by d.IdPaciente,  n.Conteo

This is the actual script ... as shown ... there is no where filter, since the resulting list must be created for a set of IdIdent values, in this case it is where the script does not group the null for each code of IdPaciente ... but put them together at the beginning of the list.

Additionally, I comment on the following. The table ## Numbers contains the maximum register count per patient ... in the case I am considering, there are two patients, code 2450 and code 38

So the resulting list should be 88 records ... since the table ## Numbers for the ANA_BIOQUIMICA table is set for that value

Then the left join with ## Numbers, serves to complete with null records, the ones that are missing from each patient code.

then there should be 44 records for each code of IdPaciente ... for the case of code 38 11 records with values and 33 with values null

sql-server

asked by Emerson Rios 05.09.2017 в 19:06

source

1 answer

Error Directory index forbidden by Options directive: Sometimes I do not recover the varaible of SESSION in php

score 0 · Accepted Answer

I do not really know if the following answers your question:

SELECT  n.Conteo,  
    n.IdPaciente, 
    d.FechaAnalitica, 
    d.GlucosaPre, 
    d.Sodio, 
    d.Calcio
    FROM (SELECT    Conteo,
            IdPaciente,
            FROM ##PAC_ID_TEMP
            CROSS JOIN ##Numeros 
            WHERE Tabla = 'ANA_BIOQUIMICA'
        ) n
    LEFT JOIN ( SELECT IdPaciente,
                       FechaAnalitica, 
                       GlucosaPre, 
                       Sodio, 
                       Calcio,
                       ROW_NUMBER() (OVER PARTITION BY IdPaciente ORDER BY IdPaciente) AS Conteo
                FROM ANA_BIOQUIMICA
                ORDER BY IdPaciente
        ) A
        ON A.IdPaciente = n.IdPaciente  
        AND A.Conteo = n.Conteo
    ORDER BY n.IdPaciente,  n.Conteo

Conceptually, at least, what we do is generate a table of numbers for each patient that I understand should generate the 44 records for each patient, then you just have to do LEFT to ANA_BIOQUIMICA with ROW_NUMBER() for match with Conteo .