where tbl1.er_date between '1/1/2012' and '1/31/2012'

IF OBJECT_ID('tempdb..#Claims') IS NOT NULL
  DROP TABLE #Claims
IF OBJECT_ID('tempdb..#ClaimSpans') IS NOT NULL
  DROP TABLE #ClaimSpans

CREATE TABLE #Claims (ClaimID int IDENTITY(1,1) PRIMARY KEY, PCN varchar(10), Admit date, Discharge date)

INSERT INTO #Claims (PCN, Admit, Discharge)
  VALUES 
    ('000000000A', '2012-01-01', '2012-01-03')
    , ('000000000A', '2012-01-05', '2012-01-09')
    , ('000000000A', '2012-01-10', '2012-01-11')
    , ('000000000A', '2012-01-12', '2012-01-12')
    , ('000000000A', '2012-01-14', '2012-01-19')
    , ('000000000A', '2012-01-20', '2012-01-31')
    , ('000000000B', '2012-01-05', '2012-01-09')
    , ('000000000B', '2012-01-10', '2012-01-11')
    , ('000000000B', '2012-02-05', '2012-02-09')
    , ('000000000B', '2012-02-10', '2012-02-11')
    , ('000000000B', '2012-02-12', '2012-02-14')
    , ('000000000B', '2012-02-15', '2012-02-16')
    , ('000000000B', '2012-02-27', '2012-02-28')
    , ('000000000B', '2012-03-9', '2012-03-11')
    , ('000000000C', '2012-03-05', '2012-03-09')
    , ('000000000D', '2012-04-10', '2012-04-11')

;WITH Buckets (RowNum, PCN, Yr, Mnth, Admit) as
(
	SELECT 
		ROW_NUMBER() OVER (PARTITION BY PCN ORDER BY PCN, Admit) as RowNum,
		PCN,
		YEAR(Admit) as Yr,
		MONTH(Admit) as Mnth,
		Admit
	FROM #Claims
	GROUP BY PCN, YEAR(Admit), MONTH(Admit), Admit
)
SELECT z.PCN, z.Yr, z.Mnth, z.Admit, y.Admit as PrevAdmit, DATEDIFF(d, y.Admit, z.Admit) as Days, z.RowNum, y.RowNum as PrevRowNum
INTO #ClaimSpans
FROM Buckets as z
LEFT JOIN Buckets as y
	ON z.RowNum - 1 = y.RowNum AND z.PCN = y.PCN

SELECT * FROM #ClaimSpans

-- find claims for patients that came back between 2-4 days
SELECT * FROM #ClaimSpans WHERE Days BETWEEN 2 and 4

-- find the count of patients that started in one group below and either stayed within the same group or moved to another span
;WITH Buckets (PCN, Bucket, Days, OldRowNum, RowNum)
as
(
SELECT
	PCN,
	CASE
		WHEN Days BETWEEN 0 AND 1 THEN '0 to 1'
		WHEN Days BETWEEN 2 AND 4 THEN '2 to 4'
		WHEN Days BETWEEN 4 AND 7 THEN '4 to 7'
		WHEN Days BETWEEN 8 AND 10 THEN '8 to 10'
		WHEN Days BETWEEN 11 AND 14 THEN '11 to 14'
		ELSE '15 to 20' END as Bucket,
	Days,
	RowNum as OldRowNum, 
	ROW_NUMBER() OVER (PARTITION BY PCN ORDER BY PCN, RowNum) as RowNum -- recalc RowNum
FROM #ClaimSpans
WHERE RowNum > 1 -- first claim won't join to a previous claim
  AND Days <= 20 -- claims over 20 days won't count
)
SELECT
	r.FromBucket,
	r.ToBucket,
	COUNT(*) as Cnt
FROM
(
	SELECT
		a.Bucket as FromBucket,
		b.Bucket as ToBucket
	FROM Buckets as a
	JOIN Buckets as b
		ON a.PCN = b.PCN AND a.RowNum = b.RowNum - 1 AND a.OldRowNum = b.OldRowNum - 1 -- OldRowNum makes sure that there wasn't a break in the sequence, like when the 20+ days were pulled
) as r		
GROUP BY FromBucket, ToBucket

-- clean up
IF OBJECT_ID('tempdb..#Claims') IS NOT NULL
  DROP TABLE #Claims
IF OBJECT_ID('tempdb..#ClaimSpans') IS NOT NULL
  DROP TABLE #ClaimSpans