CRN - Max date prompt

[JKas]

Hello,

I have a problem trying to create a membership report. Here is an example of my data:

ID Program Status Expiration_Date
1 W1 A 5/31/06
1 W1 E 5/31/05
2 W1 E 5/04/05
3 W2 E 5/01/05
4 W2 A 5/23/05

My requirements are to only return the members who expire in the current month (May 05) and have NOT renewed. The renewal creates a new record with a future effective date and changes the status of the current record from "E" (expired) to "A" (active).
My expected results would then be:

ID Program Status Expiration_Date
2 W1 E 5/31/05
3 W2 E 5/01/05
4 W2 A 5/23/05


I can get the Max date calculation working fine.
Here are my two questions:

1) How do I create a prompt using the calculation? I want the users to be able to select the range of dates, but only after all max dates have been selected. When I go to create a date prompt, it only allows me to choose from my model and NOT my query (where max calculation is).

2) It seems like this needs to be either multiple queries, joins, or parent child. Can anyone recommend which method would be best in the case?

I'm new to programming and ReportNet. I understand the general concepts behind multiple queries, parent child, etc. I'm struggling in determining which method should be used when.

Thanks in advance.

Jeff

 

[JKas]

ok,

After learning more about ReportNet and SQl, here is my Tabular SQL for the above problem:

select distinct
ADVANCEMENT_PERSON_ORG_ID.ID as ID,
ADVANCEMENT_PERSON_ORG_ID.LAST_NAME as LAST_NAME,
ADVANCEMENT_PERSON_ORG_ID.FIRST_NAME as FIRST_NAME,
MEMBERSHIP.EXPIRATION_DATE as EXPIRATION_DATE,
MEMBERSHIP.MEMBERSHIP_PROGRAM as MEMBERSHIP_PROGRAM,
MEMBERSHIP.MEMBERSHIP_CATEGORY as MEMBERSHIP_CATEGORY,
MEMBERSHIP.MEMBERSHIP_PROGRAM_TYPE as MEMBERSHIP_PROGRAM_TYPE,
MEMBERSHIP.MEMBERSHIP_STATUS as MEMBERSHIP_STATUS
From (ADVANCEMENT_PERSON_ORG_ID
INNER JOIN MEMBERSHIP
ON ADVANCEMENT_PERSON_ORG_ID.ENTITY_UID = MEMBERSHIP.ENTITY_UID)
WHERE MEMBERSHIP.EXPIRATION_DATE=
(SELECT MAX(EXPIRATION_DATE)
from
ODSPP..RAVEN.ADVANCEMENT_PERSON_ORG_ID AS ADVANCEMENT_PERSON_ORG_ID,
ODSPP..RAVEN.MEMBERSHIP AS MEMBERSHIP
where
ADVANCEMENT_PERSON_ORG_ID.ENTITY_UID = MEMBERSHIP.ENTITY_UID)


However, it is not working.

Does anyone have any suggestions?

Thanks

Jeff

 

[Draoued]

Do you need the Status ??

select T1.`ID `, T1.`Program`, max(T1.`Expiration_date`)
from `DATE.csv` T1
group by T1.`ID `, T1.`Program`
having max(T1.`Expiration_date`) between {ts '2005-05-01 00:00:00'} and {ts '2005-05-31 00:00:00'}
order by 1, 3, 2

This returns only the 3 rows you want to see.

 

[JKas]

Draoued,

Thanks but this won't work either. I think you think that the program and expiration date are in T1. They would be in T2. That is why I need the inner join.

Thanks

Jeff