Display code active at specific point of time

building semantic layer
1

Hi Guru’s,
I apologise if this is repeated but was unable to find a solution by searching all over the forum.
I wanted to implement in the designer.

SELECT fhp.fund_holder_code
FROM pe,
     fhp
WHERE 
AND pe.reg_gp_practice = fhp.practice_code
AND pe.discharge_date BETWEEN fhp.date_from AND NVL(fhp.date_to, pe.discharge_date)

The motive is to get the list of codes which were active at the respective course of time for each patient.
E.g. if a patient came last year to the doctor and if code A was used then it should display code A but in the patient came second time to the doctor and Code B was prevelant then it should display B.
The above query gives the code which is the correct match but unable to implement it in the Designer.

gurmeet_singh2 :uk: (BOB member since 2007-12-14)

2

Arre you looking to create a derived table?

SELECT fhp.fund_holder_code AS Fund_Holder

You need to explicitly name the field.

dessa :madagascar: (BOB member since 2004-01-29)