Rows being dropped in query

  • Hi,

    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 ALL

    SELECT DATEADD(dd,1,c) FROM cte

    WHERE c < '20131231'

    )

    INSERT INTO #Calendar2 SELECT c FROM cte OPTION (MAXRECURSION 0);

    SELECT

    ICN,

    COUNT(DISTINCT Dt) AS Days

    FROM

    AllOPBYDOS s

    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'

    GROUP BY

    ICN;

  • We 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.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • The table has 4 columns

    ICN, From Date of service, To date of service, and detail status code

    123,1/1/12 ,1/5/12 ,P

    123,1/6/12 ,1/6/12 ,p

    123,1/1/12 ,1/10/12 ,p

    For 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?

  • is there something in the join that could be counting these records out?

    Maybe in some cases "s.[to Date of service]" is NULL to indicate "no end date"?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • montecarlo2079 (2/22/2013)


    The table has 4 columns

    ICN, From Date of service, To date of service, and detail status code

    123,1/1/12 ,1/5/12 ,P

    123,1/6/12 ,1/6/12 ,p

    123,1/1/12 ,1/10/12 ,p

    For 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?

    Not really. We don't know what the datatyes are, etc.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • select icn, DateDiff(dd,min(fromdt),isNull(max(todt),GETDATE())) daycnt from icns

    group by icn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply