Rows being dropped in query

Question

Post reply

Rows being dropped in query

montecarlo2079

SSC-Addicted

Points: 403
More actions
February 21, 2013 at 1:23 pm

#270772

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;

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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]

montecarlo2079 SSC-Addicted Points: 403 More actions · Answer 2

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?

ScottPletcher SSC Guru Points: 101105 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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)

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 5

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

group by icn