0

This query returns all of the selected values from today's date to 90 days ago: 

 SELECT max(cases_visits.created_dt), users_profiles.account_num,
    concat(cases.patient_first_initial,
    cases.patient_middle_initial, cases.patient_last_initial) AS initials,
    concat(users.first_name, ' ',users.last_name) as name   
    FROM cases
    JOIN users_profiles
    ON users_profiles.user_id=cases.doctor_id
    JOIN cases_visits
    ON cases.id=cases_visits.case_id

    join users on users.id = cases.doctor_id

    WHERE cases_visits.patient_visit_type = 'NP' && cases_visits.created_dt BETWEEN     curdate() - INTERVAL 90 DAY AND SYSDATE()

    group by users.first_name

I'd like to find a query that will now select the exact same thing, but only if records DO not exist in the previous query. EXAMPLE: return records from > 90 days ago, that do not have records in the past 90 days.

I have tried to do this: (note, 2013-07-03 in the query was 90 days from the first time i ran)

    SELECT cases_visits.created_dt, users_profiles.account_num,
    concat(cases.patient_first_initial,
    cases.patient_middle_initial, cases.patient_last_initial) AS initials,
    concat(users.first_name, ' ',users.last_name) as name
    FROM cases
    JOIN users_profiles
    ON users_profiles.user_id=cases.doctor_id
   JOIN cases_visits
   ON cases.id=cases_visits.case_id
   join users on users.id = cases.doctor_id
   WHERE cases_visits.created_dt < '2013-07-03'
   group by users.first_name

This does not give me the proper data, I think because i need to somehow exclude records that exist from the past 90 days.

THIS IS WHAT IM TRYING TO DO: Select a records with a a value = to 'NP' for the past 90 days, then i need to select records where there is not a np value for greater than 90 days, but these records should be completely unique from the first query (i.e the individual could have a case from within the 90 days, and one 180 days ago, i would not need his records.)

EDIT: I forgot to mention I have tried this query with an error near 'in':

SELECT cases_visits.created_dt, users_profiles.account_num,
concat(cases.patient_first_initial,
cases.patient_middle_initial, cases.patient_last_initial) AS initials,
concat(users.first_name, ' ',users.last_name) as name    
FROM cases 
JOIN users_profiles
ON users_profiles.user_id=cases.doctor_id    
JOIN cases_visits
ON cases.id=cases_visits.case_id    
join users on users.id = cases.doctor_id    
WHERE cases_visits.created_dt < '2013-07-03'
and cases_visits.patient_visit_type = 'NP'    
and not in (select created_dt from cases_visits where  cases_visits.patient_visit_type = 'NP' && cases_visits.created_dt BETWEEN curdate() - INTERVAL 90 DAY AND SYSDATE())   
group by users.first_name
Kisaragi
  • 2,198
  • 3
  • 16
  • 28

3 Answers3

0

You could use a subquery:

select * from table where ID NOT IN (select id from table where a=1);

This would essentially select the records from the table that don't match the records the inner query did match.

Josh Ribakoff
  • 2,948
  • 3
  • 27
  • 26
0
SELECT * FROM table WHERE cases_visits.created_dt < '2013-07-03'
AND case_visist.SOME_UNIQUE_ID NOT IN 
(SELECT case_visist.SOME_UNIQUE_ID FROM table WHERE cases_visits.patient_visit_type = 'NP' && cases_visits.created_dt BETWEEN     curdate() - INTERVAL 90 DAY AND SYSDATE() )

You can use NOT IN statement and enter a SELECT statement that select all the records that should be excluded. A some more info in this thread

Community
  • 1
  • 1
Jaroslav
  • 1,389
  • 14
  • 27
  • This doesn't seem to work, im dubious if the < '2013-07-03' is working like I think it would. I seem to be having a problem using the 'not in' – Kisaragi Oct 03 '13 at 17:00
  • In you edited question last query you forgot to write what column to compare in NOT IN. You should change "and not in" to "and `cases_visits`.`created_dt` not in (...". – Jaroslav Oct 03 '13 at 17:04
  • Its funny that you mentioned that, I had just realized that lol. Seems to have worked, but ill check for the duplicates – Kisaragi Oct 03 '13 at 17:07
  • If that solved your problem, it would be kind if you would accept this answer. – Jaroslav Oct 03 '13 at 17:39
  • I'm still getting records that have returns within the original 90 days. I have tried various combinations but cannot get them to not be selected! – Kisaragi Oct 03 '13 at 17:41
  • Start debugging by making select in "NOT IN" work - it should select everything you want to exclude. When it works good, put it in this bigger query and if something still doesn't work as supposed, you will know that the first subquery is working good and you should search for a problem in other places. – Jaroslav Oct 03 '13 at 17:44
0

This set should be visits for the same doctor, same cases but a different visit?

SELECT max(cases_visits.created_dt), users_profiles.account_num,
  concat(cases.patient_first_initial,
  cases.patient_middle_initial, cases.patient_last_initial) AS initials,
  concat(users.first_name, ' ',users.last_name) as name   
FROM cases
JOIN users_profiles
  ON users_profiles.user_id=cases.doctor_id
JOIN cases_visits
  ON cases.id=cases_visits.case_id
JOIN users
  ON users.id = cases.doctor_id
WHERE cases_visits.patient_visit_type = 'NP' && cases_visits.created_dt <= curdate() - INTERVAL 90 DAY
  AND EXISTS(SELECT *
         FROM case_visits AS inside
         WHERE inside.case_id = cases.id
           AND inside.patient_visit_type = 'NP'
           AND inside.created_dt BETWEEN curdate() - INTERVAL 90 DAY AND SYSDATE())
GROUP BY account_num, initials, name
AgRizzo
  • 5,261
  • 1
  • 13
  • 28