CODEWITHSUNDEEP
t-sql
stackuser
stackuser

Reputation: 672

How to find the difference in dates in two rows

In the data below, resno is the resident number. I am trying to find the count of residents that have an 'R' in the top row (when the data is sorted by 'Sort' desc) and the difference between the dates in Row 1 and Row 2 is 30 days.

cono    resno      date                         seq    sort     admit_disch
140         9423    2013-12-12 00:00:00.000      255003 255003  R
140         9423    2013-11-17 00:00:00.000      255002 255002  D
140         9423    2013-11-12 00:00:00.000      255001 255001  R
140         9423    2011-06-06 00:00:00.000      255000 255000  D
140         9423    2011-05-18 00:00:00.000      254999 254999  A

140         9604    2012-02-20 00:00:00.000      255548 255548  A

I am able to write a query to get the 'R' residents, but not sure how to check the dates on two different rows. In the data above, it must check if the date 2013-12-12 is greater than 2013-11-17 by 30 days or more.

 select cono,COUNT(resno) from
    (
      select ROW_NUMBER() over(partition by resno rsa.sort desc) rn,*
      from ri_status_admit rsa 
    )B
    where B.rn = 1
   and B.admit_disch = 'R'

      group by cono,resno

Upvotes: 1

Views: 79

Answers (1)

paparazzo
paparazzo

Reputation: 45096

with sorted as 
    (
      select ROW_NUMBER() over(partition by resno rsa.sort desc) rn,*
      from ri_status_admit rsa 
    )
select s1.resno 
  from sorted s1 
  join sorted s2
    on s1.rn = 1 
   and s1.admit_disch = 'R' 
   and s2.rn = 2 
   and datediff(dd,r1.date, r2.date) > 30

Not tested I suspect some syntax errors but the is the basic idea

Upvotes: 1

Related Questions