I am trying to count distinct days for medical claims using the icn, from date of service and to date of service.
Im using the sql below to create a temp table/calendar.
When the select statement runs, it omits about 39 icns in my list. is there something in the join that could be counting these records out?
CREATE TABLE #Calendar2(Dt DATETIME NOT NULL PRIMARY KEY);
;WITH cte AS
SELECT CAST('20120101' AS DATETIME) AS c
SELECT DATEADD(dd,1,c) FROM cte
WHERE c < '20131231'
INSERT INTO #Calendar2 SELECT c FROM cte OPTION (MAXRECURSION 0);
COUNT(DISTINCT Dt) AS Days
INNER JOIN #Calendar2 c ON
s.[from Date of service] >= c.Dt AND s.[to Date of service] < c.Dt+1
Where [Detail Status Code] = 'p'