Find dates closer to a given date

Question

Find dates closer to a given date

Navigation

#1 by (1 votes)

1

Good morning.

I try to recover, based on a group of dates given by a user, the dates of a table that most closely match the dates given.

The test data and tables are like this:

create table PAC_ID_T
(
  IdPaciente int,
  NHC varchar (10)
);

insert into PAC_ID_T (IdPaciente,NHC)
values (3000,'100111');

create table Datos$
(
  NHC Varchar (10),
  Fecha date
);

insert into Datos$ (NHC, Fecha) values
( '100111' , '2013-07-24' ),
('100111' , '2012-08-01' );


create table SIGNOSVITALES_T
(
  IdPaciente int,
  FechaVisita date
);


insert into SIGNOSVITALES_T (IdPaciente, FechaVisita)
select IdPaciente, FechaVisita from
(
              select   3000  IdPaciente , '2011-01-03' FechaVisita
    union all select   3000  IdPaciente , '2011-01-06' 
    union all select   3000  IdPaciente , '2012-07-24' 
    union all select   3000  IdPaciente , '2012-07-26' 
    union all select   3000  IdPaciente , '2012-07-31' 
    union all select   3000  IdPaciente , '2013-08-01' 
    union all select   3000  IdPaciente , '2013-05-22' 

)t

At the moment I have this script, but I can only retrieve one of the dates (Test the script) :

select   a.NHC,  max(date_format(a.FechaVisita,'%d/%m/%Y')) FechaVisita
from
(
    select p.IdPaciente, P.NHC, a.FechaVisita, timestampdiff  (day , a.FechaVisita ,  h.Fecha) DiasCercanos
    from Datos$ H
    left join PAC_ID_T p
    on h.NHC = p.NHC
    left join SIGNOSVITALES_T a 
    on p.IdPaciente = a.IdPaciente 
    where  timestampdiff  (day , a.FechaVisita ,  h.Fecha) > 0 

)a
left join 
(
    select distinct  H.NHC ,  a.FechaVisita ,   min(timestampdiff  (day , a.FechaVisita ,  H.Fecha ) )DiasCercanos
    from Datos$ H
    left join PAC_ID_T p
    on h.NHC = p.NHC
    left join SIGNOSVITALES_T a
    on p.IdPaciente = a.IdPaciente
    where a.IdPaciente is not null 
    and A.FechaVisita <= H.Fecha  
    group by   H.NHC,a.FechaVisita
)f
on a.NHC = f.NHC and a.DiasCercanos = f.DiasCercanos
where f.NHC is not null
group by a.NHC
ORDER BY a.NHC ASC

In this case, the dates given are reflected in the table Data $, in this table there are two dates:

and the table where I need to find the dates closest to this is the table SIGNOSVITALES_T, this has the following dates:

So, I need to recover the two dates closest to those defined in the table Data $, in this case they would be:

2012-07-31 (It is the only one that returns so far) 2013-05-22 (This is the second date that I need to return, it must be a date less than that indicated in the table Data $)

What do I need?

sql sql-server

asked by Emerson Rios 23.05.2018 в 19:18

source

1 answer

Obtain dependencies of a view How to make reference in package.json to another module of my project?

score 1 · Accepted Answer

;WITH CTE AS (
    SELECT  S.IdPaciente, 
        S.FechaVisita, 
        F.Fecha,
        DATEDIFF(DAY, S.FechaVisita, F.Fecha) AS 'DifDias',
        ROW_NUMBER() OVER (PARTITION BY F.Fecha ORDER BY DATEDIFF(DAY, S.FechaVisita, F.Fecha)) AS 'RN'
        FROM SIGNOSVITALES_T S
        LEFT JOIN (SELECT   P.IdPaciente,
                    D.Fecha
                    FROM PAC_ID_T  P
                    INNER JOIN Datos$ D
                        ON P.NHC =  D.NHC
            ) F
            ON F.IdPaciente = S.IdPaciente
        WHERE DATEDIFF(DAY, S.FechaVisita, F.Fecha) > 0
)
SELECT  IdPaciente,
    Fecha,
    FechaVisita
    FROM CTE
    WHERE RN = 1

Exit:

| IdPaciente |      Fecha | FechaVisita |
|------------|------------|-------------|
|       3000 | 2012-08-01 |  2012-07-31 |
|       3000 | 2013-07-24 |  2013-05-22 |

SQL Fiddle: link

Detail:

Basically we combine each row of SIGNOSVITALES_T with the two corresponding to the patient in Datos$
Then, we look for the difference in days between FechaVisita and each Fecha , we discard the FechaVisita that were later than Fecha
We order the difference in days from least to greatest and for each group of Fecha , with ROW_NUMBER() we generate a row number
Finally, we consult the generated table and only list the rows number 1 that will be the closest dates.