CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);
;WITH cte AS
(
	SELECT CAST('20130101' AS DATETIME) AS c 
	UNION ALL
	SELECT DATEADD(dd,1,c) FROM cte 
	WHERE c < '20131231'
)
INSERT INTO #Calendar SELECT c FROM cte OPTION  (MAXRECURSION 0);

CREATE TABLE #Claims (ClaimNo INT, From_DOS DATETIME, To_DOS DATETIME);
INSERT INTO #Claims VALUES 
(12345,'20130101','20130103'),(12345,'20130104','20130106')

SELECT
	ClaimNo,
	COUNT(DISTINCT dt) AS Claims
FROM
	#Claims a
	INNER JOIN #Calendar b ON 
		a.From_DOS <= b.Dt AND a.To_DOS >= b.Dt
GROUP BY
	ClaimNo;		

DROP TABLE #Claims

select [Claim Number], Visits = sum(datediff(day, [From DOS], [TO DOS]))
from   claims
group by [Claim Number]

quote:

---

Originally posted by khtan

select [Claim Number], Visits = sum(1+ datediff(day, [From DOS], [TO DOS]))
from   claims
group by [Claim Number]

---

[font=Comic Sans MS][size=1]KH

---

quote:

---

Correct.

But each distinct DOS per claim is only counted once.

So if

12345|1/1/13|1/1/13| >> 1 days = 1 visits
12345|1/1/13|1/1/13| >> 0 (already counted once)

---