Hi I want to be able to do the following so I can use count and sum to find all who fit the criteria and all whether they fit the criteria or not.
WHERE Exists (decode(select * from Case_Details first
where first.Case_ID <> second.Case_ID
And first.NHI = second.NHI
And second.Case_Start_Date - first.Case_End_Date Between 0 And 365),1,0)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi thanks for the reply,
SELECT second.Workunit, second.NHI, second.Case_Start_Date, second.Case_End_Date,secon
FROM Case_Details second
WHERE Exists ( select * from Case_Details first
where first.Case_ID <> second.Case_ID
And first.NHI = second.NHI
And second.Case_Start_Date - first.Case_End_Date Between 0 And 365 )
AND second.Service_Subtype <> 'Inpatient Mental Health Services'
AND second.workunit =:PARAM3
using this statement will retrieve only rows where EXISTS second.Case_Start_Date - first.Case_End_Date Between 0 And 365.
for all the other rows use NOT EXISTS.
if you want a column in your select representing this, you could use
SELECT SECOND.workunit
,SECOND.nhi
,SECOND.case_start_date
,SECOND.case_end_date
,SECOND.case_id
,CASE
WHEN SECOND.case_start_date - FIRST.case_end_date BETWEEN 0 AND 365 THEN 1
ELSE 0
END
FROM case_details SECOND
,case_details FIRST
WHERE FIRST.case_id <> SECOND.case_id AND
FIRST.nhi = SECOND.nhi AND
SECOND.case_start_date - FIRST.case_end_date BETWEEN 0 AND 365 AND
SECOND.service_subtype <> 'Inpatient Mental Health Services' AND
SECOND.workunit = :param3;
disadvantage: if there are multiple rows in case_details FIRST, you'll get multiple results.
maybe a select with UNION?
SELECT SECOND.workunit
,SECOND.nhi
,SECOND.case_start_date
,SECOND.case_end_date
,SECOND.case_id
, 1
FROM case_details SECOND
WHERE EXISTS (
SELECT *
FROM case_details FIRST
WHERE FIRST.case_id <> SECOND.case_id AND
FIRST.nhi = SECOND.nhi AND
SECOND.case_start_date - FIRST.case_end_date BETWEEN 0 AND 365) AND
SECOND.service_subtype <> 'Inpatient Mental Health Services' AND
SECOND.workunit = :param3
UNION
SELECT SECOND.workunit
,SECOND.nhi
,SECOND.case_start_date
,SECOND.case_end_date
,SECOND.case_id
, 0
FROM case_details SECOND
WHERE NOT EXISTS (
SELECT *
FROM case_details FIRST
WHERE FIRST.case_id <> SECOND.case_id AND
FIRST.nhi = SECOND.nhi AND
SECOND.case_start_date - FIRST.case_end_date BETWEEN 0 AND 365) AND
SECOND.service_subtype <> 'Inpatient Mental Health Services' AND
SECOND.workunit = :param3
1st thing is, looks like you are treying to join the same table (Self-join). if thiss is the case, you may end up with a cartician join which you may not want. so there must be some criteria on basis of which you want to join first then compare.
I am assuming the criteria is, "first.NHI = second.NHI "
on this baisi the query should be,
select first.Case_ID, second.Case_ID, second.NHI,
(case when second.Case_Start_Date - first.Case_End_Date Between 0 And 365 then 1 else 0) criteria_result
from <TABLE> first, <TABLE> second
where first.Case_ID <> second.Case_ID
And first.NHI = second.NHI;
If this is not the case, please provide the test data and output format that you want for your query.
sorry i forgot the END to the case statement, re-writing the query
select first.Case_ID, second.Case_ID, second.NHI,
(case when second.Case_Start_Date - first.Case_End_Date Between 0 And 365 then 1 else 0 end) criteria_result
from <TABLE> first, <TABLE> second
where first.Case_ID <> second.Case_ID
And first.NHI = second.NHI;
Business Accounts
Answer for Membership
by: rbrookerPosted on 2007-07-08 at 20:02:21ID: 19442654
can you post the entire sql please?