| DENSE RANK / FIRST_VALUE help [message #207225] |
Mon, 04 December 2006 13:50  |
staann56
Messages: 136
Registered: May 2006
Location: atlanta
|
Senior Member |
|
|
I'm trying to pull the first and last charting dates for the RESULT_VALUE field using the DENSE RANK or FIRST_VALUE/LAST_VALUE functions with no success. Can somebody please tell me where I'm going wrong.
Here's my code:
SELECT DISTINCT
p.name
p.pid,
pr.PS,
FIRST_VALUE(DDT.TOCHAR(pr.perform,'MM/DD/YYYY HH24:MI')) OVER ( ORDER BY pr.PS) First_Chart,
pr.result_value
FROM
pat_results pr,
pcq_label pcql,
patient p
WHERE
pr.PS = p.PS
AND pcql.label = pr.label
AND pcql.name IN ('Risk')
(I've also tried this line instead of FIRST_VALUE
MIN(pr.result_value) KEEP (DENSE_RANK LAST ORDER BY pr.perform) OVER (PARTITION BY p.PS) AS First_Charted)
I'm getting the following output with the date being correct but listed for each RESULT_VALUE:
Name PID DEPT FIRST_CHART RESULT_VALUE
TEST,TEST 1234 B 11/27/2006 19
TEST,TEST 1234 B 11/27/2006 20
TEST,TEST 1234 B 11/27/2006 21
TEST,TEST 1234 B 11/27/2006 22
TEST,TEST 1234 B 11/27/2006 23
But I would like the result set to be like so:
Name PID DEPT LAST_CHART RESULT_VALUE
TEST,TEST 1234 B 11/27/2006 19
TEST,TEST 1234 B 12/01/2006 23 (for LAST_VALUE)
Thanks,
Stan
|
|
|
|
| Re: DENSE RANK / FIRST_VALUE help [message #207254 is a reply to message #207225] |
Mon, 04 December 2006 19:57  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If I understand right, you want the minimum and maximum dates for each combination of Name, PID, and DEPT, and then you want the result_value that corresponds to those min and max rows.
If you are happy for it to come out on one row instead of two...
SELECT
p.name
p.pid,
pr.PS,
DDT.TOCHAR(MIN(perform),'MM/DD/YYYY HH24:MI') First_Chart,
MIN(pr.result_value) KEEP (
DENSE_RANK FIRST ORDER BY pr.perform) First_Result,
DDT.TOCHAR(MAX(perform),'MM/DD/YYYY HH24:MI') Last_Chart,
MIN(pr.result_value) KEEP (
DENSE_RANK LAST ORDER BY pr.perform) Last_Result
FROM
pat_results pr,
pcq_label pcql,
patient p
WHERE
pr.PS = p.PS
AND pcql.label = pr.label
AND pcql.name IN ('Risk')
GROUP BY
p.name
p.pid,
pr.PS
You could convert it to two rows by joining to a row generator (search the board). It is possible to do it with analytic functions, but I don't like them because they are not very efficient, and they just seem wrong when you you really want to do is aggregate.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
