My friend gave me an interesting problem yesterday. He works for a hospital and needs to count patient visits, and if a patient visits the doctor on consecutive days, it needs to be counted as 1 visit. Here is how i solved it. This is just one of many ways to do it.
Counting distinct periods of consecutive days
DECLARE @tempTable TABLE(row_id INT IDENTITY(1,1),
patient_id INT,
er_date DATETIME)
-- Test Data
INSERT INTO @tempTable(patient_id,er_date)
SELECT 1,'1/1/2012'
UNION ALL
SELECT 1,'1/2/2012'
UNION ALL
SELECT 1,'1/3/2012'
UNION ALL
SELECT 2,'1/5/2012'
UNION ALL
SELECT 2,'1/6/2012'
UNION ALL
SELECT 3,'1/8/2012'
UNION ALL
SELECT 3,'1/9/2012'
UNION ALL
SELECT 1,'2/1/2012'
UNION ALL
SELECT 1,'2/2/2012'
UNION ALL
SELECT 2,'2/3/2012'
UNION ALL
SELECT 2,'3/3/3012'
UNION ALL
SELECT 3,'1/10/2012'
UNION ALL
SELECT 3,'1/13/2012'
UNION ALL
SELECT 5,'1/5/2012'
--If datediff is only 1 , it is treated as same visit
SELECT
tbl1.patient_id,
SUM( CASE WHEN tbl1.er_date IS NOT NULL AND tbl2.er_date IS NULL THEN 1 ELSE 0 END ) [number_of_visits]
FROM
@tempTable tbl1
LEFT JOIN @tempTable tbl2
ON tbl1.patient_id = tbl2.patient_id
AND tbl1.er_date = DATEADD(dd,1,tbl2.er_date)
GROUP BY
tbl1.patient_id;