none
Get data based on date conditions RRS feed

  • Question

  • Hi Friends,

    I have a table say Patient.

    The first the patient comes, i enter patient details in it. updated date[the date patient comes] is recorded whenever details are entered.


    The same patient comes 2nd time, i enter patient details in it(some details updated). updated date is recorded whenever details are entered.

    This can happen n number of times with n no. of patients.

    sample data

    patientid            updated_date       xyz details

    123                       14 may 2020            fever

    123                        10 june  2019          cold

    123                       2 may 2019              body pains

    123                       1 march 2019           headache   

    456                       12  jan    2020          asfd

    456                       31 dec    2019           asfd

    456                        01 jan 2019             dfgg

    Now for every patientid, latest/top 1 row  should be compared with its preceeding row on updatedon date and check following condition -

    latest row.updatedon should be between  precceding row.updatedon and precceding row.updated on +365 days.

    Required Data:

    patientid            updated_date       xyz details

    123                       14 may 2020            fever

    456                       12  jan    2020          asfd



    sania

    Friday, June 14, 2019 1:21 PM

All replies

  • Here is the query, hope this is what you are looking for:

    CREATE TABLE patient_visit
    (patientid int,updated_date date,dtls varchar(10));
    
    INSERT INTO dbo.patient_visit
    (
        patientid,
        updated_date,
        dtls
    )
    VALUES
    (123,'20200514','fever'),   
    (123,'20190610','cold'),
    (123,'20190502','body pains'),
    (123,'20190301','headache'),
    (456,'20200112','asfd'),
    (456,'20191231','asfd'),
    (456,'20190101','dfgg');
    
    WITH cte
    AS (
        SELECT *,
            CASE 
                WHEN updated_date 
                    BETWEEN lead(updated_date) OVER (PARTITION BY patientid ORDER BY updated_date DESC)
                        AND dateadd(day, 365, lead(updated_date) OVER (PARTITION BY patientid ORDER BY updated_date DESC))
                    THEN 1 ELSE 0 END is_valid,
            row_number() OVER (PARTITION BY patientid ORDER BY updated_date DESC) seq
        FROM dbo.patient_visit
        )
    SELECT patientid,updated_date,dtls
    FROM cte
    WHERE seq = 1 AND is_valid = 1;

    Output:


    Friday, June 14, 2019 7:11 PM
  • Hi SFatimi,

    As per your requirement, I think you would like to select the latest updated row which have the duration of less than 365 days from the previous date. Right?

    If my understanding is correct, here is my script:

    IF OBJECT_ID('Patient') IS NOT NULL drop table Patient
    go 
    create table Patient
    (patientid int not null,
    updated_date date not null,
    xyz_details varchar(20) not null)
    
    insert into Patient values 
    (123,'14 may 2020','fever'),
    (123,'10 june  2019','cold'),
    (123,'2 may 2019','body pains'),
    (123,'1 march 2019','headache'),  
    (456,'12 jan 2020','asfd'),
    (456,'31 dec 2019','asfd'),
    (456,'01 jan 2019','dfgg')
    
    ;with cte as (
    select *,
    max(updated_date)over(partition by patientid)max,
    DATEDIFF(day,updated_date,max(updated_date)over(partition by patientid))  AS difference,
    DENSE_RANK() over(partition by patientid order by updated_date desc) no
    from patient),
    cte2 as ( 
    select * ,lead(difference) over (partition by patientid order by updated_date desc) dif2 from cte)
    
    select patientid,updated_date,xyz_details from cte2
    where no=1 and dif2<365
    
    /*
    patientid   updated_date xyz_details
    ----------- ------------ -----------
    123         2020-05-14   fever
    456         2020-01-12   asfd
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, June 17, 2019 6:07 AM
  • Hi Sfatimi,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 12, 2019 7:57 AM