Q:

SQL Code to display data matching on 3 conditions

Need to display the number of days elapsed of patients who were admitted to a skill nursing facility, discharged to their homes (with or without care) and was admitted to a hospital or Emergency Room. 
I was asked to display the number of days elapsed of patients who were admitted to a skill nursing facility, discharged to their homes (with or without care) and was admitted to a hospital or Emergency Room. The metric is to assess the effectiveness of skilled nursing facilities ability to keep patients from being readmitted to hospitals or seen in the emergency room after leaving a nursing facility. 
I have a much larger query that works great using FOLLOWING and PRECEDING that gives me previous facility and next facility admitted, but it doesn't help with the issue above. I know a need another subquery, but the one I have just isolated the latest and earliest. I need it to be rolling and looking for the first instance that meets the condition. I was asked to either give days or insert the date of last admission.
select nursing.*, hospital.*
from
(Select--nursing stays only
SUMMARY.MEMBER_ID
, SUMMARY.POS
, SUMMARY.ADMIT_DATE
, SUMMARY.DISCHARGE_DATE
, SUMMARY.Discharge_To

FROM PRD.SUMMARY

INNER JOIN

(Select 
SUMMARY.MEMBER_ID, MAX(DISCHARGE_DATE) MX_DISCHARGE_DATE

FROM PRD.SUMMARY
WHERE

SUMMARY.DISCHARGE_DATE BETWEEN '2017-01-01' AND '2018-12-31'
  And SUMMARY.POS = 'Nursing' 
group by SUMMARY.MEMBER_ID) sq 

ON sq.MEMBER_ID = SUMMARY.MEMBER_ID and sq.MX_DISCHARGE_DATE = SUMMARY.DISCHARGE_DATE
WHERE

SUMMARY.DISCHARGE_DATE BETWEEN '2017-01-01' AND '2018-12-31'
  And SUMMARY.POS = 'Nursing') nursing 

INNER JOIN

( --hospital stays only
Select
SUMMARY.MEMBER_ID
, SUMMARY.POS
, SUMMARY.ADMIT_DATE
, SUMMARY.DISCHARGE_DATE
, SUMMARY.Discharge_To

FROM PRD.SUMMARY

INNER JOIN (

select MEMBER_ID, MIN(ADMIT_DATE) MIN_ADMIT_DATE

FROM PRD.SUMMARY

WHERE
SUMMARY.DISCHARGE_DATE BETWEEN '2017-01-01' AND '2018-12-31' And SUMMARY.POS = 'Hospital'

GROUP BY SUMMARY.MEMBER_ID) sq 

on sq.MEMBER_ID = SUMMARY.MEMBER_ID and sq.MIN_ADMIT_DATE = SUMMARY.ADMIT_DATE

WHERE
SUMMARY.DISCHARGE_DATE BETWEEN '2017-01-01' AND '2018-12-31' And SUMMARY.POS = 'Hospital')

hospital on nursing.MEMBER_ID = hospital.MEMBER_ID and nursing.DISCHARGE_DATE >= hospital.ADMIT_DATE

DDL to create the above table
CREATE TABLE [dbo].[jpsSUMMARY](
    [member_id] [int] NULL,
    [pos] [nvarchar](10) NULL,
    [admit_date] [date] NULL,
    [discharge_date] [date] NULL,
    [discharge_to] [nvarchar](50) NULL
)
GO

CSV data corresponding to the Expected Output
member_id,pos,admit_date,discharge_date,discharge_to
1001,Nursing   ,2016-03-08,2016-03-14,Home Without Care                                 
1001,Hospital  ,2016-03-21,2016-03-24,Home Without Care                                 
1001,ER        ,2016-03-27,2016-03-28,Hospital                                          
1001,Nursing   ,2016-08-19,2016-09-02,Home Without Care                                 
1001,ER        ,2016-09-05,2016-09-06,Home Without Care                                 

Here is the expected output

A:

I was able to re-constitute that sql, and then prettyprint to look at it.
Try this to see if it is any simpler.  Do all the OVER's once in the CTE...  You may want to remove the ISNULL(CAST(... and accept the results as 0 (zero).
revised May10 noon pos -> [pos]
WITH  SumRN as
(SELECT 
       member_id
      ,[pos]
      ,admit_date
      ,discharge_date
      ,discharge_to
      ,ROW_NUMBER() Over (Partition By member_id order by discharge_date, admit_date) as rn
  FROM PRD.SUMMARY  --jpsSUMMARY
 )

SELECT
     s1.member_id
    ,s1.[pos]
    ,s1.admit_date
    ,s1.discharge_date
    ,s1.discharge_to
    ,ISNULL(sp.[pos],'') as Previous
    ,ISNULL(sf.[pos],'') as Next

    ,ISNULL(cast(case when sp.[pos] = 'Nursing' and sp.discharge_to Like 'Home%' and s1.[pos] = 'Hospital'  
           Then Datediff(d, sp.discharge_date, s1.admit_date) Else null End  as varchar(10)), '') as DaysNursingHosp
    ,ISNULL(cast(case when sp.[pos] = 'Nursing' and sp.discharge_to Like 'Home%' and s1.[pos] = 'ER'        
           Then Datediff(d, sp.discharge_date, s1.admit_date) Else null End  as varchar(10)), '') as DaysNursingER
 From          SumRN s1
 Left Join     SumRN sp
                  on s1.RN - 1 = sp.RN 
 Left Join     SumRn sf
                  on s1.RN + 1 = sf.RN

Results--
member_id   pos admit_date  discharge_date  discharge_to        Previous    Next    DaysN2Hosp  DaysN2ER
1001    Nursing     2016-03-08  2016-03-14  Home Without Care               Hospital        
1001    Hospital    2016-03-21  2016-03-24  Home Without Care   Nursing     ER          7   
1001    ER          2016-03-27  2016-03-28  Hospital            Hospital    Nursing         
1001    Nursing     2016-08-19  2016-09-02  Home Without Care   ER          ER              
1001    ER          2016-09-05  2016-09-06  Home Without Care   Nursing                          3

