How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Michael Hannigan Your Own Question

Michael Hannigan
Michael Hannigan, Consultant
Category: Programming
Satisfied Customers: 11190
Experience:  25+ Years Experience in Field. Multi Platform Developer.
23511658
Type Your Programming Question Here...
Michael Hannigan is online now
A new question is answered every 9 seconds

I am trying to find out whether people have met certain due

Customer Question

Hi, I am trying to find out whether people have met certain due dates. They each have different start dates. They have phone meetings due every 30 days beginning at the start date and face to face meetings every 90 days. I want to see their most recent meeting date they completed and find out whether it was on time based on the schedule, and also when their next due date would be based on the schedule (of 30 or 90 days from the start date up to the present time). We need one line item per person for their phone meeting and one for the face to face. Can you help with the logic for this? Here is what we came up with so far:
SELECT DIM_MEMBER.MEMBER_ID, DIM_MEMBER.FIRST_NAME, DIM_MEMBER.LAST_NAME, (DIM_PAYER.FIRST_NAME | | ' ' | | DIM_PAYER.LAST_NAME) AS CC,
DIM_MEMBER_DIAGNOSIS.DIAGNOSIS_CODE, to_char(DIM_MEMBER_DIAGNOSIS.START_DATE, 'MM/DD/YYYY') AS CNA_START,
to_char(DIM_NOTE.MODIFIED_DATE, 'MM/DD/YYYY') AS MODIFIED, DIM_NOTE.CASE_NOTETYPE, DIM_CASE."TYPE", DIM_CASE.CASE_RECORDSTATE,
DIM_CASE.CASE_ID
FROM FACT_NOTE, DIM_CASE, DIM_NOTE, DIM_USER, FACT_CASE, DIM_MEMBER, DIM_PAYER, FACT_MEMBER_DIAGNOSIS, DIM_MEMBER_DIAGNOSIS
WHERE FACT_NOTE.CM_ID = DIM_CASE.CASE_ID
AND FACT_NOTE.NOTE_ID = DIM_NOTE.NOTE_ID
AND FACT_NOTE.OWNER_ID = DIM_USER.USER_ID
AND DIM_CASE.CASE_ID = FACT_CASE.CASE_ID
AND FACT_CASE.MEMBER_ID = DIM_MEMBER.MEMBER_ID
AND(DIM_NOTE.CASE_NOTETYPE LIKE '%Required%Complete')
AND (DIM_CASE."TYPE" LIKE 'Behav%')
AND DIM_PAYER.PAYER_ID = DIM_USER.USER_ID
AND DIM_CASE.CASE_RECORDSTATE = 'OPEN'
AND FACT_MEMBER_DIAGNOSIS.MEMBER_ID = DIM_MEMBER.MEMBER_ID
AND DIM_MEMBER_DIAGNOSIS.MEMBERDIAGNOSIS_ID = FACT_MEMBER_DIAGNOSIS.MEMBERDIAGNOSIS_ID
AND DIM_MEMBER_DIAGNOSIS.ERROR = 'FALSE'
AND DIM_MEMBER_DIAGNOSIS.DELETED_DATE IS NULL
AND DIM_MEMBER_DIAGNOSIS.DIAGNOSIS_CODE IN ('CC Level 1', 'CC Level 2', 'CC Level 3')
AND DIM_MEMBER_DIAGNOSIS.START_DATE = CURRENT_DATE OR DIM_MEMBER_DIAGNOSIS.END_DATE IS NULL)
GROUP BY DIM_MEMBER.MEMBER_ID, DIM_MEMBER.FIRST_NAME, DIM_MEMBER.LAST_NAME, DIM_PAYER.FIRST_NAME, DIM_PAYER.LAST_NAME,
DIM_NOTE.MODIFIED_DATE, DIM_NOTE.CASE_NOTETYPE, DIM_CASE."TYPE", DIM_CASE.CASE_RECORDSTATE, DIM_MEMBER_DIAGNOSIS.DIAGNOSIS_CODE,
DIM_MEMBER_DIAGNOSIS.START_DATE, DIM_CASE.CASE_ID
Submitted: 1 year ago.
Category: Programming
Expert:  Michael Hannigan replied 1 year ago.

Do you really mean to have all these conditions on the query? Maybe it is too restrictive to return any records. What do you get back when you run it?

FACT_NOTE.CM_ID = DIM_CASE.CASE_ID AND

FACT_NOTE.NOTE_ID = DIM_NOTE.NOTE_ID AND

FACT_NOTE.OWNER_ID = DIM_USER.USER_ID AND

DIM_CASE.CASE_ID = FACT_CASE.CASE_ID AND

FACT_CASE.MEMBER_ID = DIM_MEMBER.MEMBER_ID AND

(DIM_NOTE.CASE_NOTETYPE LIKE '%Required%Complete') AND (DIM_CASE."TYPE" LIKE 'Behav%') AND

DIM_PAYER.PAYER_ID = DIM_USER.USER_ID AND

DIM_CASE.CASE_RECORDSTATE = 'OPEN' AND

FACT_MEMBER_DIAGNOSIS.MEMBER_ID = DIM_MEMBER.MEMBER_ID AND

DIM_MEMBER_DIAGNOSIS.MEMBERDIAGNOSIS_ID = FACT_MEMBER_DIAGNOSIS.MEMBERDIAGNOSIS_ID AND

DIM_MEMBER_DIAGNOSIS.ERROR = 'FALSE' AND DIM_MEMBER_DIAGNOSIS.DELETED_DATE IS NULL AND

DIM_MEMBER_DIAGNOSIS.DIAGNOSIS_CODE IN ('CC Level 1', 'CC Level 2', 'CC Level 3') AND

DIM_MEMBER_DIAGNOSIS.START_DATE = CURRENT_DATE OR DIM_MEMBER_DIAGNOSIS.END_DATE IS NULL)

Customer: replied 1 year ago.
We get about 560 lines of data with the following headers. It returns data, but from that data we want to see whether each person has been compliant with their follow ups. It is pulling multiple lines per person, but we really only want to see one line item per person and if their Modified Date meets the time due or not and when their next follow up should be. The data itself is confidential, but here is a screen shot of the headers :
Expert:  Michael Hannigan replied 1 year ago.

there are ways you can list only the most recent record or the first record returned, etc. Here is a basic example of how you would select the latest.

select t.username, t.date, t.valuefrom MyTable tinner join ( select username, max(date) as MaxDate from MyTable group by username) tm on t.username = tm.username and t.date = tm.MaxDate

Using MaxDate will give you only the most recent.

I would appreciate a moment of your time to rate my level of service at your convenience. Thank you.

Mike

Customer: replied 1 year ago.
The issue is not getting only the most recent, it is in the comparing each follow up to the applicable date due. Because there are scheduled due dates and numerous follow up dates we need to find whether each follow up was a timely one and when the next one will be.
Expert:  Michael Hannigan replied 1 year ago.

You can use group by to group the results by individual.

Customer: replied 1 year ago.
I know that, but we aren't getting the results we want with the query we have. We have decided to do this manually. Thank you for your efforts, I appreciate it.
Expert:  Michael Hannigan replied 1 year ago.

You're welcome. I would appreciate a moment of your time to rate my level of service using the OK, GOOD, or EXCELLENT rating icons at your convenience.

Customer: replied 1 year ago.
Will I be charged the $50 for this unanswered question? I understand I will have to pay $5 for the convenience which is fine. You deserve a good rating for effort, but I don't think I should have to pay for something which wasn't completely answered and we have to do it manually anyway. . . . .The system originally sent me an email stating that they weren't able to answer it, and then you asked me another question after that, extending the communication, but the end results were the same. Thanks for letting me know if you can.
Expert:  Michael Hannigan replied 1 year ago.

I am only a technical resource. For any account related questions, please contact JustAnswer directly.