Here is the updated code:

CREATE TABLE dbo.PatientInfo (

MEMBID CHAR(8),

ADMDATE DATE,

DSCHDATE DATE

);

go

INSERT INTO dbo.PatientInfo

VALUES ('06000248','08/02/2010','08/04/2010'),

('06000248','08/10/2010','08/12/2010'),

('06000248','08/20/2010','08/22/2010'),

('06000348','08/02/2011','08/04/2011'),

('06000348','08/10/2011','08/12/2011'),

('06000348','08/20/2011','08/22/2011');

GO

WITH BaseData AS (

SELECT

ROW_NUMBER() OVER (PARTITION BY MEMBID ORDER BY DSCHDATE) rn,

MEMBID,

ADMDATE,

DSCHDATE

FROM

dbo.PatientInfo

)

SELECT

bd1.MEMBID,

bd1.ADMDATE,

bd1.DSCHDATE,

ISNULL(DATEDIFF(dd, bd2.DSCHDATE, bd1.ADMDATE),0) DaysOut

FROM

BaseData bd1

LEFT OUTER JOIN BaseData bd2

ON (bd1.rn = bd2.rn + 1

AND bd1.MEMBID = bd2.MEMBID)

;

GO

DROP TABLE dbo.PatientInfo;

GO