I am trying to write a report which shows only clients who have never been referred before and whose first referral is between two dates (e.g. April to May). I am completly stuck can anyone help??
The database contains basically the following Info relevant to the query:
Client ID, Referral date, referral reason, referral ID.
If client A was refered in January and again in April he should not be in the report.
If client B was referred in April only he should be in the report.
If client C was referred in April and again in May he should be in the report.
If client D was referred in January then in February and again in May he should not be in the report.
Note some clients may have a dozen or more referrals one may be in April and the others in earlier months or none of the referrals may be in the relevant period.
Select ClientID, ClientName
From Table
WHERE ReferalDate Between April 2004 and May 2004
MINUS
Select ClientID, ClientName
From Table
WHERE NOT (ReferalDate Between April 2004 and May 2004)
You should be able to use the MINUS operator when building your data provider. Depending on your real requirements your data provider/query might be more complex or involve a subquery etc.
Another option, based on the pseudo code above might be
Select ClientID, ClientName
From Table
WHERE ReferalDate Between April 2004 and May 2004
MINUS
Select ClientID, ClientName
From Table
WHERE ReferalDate < April 2004
The minus operator would clear out clients whose first referal date was before April 2004
This doesn’t seem to work as I want it to It brings in people who have had a referral in the time period I am looking at who also have previous referral(s) at earlier date(s). I am not very familiar with sql.
Select DISTINCT ClientID, ClientName
From Table
WHERE ReferalDate Between April 2004 and May 2004
MINUS
Select DISTINCT ClientID, ClientName
From Table
WHERE ReferalDate < April 2004
The distincts will de-dupe those cases of someone having two or more referrals in the time period.
Are you testing the code in TOAD (or equivalent) or directly in BusinessObjects?
Give this a try:
Select DISTINCT ClientID, ClientName
From Table
WHERE ReferalDate Between April 2004 and May 2004
AND
ClientID not in (
Select DISTINCT ClientID From Table
WHERE ReferalDate < April 2004)
In BusinessObjects you would use the second condition by using a subquery.
SELECT
ALL_PERSONS.IDENTIFIER,
initcap(nvl(ltrim(ALL_PERSONS.FIRST_NAMES||' '||ALL_PERSONS.FAMILY_NAME),'Not Recorded')),
floor(months_between(sysdate,( nvl(ALL_PERSONS.DATE_OF_BIRTH,ALL_PERSONS.APPROX_DATE_OF_BIRTH) ))/12),
REFERRAL.DATE_CONTACT_REFERRED
FROM
PERSONS ALL_PERSONS,
REFERRALS REFERRAL
WHERE
( ALL_PERSONS.IDENTIFIER=REFERRAL.PERSON_IDENTIFIER(+) )
AND (
REFERRAL.DATE_CONTACT_REFERRED >= '01-04-2004 00:00:00'
)
MINUS
SELECT
distinct ALL_PERSONS.IDENTIFIER,
initcap(nvl(ltrim(ALL_PERSONS.FIRST_NAMES||' '||ALL_PERSONS.FAMILY_NAME),'Not Recorded')),
floor(months_between(sysdate,( nvl(ALL_PERSONS.DATE_OF_BIRTH,ALL_PERSONS.APPROX_DATE_OF_BIRTH) ))/12),
REFERRAL.DATE_CONTACT_REFERRED
FROM
PERSONS ALL_PERSONS,
REFERRALS REFERRAL
WHERE
( ALL_PERSONS.IDENTIFIER=REFERRAL.PERSON_IDENTIFIER(+) )
AND ( REFERRAL.DATE_CONTACT_REFERRED < '01-04-2004 00:00:00')
It brings back a list of all persons referred After 1/4/04 even if they have been referred previously. I want it to bring back a list of persons referred after this date who have never been referred before.
i.e. currently it would bring back a person referred for the fist time in April this is OK these people should be in the list, but it also brings back persons who were referred prior to this (eg. referred in 2003 and again in April 04) I want the report to exclude these people from the list.
Is it possible to do this in BO without using sql, I am not very experienced with sql??
SELECT Client ID, Referral date, referral reason, referral ID
FROM table
WHERE Referral date BETWEEN start_date AND end_date
AND Client ID NOT IN (SELECT A.Client ID FROM table a WHERE a.Referral date < start_date AND a.Client ID = Client ID)
Start Date and End Date would be prompts you could use in your query.
Create a data provider
Result Objects: Client ID, Client Name
Condition: Referral Date = April 2004
Then select the Combine queries button and change it to a Minus query.
This query will have the same result objects, change the condition to
Referral Date < April 2004
I’ve modifed the sql you posted and think this may be what you need.
SELECT
ALL_PERSONS.IDENTIFIER,
initcap(nvl(ltrim(ALL_PERSONS.FIRST_NAMES||' '||ALL_PERSONS.FAMILY_NAME),'Not Recorded')),
floor(months_between(sysdate,( nvl(ALL_PERSONS.DATE_OF_BIRTH,ALL_PERSONS.APPROX_DATE_OF_BIRTH) ))/12),
REFERRAL.DATE_CONTACT_REFERRED
FROM
PERSONS ALL_PERSONS,
REFERRALS REFERRAL
WHERE
( ALL_PERSONS.IDENTIFIER=REFERRAL.PERSON_IDENTIFIER(+) )
AND (
REFERRAL.DATE_CONTACT_REFERRED >= '01-04-2004 00:00:00'
)
AND ALL_PERSONS.IDENTIFIER NOT IN (SELECT A.IDENTIFIER
FROM ALL_PERSONS A, REFERRALS B
WHERE B.DATE_CONTACT_REFERRED < '01-04-2004 00:00:00'
AND ALL_PERSONS.IDENTIFIER = A.IDENTIFIER
AND B.IDENTIFIER = A.IDENTIFIER)
Hi, This works if I don’t include the referral date. As soon as I put in the referral datre I get the problem of it bringing back people who have a referral after 1/4/04 but who also have a referral prior to this date. For my report I really needed to have the date on which the client was referred included.
(BOB member since 2003-07-31)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)