CODEWITHSUNDEEP
mysqlsql
Gulshan
Gulshan

Reputation: 131

How to Use Values from Previous row in a SQL

Following SQL Query gave output as mentioned below:

SELECT claim_id,person_id,service_date,readmission_in_60_days 
FROM xyz WHERE person_id IN("00026cb6","021fb6bd") GROUP BY person_id,service_date
ORDER BY service_date ASC



 claim_id   person_id   service_date  readmission_in_60_days
    8       021fb6bd    2015-01-01             NULL
    304     021fb6bd    2015-01-05             NULL
    296     021fb6bd    2015-01-06             NULL
    11888   021fb6bd    2015-01-07             NULL
    23928   021fb6bd    2015-01-08             NULL
    265442  00026cb6    2016-07-20             NULL
    518062  00026cb6    2016-09-28             NULL
    579739  00026cb6    2016-10-02             NULL

Here I want to check n and n-1 row service_date for a particular person_id.If the difference between n and n-1 service_date is less than 60 days then n row column readmission_in_60_days="TRUE" else "FALSE".

So desired output from SQL query should be:

claim_id   person_id   service_date  readmission_in_60_days
    8       021fb6bd    2015-01-01             FALSE
    304     021fb6bd    2015-01-05             TRUE
    296     021fb6bd    2015-01-06             TRUE
    11888   021fb6bd    2015-01-07             TRUE
    23928   021fb6bd    2015-01-08             TRUE
    265442  00026cb6    2016-07-20             FALSE
    518062  00026cb6    2016-09-28             FALSE
    579739  00026cb6    2016-10-02             TRUE

Upvotes: 3

Views: 112

Answers (3)

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

An alternative is to emulate LAG function in MySQL by means of variables. Something like this:

SET @prevdate = '1900-01-01';
SET @prevperson = "";

SELECT id, personid, servicedate, 
CASE WHEN prevperson = personid THEN
    CASE WHEN datediff(servicedate,prevdate) > 60 THEN FALSE
    ELSE TRUE END
    ELSE FALSE END AS readmission_within_60_days
FROM
(SELECT id, personid, servicedate,
@prevdate prevdate,
@prevperson prevperson,
@prevdate := servicedate as thisdate, 
@prevperson := personid as thisperson FROM xyz
ORDER BY id, personid) t order by id, personid;

Edit

If you cannot use variables, you can get what you want by:

SELECT z.id, 
       z.personid, 
       z.servicedate, 
       CASE WHEN z.MaxPrevDate IS NULL 
           THEN FALSE 
       ELSE 
          CASE WHEN datediff(z.servicedate,z.maxprevdate) > 60 
              THEN FALSE
          ELSE 
              TRUE 
          END 
       END as readmission_within_60_days 
FROM
    (SELECT x.id, x.personid, x.servicedate, 
    (SELECT Max(servicedate) FROM xyz y 
     WHERE y.personid = x.personid AND y.id < x.id) AS maxPrevDate 
     FROM xyz x) z

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use EXISTS and a subquery:

SELECT person_id, service_date,
       (EXISTS (SELECT 1
                FROM xyz xyz2
                WHERE xyz2.person_id = xyz.personid AND
                      xyz2.service_date < xyz.service_date AND
                      xyz2.service_date >= DATE_SUB(xyz.service_date, INTERVAL 60 DAY)
       ) as readmission_in_60_days 
FROM xyz
WHERE person_id IN ('00026cb6', '021fb6bd')
GROUP BY person_id, service_date
ORDER BY service_date ASC

Note: I removed claim_id. If you want to include it in the SELECT then either:

  • Add it to the GROUP BY
  • Use an aggregation function such as MIN(), MAX(), or GROUP_CONCAT().

Upvotes: 4

Dipak Delvadiya
Dipak Delvadiya

Reputation: 2112

Please try this one.

Declare @tblTest as table
(
  claim_id   int,
  person_id  varchar(50),
  service_date  datetime,
  readmission_in_60_days varchar(10) null
)

insert into @tblTest values (304   , '021fb6bd','2015-01-05', NULL)
insert into @tblTest values (296   , '021fb6bd','2015-01-06', NULL)
insert into @tblTest values (11888 , '021fb6bd','2015-01-07', NULL)
insert into @tblTest values (23928 , '021fb6bd','2015-01-08', NULL)
insert into @tblTest values (265442, '00026cb6','2016-07-20', NULL)
insert into @tblTest values (518062, '00026cb6','2016-09-28', NULL)
insert into @tblTest values (579739, '00026cb6','2016-10-02', NULL)

SELECT t.claim_id, 
    t.person_id, 
    t.service_date,
    t.PreviousDate,
    DATEDIFF(DAY, t.service_date, t.PreviousDate) AS TotalDiffDays,
    CASE WHEN ISNULL(t.PreviousDate,'') = '' THEN 'FALSE' 
         WHEN DATEDIFF(DAY, t.PreviousDate, t.service_date) > 60 THEN 'FALSE'
         WHEN DATEDIFF(DAY, t.PreviousDate, t.service_date) < 60 THEN 'TRUE' 
         END AS readmission_in_60_days
FROM
    (SELECT 
        claim_id, 
        person_id, 
        service_date,
        LAG(service_date, 1, null) OVER (ORDER BY claim_id ASC) AS PreviousDate from @tblTest
    ) t

Upvotes: 5

Related Questions