Create a calendar table first, if you don't have one already like this: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);
Now you can query like shown below - I am just creating a test table, you wouldn't need to do that, you would simply use your actual claims table in place of the temp table.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 ClaimsFROM #Claims a INNER JOIN #Calendar b ON a.From_DOS <= b.Dt AND a.To_DOS >= b.DtGROUP BY ClaimNo; DROP TABLE #Claims