Not difficult. One thing you need to do is to look carefully at my code below. You notice I am creating the table, populating it with sample data, and after my query I delete the table used for development. The first 2 items and the last you should provide instead of relying on us to do it for you. So happens, it is a slow day today but don't always count on that.

/*

MEMBID ADMDATE DSCHDATE

06000248 08/02/2010 08/04/2010

06000248 08/10/2010 08/12/2010 (08/10/2010 - 08/04/2010=6)

06000248 08/20/2010 08/22/2010 (08/20/2010 - 08/12/2012=8)

*/

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');

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)

;

GO

DROP TABLE dbo.PatientInfo;

GO