Most recent start date - derived table vs. analytic function

building semantic layer
1

I am trying to get the most recent start date but I can’t seem to get exactly what I want. I have a couple of scenarios I am trying to get the most recent start date for:

(A)For this I would just want the 2/8/2009 5:36 record
PAT_SEQ=123
CARE_RELN_CODE=ATT ( which I use in the filter already)
2/5/2009 12:00 AM
2/5/2009 5:00 PM
2/8/2009 5:36

(B)For this I would just want the 2/10/2009 2:00 record
PAT_SEQ=345
CARE_RELN_CODE=ATT ( which I use in the filter already)
2/10/2009 2:00

Create an object in the universe(measure):
max(physicians_STAFF_ASSIGN.START_DT)
does not work with the flag
When I try and use in a subquery I get the error “Group function is not allowed here”

Create an object in the universe:
MAX(physicians_STAFF_ASSIGN.START_DT) OVER (PARTITION BY (physicians_STAFF_ASSIGN.CARE_RELN_CODE))
This one appears to work when I use this in the as a Result Object in my report is filtered for just 1 PAT_SEQ

and works for both multiple records(A) and single records(B) when I create a variable as follows:
=If([Start Date/Time]=[Max Start Date/Time Test 2];1;0)
However, when I have multiple PAT_SEQ’s it does not return records that have just one entry as in (B)
When I try and use in a subquery I get the error “Group function is not allowed here”

Create an object in the universe:
MAX(physicians_STAFF_ASSIGN.START_DT) OVER (PARTITION BY
(physicians_STAFF_ASSIGN.PAT_SEQ),(physicians_STAFF_ASSIGN.CARE_RELN_CODE),(physicians_STAFF_ASSIGN.START_DT))
Does not work with flag
When I try and use in a subquery I get the error “Group function is not allowed here”

Create a derived table:
SELECT start_dt
FROM (
SELECT MAX(start_dt) OVER (PARTITION BY care_reln_code) as max_start_dt,start_dt
FROM staff_assign
) WHERE start_dt=max_start_dt
and then an object from the derived table:
“STAFF_ASSIGN_Derived_Table”.START_DT
Does not work with flag
Does not error but does not return the correct data in subquery. Does not return the single records as in (B) above and also as in (A) above it returns the earliest date on 2/4/2009 12:00 AM and 2/8/2009 5:36 record.

Using a Database Ranking also does not work. It skips the single records(B).
This is my first attempt at Derived tables and analytic functions so I must be doing something wrong! Thanks!!!

rdurbin9 (BOB member since 2008-04-03)

2

Can you try creating the date object using this code and see if it works ?

MAX(physicians_STAFF_ASSIGN.START_DT) OVER (PARTITION BY 
physicians_STAFF_ASSIGN.PAT_SEQ, physicians_STAFF_ASSIGN.CARE_RELN_CODE)

AnthonyJ :philippines: (BOB member since 2006-06-30)

3

No good but kind of weird too! When I use it for a single PAT_SEQ as a Result Object and using the flag it returns the (B) record okay but returns all 3 (A) records. When I try to use with multiple PAT_SEQ and in a subuqery I get the Group error.

rdurbin9 (BOB member since 2008-04-03)

4

I did some more research and DENSE_RANK appears to work:
DENSE_RANK() OVER( PARTITION BY pat_PATIENT_ALT_ID_an.ID ORDER BY max(physicians_STAFF_ASSIGN.START_DT) DESC ) AS Rk__1
along with a variable set as a flag and in the report filter where variable=1.
However, I am section and then breaking within the section. My totals are good for the break and section but my final total, all sections totalled, are higher as if it is counting the records where the rank is greater than 1. Is this normal?

rdurbin9 (BOB member since 2008-04-03)

5

Sorry, I did not create a variable. I used the DENSE_RANK object I created=1 in the report filter. Thanks!

rdurbin9 (BOB member since 2008-04-03)

6

To me it appears that the report filter is just filtering what is displayed bu the total, charges in this case, is totaling all records returned. I’ll have to figure out a way around this. Suggestions are always welcome! Thanks!

rdurbin9 (BOB member since 2008-04-03)

7

I got it. Sorry for talking out loud on the board as I worked through this. Thanks!

rdurbin9 (BOB member since 2008-04-03)