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;