InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 01:23:18 GMT20http://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspxselect icn, DateDiff(dd,min(fromdt),isNull(max(todt),GETDATE())) daycnt from icnsgroup by icnFri, 22 Feb 2013 19:09:33 GMTlnardozi 61862http://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspx[quote][b]montecarlo2079 (2/22/2013)[/b][hr]The table has 4 columnsICN, From Date of service, To date of service, and detail status code123,1/1/12 ,1/5/12 ,P123,1/6/12 ,1/6/12 ,p123,1/1/12 ,1/10/12 ,pFor each ICn I need to count only the distinct days per icn. in this example above, the answer would be 10. I dont want to count each day, but want the count of distinct days per ICN.does that help?[/quote]Not really. We don't know what the datatyes are, etc.Fri, 22 Feb 2013 13:54:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspx[quote]is there something in the join that could be counting these records out?[/quote]Maybe in some cases "s.[to Date of service]" is NULL to indicate "no end date"?Fri, 22 Feb 2013 11:33:32 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspxThe table has 4 columnsICN, From Date of service, To date of service, and detail status code123,1/1/12 ,1/5/12 ,P123,1/6/12 ,1/6/12 ,p123,1/1/12 ,1/10/12 ,pFor each ICn I need to count only the distinct days per icn. in this example above, the answer would be 10. I dont want to count each day, but want the count of distinct days per ICN.does that help?Fri, 22 Feb 2013 06:39:34 GMTmontecarlo2079http://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspxWe have no idea what data you have in your tables, so only the guess:You join on dates, so it may be that some dates in your expected data are such that they are not covered by the join.Fri, 22 Feb 2013 05:28:47 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1422774-391-1.aspxHi,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 UNION ALLSELECT DATEADD(dd,1,c) FROM cte WHERE c < '20131231')INSERT INTO #Calendar2 SELECT c FROM cte OPTION (MAXRECURSION 0);SELECTICN,COUNT(DISTINCT Dt) AS DaysFROMAllOPBYDOS sINNER JOIN #Calendar2 c ONs.[from Date of service] >= c.Dt AND s.[to Date of service] < c.Dt+1Where [Detail Status Code] = 'p'GROUP BYICN;Thu, 21 Feb 2013 13:23:08 GMTmontecarlo2079