Home » Developer & Programmer » Reports & Discoverer » Count_Distinct in Series
Count_Distinct in Series Wed, 05 December 2007 12:52
 jfrano Messages: 11Registered: 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: 913Registered: 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```
Re: Count_Distinct in Series [message #286949 is a reply to message #285805] Mon, 10 December 2007 11:58
 jfrano Messages: 11Registered: 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: 913Registered: 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)
Re: Count_Distinct in Series [message #287249 is a reply to message #286101] Tue, 11 December 2007 11:05
 jfrano Messages: 11Registered: 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

Re: Count_Distinct in Series [message #287494 is a reply to message #287249] Wed, 12 December 2007 06:49
 skooman Messages: 913Registered: 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.
 Previous Topic: Object Liberary of Developer 6 to 6i Next Topic: Adding an item
Goto Forum:

Current Time: Sat Jul 22 17:47:32 CDT 2017

Total time taken to generate the page: 0.10234 seconds