OraFAQ Forum: Reports & Discoverer » Count

Home » Developer & Programmer » Reports & Discoverer » Count_Distinct in Series

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count_Distinct in Series [message #285805]

Wed, 05 December 2007 12:52

jfrano
Messages: 11
Registered: January 2007
Location: New York

Junior Member

I'm doing a Discoverer report, where I'm trying to Count_Distinct(PatientNum) the number of Pateints had a chest Xray with in each Quarter/Year.

What I get is the following
ie
Calc
Count
Dist(PatientID) Quarter Year
195 Q2 2007
212 Q3 2007
278 Q4 2007

Within each Quarter, the Count Distinct works fine. However I only want the Patient ID to be counted in the FIRST Quarter that a Xray occurred. How might I achieve this?

Thanks

[Updated on: Wed, 05 December 2007 13:03]

Report message to a moderator

Re: Count_Distinct in Series [message #286101 is a reply to message #285805]

Thu, 06 December 2007 08:20

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

That can be done in two steps:
- determine which is the first quarter per patient
- count that

First quarter: use a analytical function (in calculations), like:
min(quarter) over (partition by patient order by quarter)
(please pay attention to the sort of the quarter, ie should be something like 200701, 200702, etc.)
For details, lookup analytical functions in the sql reference.

Then you get something like:

patient first_quarter
123     200702
124     200702
234     200701
234     200703

And do a count on that:

count(patient) quarter
1              200701
2              200702
1              200703

Report message to a moderator

Re: Count_Distinct in Series [message #286949 is a reply to message #285805]

Mon, 10 December 2007 11:58

jfrano
Messages: 11
Registered: January 2007
Location: New York

Junior Member

Thanks for the reply, one issue is that the Quarter and Year are seperate elements.

[Updated on: Mon, 10 December 2007 12:15]

Report message to a moderator

Re: Count_Distinct in Series [message #286979 is a reply to message #286949]

Mon, 10 December 2007 15:00

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Perhaps you can make one item of it, something like this:

to_number(year||quarter)

(if there are any "spare" characters like "quarter 1" or so, that first strip that of, for example using substr)

Report message to a moderator

Re: Count_Distinct in Series [message #287249 is a reply to message #286101]

Tue, 11 December 2007 11:05

jfrano
Messages: 11
Registered: January 2007
Location: New York

Junior Member

skooman wrote on Thu, 06 December 2007 09:20

patient first_quarter
123     200702
124     200702
234     200701
234     200703

And do a count on that:

count(patient) quarter
1              200701
2              200702
1              200703

That would still seem to be counting patinet 234 twice , I would have expected

1 200701
2 200702

Report message to a moderator

Re: Count_Distinct in Series [message #287494 is a reply to message #287249]

Wed, 12 December 2007 06:49

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Sorry, patient 234 should have been in the example table (first one) only once. If you have coded the lag/lead stuff correctly, it will.

Report message to a moderator

Previous Topic:	Object Liberary of Developer 6 to 6i
Next Topic:	Adding an item

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:32:50 CST 2024