Wrong Data
-
In my query below, I want to show all the peopleid's that has a clinical calltype, but eliminate those which has clinical sessions or concurrent review. Even though, I am checking for clinical sessions event dates and concurrent review. . I am still getting the records, which should not be counted.
Here is my qry:
SELECT DISTINCT p.people_id 'People ID', convert(varchar, ep.start_date, 101) 'Episode Start Date',
cltyp.name 'Call Type',
cl.duration 'Duration',
from people p
left outer join cases c on p.people_id = c.people_fk
left outer join v_cases_type cst on cst.v_ct_id = c.cases_type_fk
left outer join episode ep on c.cases_id = ep.cases_fk
left outer join v_problem prob on ep.assessed_problem_fk =prob.v_problem_id
left outer join v_problem_regarding preg on c.problem_regarding_fk= preg.v_pr_id
left outer join service svc on ep.episode_id = svc.episode_fk
left outer join call cl on cl.call_id = svc.call_fk
left outer join v_call_type cltyp on cltyp.v_ct_id = cl.call_type_fk
left join event_note en on en.call_fk = cl.call_id
left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk
left outer join event ev ON auth.authorizations_id =ev.authorizations_fk
left outer join site st on st.site_id = p.site_fk
left outer join contract_product_site cps on st.site_id = cps.site_fk
left outer join contract_product cp on cp.contract_product_id = cps.contract_product_fk
left outer join contract cc on cc.contract_id = cp.contract_fk
left outer join referral ref on ref.referral_id = svc.referral_fk
left outer join v_referral_type rt on rt.v_rt_id = ref.referral_type_fk
left outer join v_referral_detail_type rdt on rdt.v_rdt_id = ref.referral_detail_type_fk
left outer join v_urgency ur on ur.v_urgency_id = svc.urgency_fk
left outer join ##JobType JobTyp ON p.people_id = JobTyp.people_id
left outer join v_contract_relation cr on cr.v_cr_id = svc.contract_relation_fk
where st.site_name like 'pfizer%' and ep.start_date >= @month_start_date and ep.start_date <= @month_end_date
and c.status_fk='1' and p.deactivate is null and st.deactivate is null and
c.deactivate is null and svc.deactivate is null and ref.deactivate is null
and ep.deactivate is null
AND(( cltyp.name= 'Clinical' And ev.event_date is null
AND en.note_type_fk <>'29')
OR ref.referral_id is not null )
ORDER BY st.site_name, p.people_id
Edited by - bhavnabakshi on 07/30/2003 2:41:05 PM
-
<<I want to show all the peopleid's that has a clinical calltype>>
But the sql does not reflect the above because of or clause
AND(( cltyp.name= 'Clinical' And ev.event_date is null
AND en.note_type_fk <>'29')
OR ref.referral_id is not null)
If referrel_id is not null then it will display all rows irrespective of type is clinical or not.
Shouldn't this be
AND cltyp.name= 'Clinical'
AND ((ev.event_date is null
AND en.note_type_fk <>'29')
OR ref.referral_id is not null)
or something else...
Secondly
You have put <ev.event_date is null> as condition and used outer join for ev table.
This will be true in two cases
1. When there is no record in ev matching fk. In this case event_date will be null because of outer join.
2. Record exists in ev but event_date is null.
Hence if you want to exclude rows that do check for only rows that have a row in ev and date is null then try adding ev.authorizations_fk is not null
Hope this helps
-
I am still getting records which has event date or note_type_fk =29.
I added "ev.authorizations_fk is not null"
in the clause. For e.g. <B>cltyp.name= 'Clinical'
AND ((ev.event_date is null AND ev.authorizations_fk is not null
AND en.note_type_fk <>'29')
OR ref.referral_id is not null) </B>
this returns me no records.
-
One more thing, in the database I have more note_types. I want to eliminate those records which has note_type_fk='29', but the resultset is displaying other note_type_fk's. Can someone please help me out...
-
The trick here is in excluding all people with any record where note_type_fk = 29. Your current query will return people with note_type_fk = 29 if they also have a record with a different note_type_fk.
Analyzing a query of this complexity is not simple without the actual data behind it, but you can try this clause:
AND cltyp.name= 'Clinical'
And ((ev.event_date is null
AND Not Exists
(Select en2.note_type_fk
FROM people p2
left outer join cases c2 on p2.people_id = c2.people_fk
left outer join episode ep on c2.cases_id = ep2.cases_fk
left outer join service svc2 on ep2.episode_id = svc2.episode_fk
left outer join call cl2 on cl2.call_id = svc2.call_fk
left join event_note en2 on en2.call_fk = cl2.call_id
WHERE p2.People_id = p.people_id AND
en2.note_type_fk = '29')
OR ref.referral_id is not null )
-
<<I am still getting records which has event date or note_type_fk =29. >>
Again it looks to me that this is because of logical error.
As per the sql if ref.referral_id is not null then it will not check note_type_fk and event date. This is because of OR clause again.
So in this case the sql should be
AND cltyp.name= 'Clinical'
AND (ev.event_date is null
AND en.note_type_fk <>'29')
Please clarify the significance of <OR ref.referral_id is not null> and what should happen if referral_id is null/not null.
-
The biggest problem is the complexity of the query you have to build and the fact we have no idea what your tables look like or the data in them. Can you please give use a sampling of what you are getting and what you are expecting (changing sensitive info if needs be)? Are you sure these all should be Left joins?
Here is you code in a format to show relationships better for reading.
SELECT DISTINCT
p.people_id 'People ID',
convert(varchar, ep.start_date, 101) 'Episode Start Date',
cltyp.name 'Call Type',
cl.duration 'Duration',
from
people p
left join
cases c
left join
episode ep
left join
service svc
left join
call cl
left join
v_call_type cltyp
on
cltyp.v_ct_id = cl.call_type_fk
left join
event_note en
on
en.call_fk = cl.call_id
on
cl.call_id = svc.call_fk
left join
v_urgency ur
on
ur.v_urgency_id = svc.urgency_fk
left join
v_contract_relation cr
on
cr.v_cr_id = svc.contract_relation_fk
left join
authorizations auth
left join
event ev
ON
auth.authorizations_id =ev.authorizations_fk
ON
auth.authorizations_id = svc.authorizations_fk
left join
referral ref
left join
v_referral_type rt
on
rt.v_rt_id = ref.referral_type_fk
left join
v_referral_detail_type rdt
on
rdt.v_rdt_id = ref.referral_detail_type_fk
on
ref.referral_id = svc.referral_fk
on
ep.episode_id = svc.episode_fk
left join
v_problem prob
on
ep.assessed_problem_fk =prob.v_problem_id
on
c.cases_id = ep.cases_fk
left join
v_cases_type cst
on
cst.v_ct_id = c.cases_type_fk
left join
v_problem_regarding preg
on
c.problem_regarding_fk= preg.v_pr_id
on
p.people_id = c.people_fk
left join
site st
left join
contract_product_site cps
left join
contract_product cp
left join
contract cc
on
cc.contract_id = cp.contract_fk
on
cp.contract_product_id = cps.contract_product_fk
on
st.site_id = cps.site_fk
on
st.site_id = p.site_fk
left join
##JobType JobTyp
ON
p.people_id = JobTyp.people_id
WHERE
st.site_name like 'pfizer%' and
ep.start_date >= @month_start_date and
ep.start_date <= @month_end_date and
c.status_fk='1' and
p.deactivate is null and
st.deactivate is null and
c.deactivate is null and
svc.deactivate is null and
ref.deactivate is null and
ep.deactivate is null AND
(
(
cltyp.name = 'Clinical' And
ev.event_date is null AND
en.note_type_fk != '29'
) OR
ref.referral_id is not null
)
ORDER BY
st.site_name,
p.people_id -
Thanks Antares686
This looks much better. You are standards man
-
Brendthess query sort of works, but still getting records that has event dates. Requirements for this query are: show all the records that have at least one clinical call, but no clinical session events or concurrent review note types. Most of the fields that I am retrieving are saved as separate record in the db in several different tables. That's why I am doing left outer join. Reason, I am checking for Referral_id not NULL is to get referrals from the database. If I take out the referral_id, from the where clause, I am getting NULL in Referral type. Thing is even if I am checking for note_type_fk !='29', there are more note_types that can be associated with the people_id. I have to eliminate the records, which have an event or concurrent review. Thanks everyone for your help.
Here is the sample of what it is suppose to look like:
People ID#SiteGenderPresenting ProblemProblem RegardingEpisode Create DateAssessed Intake ProblemCall TypeUrgencyRelationshipDurationJob TypeReferral TypeReferral Detail TypeReferral Acceptance
Ann Arbor, MIFemaleFinancialSelf4/7/2003FinancialClinicalRoutineEmployee25Office/ClericalFinancialBudget HelpYes
Edited by - bhavnabakshi on 08/01/2003 07:25:33 AM
-
Instead of going by ev.event_date is NULL, I am checking ev.event_date not between '4/01/2003' and '6/30/2003' still it does not work 🙁
-
The problem with event dates is related to the problem that I discussed with the Note_Type_FK -- you need to check all cases. SO, add this to your query (an extension of my previous code):
AND cltyp.name= 'Clinical'
And ((Not Exists
(Select ev.event_date
FROM people p2
left outer join cases c2 on p2.people_id = c2.people_fk
left outer join episode ep on c2.cases_id = ep2.cases_fk
left outer join service svc2 on ep2.episode_id = svc2.episode_fk
left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk
left outer join event ev ON auth.authorizations_id =ev.authorizations_fk
WHERE p2.People_id = p.people_id AND
IsDate(ev.event_date)
)
AND Not Exists
(Select en2.note_type_fk
FROM people p2
left outer join cases c2 on p2.people_id = c2.people_fk
left outer join episode ep on c2.cases_id = ep2.cases_fk
left outer join service svc2 on ep2.episode_id = svc2.episode_fk
left outer join call cl2 on cl2.call_id = svc2.call_fk
left join event_note en2 on en2.call_fk = cl2.call_id
WHERE p2.People_id = p.people_id AND
en2.note_type_fk = '29')
OR ref.referral_id is not null )
-
I believe I have to use inner join for Event_date and that works. Now, if I use "OR" for the second loop in where clause, I get records that has events and if I use "AND" that gives me records that does not have event_note_type_fk 29.
AND ((cltyp.name= 'Clinical'
AND Not Exists
(Select ISDATE(ev.event_date)
FROM people p2
INNER join cases c2 on p2.people_id = c2.people_fk
INNER join episode ep2 on c2.cases_id = ep2.cases_fk
INNER join service svc2 on ep2.episode_id = svc2.episode_fk
INNER join authorizations auth ON auth.authorizations_id = svc.authorizations_fk
INNER join event ev ON auth.authorizations_id =ev.authorizations_fk
WHERE p2.People_id = p.people_id
)
ORNot Exists
(Select en2.note_type_fk
FROM people p2
INNER join cases c2 on p2.people_id = c2.people_fk
INNER join episode ep2 on c2.cases_id = ep2.cases_fk
INNER join service svc2 on ep2.episode_id = svc2.episode_fk
INNER join call cl2 on cl2.call_id = svc2.call_fk
INNER join event_note en2 on en2.call_fk = cl2.call_id
WHERE p2.People_id = p.people_id AND
en2.note_type_fk = '29')
))
Edited by - bhavnabakshi on 08/04/2003 08:54:22 AM
-
Thanks brendthess, I am able to fix the problem everything works great.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply