Appointment report does not match Appointment Schedule module

mahmoud2020 · April 20, 2021, 12:44pm

Hello, I am new to EMR, I need help with this issue, I have a report called Appointment details which I fetch the info from the appointment module but the generated Excel file is different and missing some appointments, the SQL query has been written 3 years ago and a month ago we noticed this problem in the report, I am attaching the SQL script and the configuration file in case someone is able to point out the problem.

Thanks in advance

SELECT
    identifierOfPatient.identifier AS "Patient ID",
    concat_ws(' ', nameOfTheProviders.given_name, nameOfTheProviders.family_name) AS "Provider",
    serviceAppointment.name AS "Appointment Service",
    serviceTypeAppointment.name AS "Appointment Service Type",
    DATE(pai.start_date_time) AS "Date Of Appointment",
    DATE_FORMAT(pai.start_date_time, '%I:%i %p') AS "Appointment Start Time",
    DATE_FORMAT(pai.end_date_time, '%I:%i %p') AS "Appointment End Time",
    pai.status AS "Status",
    (CASE WHEN pai.status in('Scheduled') then NULL ELSE DATE_FORMAT(CONVERT_TZ(appCheckinTime.notes,'+00:00','+05:30'),'%I:%i %p') END) AS "Start CheckedIn Time",
    (CASE WHEN language.concept_full_name is not null and otherLanguage.value is Not NULL
      then concat_ws(', ',language.concept_full_name,otherLanguage.value)
      WHEN language.concept_full_name is not null and otherLanguage.value is NULL then language.concept_full_name
      WHEN language.concept_full_name is null and otherLanguage.value is NOT NULL THEN otherLanguage.value
      ELSE NULL END) AS "Patient's Language",
  pai.comments AS "Notes"

FROM
  patient pa
  LEFT JOIN patient_appointment pai ON pa.patient_id = pai.patient_id and pai.voided = 0
  LEFT JOIN
            (/*getting checkin time*/
            SELECT
            latest_appointment_audit.appointment_id,
            latest_appointment_audit.notes,
            latest_appointment_audit.date_created,
            pa.patient_id,
            latest_appointment_audit.status
            FROM
            patient_appointment pa
            INNER JOIN
            (
            SELECT
                latest_appointment_audit.appointment_id,
                latest_appointment_audit.notes,
                latest_appointment_audit.date_created,
                pa.patient_id,
                latest_appointment_audit.status
            FROM
                patient_appointment pa
                INNER JOIN
                    (
                        SELECT
                            paa.patient_appointment_audit_id,
                            paa.appointment_id,
                            paa.date_created,
                            paa.notes,
                            paa.status
                        FROM
                            patient_appointment_audit paa
                            INNER JOIN
                                (
                                    /*getting latest status of appointment*/
                                    SELECT
                                        MAX(paa.date_created) AS date_created,
                                        paa.appointment_id,
                                        paa.notes
                                    FROM
                                        patient_appointment_audit paa
                                    WHERE
                                        paa.status = 'CheckedIn'
                                    GROUP BY
                                        paa.appointment_id
                                )
                                latest_audit_for_appointment
                                ON latest_audit_for_appointment.appointment_id = paa.appointment_id
                                AND latest_audit_for_appointment.date_created = paa.date_created
                    )
                    latest_appointment_audit
                    ON pa.patient_appointment_id = latest_appointment_audit.appointment_id
                    AND pa.voided = 0
            )
            latest_appointment_audit
            ON pa.patient_appointment_id = latest_appointment_audit.appointment_id
            AND pa.voided = 0
            ) as appCheckinTime on pai.patient_id = appCheckinTime.patient_id and appCheckinTime.appointment_id = pai.patient_appointment_id
  LEFT JOIN appointment_service serviceAppointment ON   pai.appointment_service_id = serviceAppointment.appointment_service_id AND serviceAppointment.voided = 0
  LEFT JOIN appointment_service_type serviceTypeAppointment ON serviceAppointment.appointment_service_id = serviceTypeAppointment.appointment_service_id  AND pai.appointment_service_type_id = serviceTypeAppointment.appointment_service_type_id AND serviceTypeAppointment.voided = 0
  LEFT JOIN patient_identifier identifierOfPatient ON pai.patient_id = identifierOfPatient.patient_id
  LEFT JOIN visit checkinTime ON pai.patient_id = checkinTime.patient_id AND DATE(checkinTime.date_started) = DATE(pai.start_date_time)/*getting Start visit time*/
  LEFT JOIN provider providerForAppointment ON pai.provider_id = providerForAppointment.provider_id
  LEFT JOIN person_name nameOfTheProviders ON providerForAppointment.person_id = nameOfTheProviders.person_id
  LEFT JOIN person_attribute attributeOfPatient ON pai.patient_id = attributeOfPatient.person_id
  LEFT JOIN person_attribute_type typeOfPatientAttribute ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
  LEFT JOIN concept_view cv ON cv.concept_id = attributeOfPatient.value
  Left Join
  (/*Getting the value for language*/
        SELECT
        attributeOfPatient.person_id,
        cv.concept_full_name
        FROM
        person_attribute attributeOfPatient
        INNER JOIN
            person_attribute_type typeOfPatientAttribute
            ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
        INNER JOIN
            concept_view cv
            ON cv.concept_id = attributeOfPatient.VALUE
        WHERE
        typeOfPatientAttribute.name = "Language"
        AND attributeOfPatient.voided = 0
  ) as language on pai.patient_id = language.person_id

  Left join
 (/*Getting the value for Other language*/
        SELECT
        attributeOfPatient.person_id,
        attributeOfPatient.VALUE
        FROM
        person_attribute attributeOfPatient
        INNER JOIN
            person_attribute_type typeOfPatientAttribute
            ON attributeOfPatient.person_attribute_type_id = typeOfPatientAttribute.person_attribute_type_id
        WHERE
        typeOfPatientAttribute.name = "Other Language"
        AND attributeOfPatient.voided = 0
  ) as otherLanguage on pai.patient_id = otherLanguage.person_id

WHERE
  DATE(pai.start_date_time) BETWEEN DATE('#startDate#') AND DATE('#endDate#')
  AND pai.appointment_kind='Scheduled'
  AND pai.appointment_service_id IN
                                  (
                                    SELECT appointment_service_id
                                    FROM appointment_service
                                    where voided = 0
                                  )
  AND identifierOfPatient.identifier not in ('A00000')/*removing dummy patient from reports*/
GROUP BY pai.patient_appointment_id
ORDER BY pai.start_date_time;


{
  "appointmentStatusReport": {
   "name": "Appointment Details",
    "type": "MRSGeneric",
    "config": {
        "sqlPath": "/var/www/bahmni_config/openmrs/apps/reports/sql/appointment_status_report.sql"
    }
  }
}

mksd · April 20, 2021, 1:40pm

Hi @mahmoud2020,

Any pattern that you observed about the missing appointments?

I see that your report skips reporting on voided entities (which makes sense), but could it be that some have been voided ending up not showing anymore?

mahmoud2020 · April 20, 2021, 2:17pm

I can not detect a certain pattern yet, the only pattern that I see, skips some appointments for certain providers, for example in the Appointment Scheduling module for X provider on a certain date, there are 4 appointments, in the excel sheet there are not appointments for this provider.

mksd · April 20, 2021, 2:37pm

If you know already that then, take the query and specialize it for this faulty provider. So literally hardcode stuff like "where provider.id = 123" where 123 is the id of the faulty provider.

Then… trim down the query until you find which piece seems to be taking the data away.

Does that make sense?

That’s unless you can guess right away what could be different with this provider from another provider for whom the data comes out fine.

mahmoud2020 · April 21, 2021, 8:24am

I think the problem with one appointment_service, that the reports skips some data in it not the entire data though, I checked the configuration for it and it’s just the same like the others.

mksd · April 27, 2021, 3:31pm

What makes you say that?

Could it be that a voided service still somehow shows up through the UI? That would explain that your reports skips it, while the UI still shows it:

pai.appointment_service_id IN
  (
    SELECT appointment_service_id
    FROM appointment_service
    where voided = 0
  )

If you run

SELECT name FROM appointment_service WHERE voided = 1;

does it show service names that you continue to see through the UI?

mahmoud2020 · April 27, 2021, 3:37pm

Hello,

Thanks for your reply, when I run this command SELECT name FROM appointment_service WHERE voided = 1; Here is what I get

mksd · April 27, 2021, 3:39pm

And could those be the service names of appointments that you see coming up through the app but not through the SQL report?

mahmoud2020 · April 27, 2021, 3:41pm

Sadly not if I run the same command but with voided = 0, I get all the services we have. but the 2 rows that I got with voided = 1, I have no idea what are those.

mksd · April 27, 2021, 3:45pm

But in the app, do you see ‘Intake’ and ‘Interpreter’ appointments in the appointments lists?

If not, how did you measure that your SQL report was not reporting the expected data? What did you compare it to?

mahmoud2020 · April 27, 2021, 3:49pm

‘Intake’ and ‘Interpreter’ are not in the appointments lists, maybe I was not clear but when I generate the excel report some appointments are missing but they are represented in the Appointment Scheduling module UI, I can see them in the interface but they do not exist in the excel sheet. I checked the configuration for all the providers and services and all of them are the same but even some providers show in the report some times and some times not. I am new to EMR and this issue is urgent to be solved. Is that answers your question?

mksd · April 27, 2021, 4:03pm

Sort of You have to imagine that we do not have all the infos that you have. We do not know in which direction you observe discrepancies… etc. So every detail matters.

Do you mean that the problem is not consistently happening?

If it is consistently happening, settle on a situation where you see data obviously missing from your report. Then start trimming down the SQL query until you understand which part of it is making the data go away.

If it is unstable, as in happening inconsistently (sometimes yes, sometimes no), then it may be a completely different story. So let’s settle that point first.

mahmoud2020 · April 27, 2021, 4:11pm

It’s consistent, what I meant is the behavior of the missing info is not consistent, I did trim the SQL script but I could not find the cause of the bug still, is there a way that could be a meta data or caching or some sort of restart is needed ?

mksd · April 27, 2021, 4:26pm

When trimming down the query, didn’t you eventually end up with something simple enough so that the reason why data is not coming up becomes obvious?

I’m not telling you to edit and run the report, I mean using its SQL query directly on the database through the MySQL CLI (or any MySQL tool that you find appropriate.)

mahmoud2020 · April 28, 2021, 4:08pm

Hello @mksd , I followed your advice, I found the missing appointments in the excel sheet, they don’t exist in the database at all but they represented in the appointment scheduling module UI. it means when they created the appointment, the DB did not update the entry for it. Any thoughts, please?

Thanks!

mksd · April 29, 2021, 8:14am

@mahmoud2020 can you go to the appointments lists showing those appointments that you don’t expect to see (/bahmni/appointments/#/home/manage/appointments/calendar), and could you copy the response of /openmrs/ws/rest/v1/appointment/all?

Then can you paste the response’s body in your reply on this thread?

mahmoud2020 · April 29, 2021, 8:54am

Hello @mksd ,

Thanks for your reply, here is the array from the response I filtered it to minimize the response, the one with the uuid: “287068df-81e9-4151-af7c-516e2525ce0d”, this is the one that I could found in patient_appointment table and not in the generated excel sheet report but I still see it in the UI. There are other appointments with the same behavior.

  [ {

    uuid: "490054f5-48de-4141-9b96-5332b9d43cf4",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14601",

      name: "SA",

      uuid: "e101ea3a-709b-46f2-bb9a-ead543c7eebe",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "dy",

      uuid: "cdbb1873-bd97-4b7f-9765-97e1ef396c28",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "NA _ green _ physical",

    additionalInfo: {

      language: "S",

    },

  },

  {

    uuid: "2f3dfe57-19fe-4423-a1c6-ae8e0e795708",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14008",

      name: "V",

      uuid: "feeb503d-7fed-4231-86ae-2a69dac93381",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "M A",

      uuid: "8cfd8913-1725-48db-96e7-b77d72b44c42",

    },

    location: null,

    startDateTime: 1617706800000,

    endDateTime: 1617710400000,

    appointmentKind: "Scheduled",

    status: "Cancelled",

    comments: "",

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "287068df-81e9-4151-af7c-516e2525ce0d",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14000",

      name: "N",

      uuid: "54ad5bfa-f2f2-46cd-af9f-560f14c8fe8c",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "P",

      uuid: "01e6a22a-113a-434d-af69-3869cbba5479",

    },

    location: null,

    startDateTime: 1617710400000,

    endDateTime: 1617714000000,

    appointmentKind: "WalkIn",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "17acb20a-d22c-4ef0-9fcb-13bc24a9c8d5",

    appointmentNumber: "0000",

    patient: {

      identifier: "A12594",

      name: "S",

      uuid: "6edba21c-aebc-433c-93a8-6dbeee23d3a0",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Medical",

      uuid: "78204b5d-568e-4fd7-9b8e-477d21a32616",

    },

    provider: {

      name: "H",

      uuid: "ad5158fa-b81a-4c97-8cee-d24e72e7ffe3",

    },

    location: null,

    startDateTime: 1617710400000,

    endDateTime: 1617714000000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "75714ace-e597-40b1-816a-6e392b006110",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14686",

      name: "E",

      uuid: "1500e255-1687-4e6e-a73b-da16c46cba8c",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "Ra R",

      uuid: "74dea2ee-a2eb-4c78-be86-013948fd952b",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Missed",

    comments: "Physical",

    additionalInfo: {

      language: "Tigre",

    },

  },

  {

    uuid: "d9520168-addf-4425-8b9e-f2643150a246",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14137",

      name: "N",

      uuid: "837e552b-8a56-4543-a018-b72af9d0e9f0",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Physiotherapy",

      uuid: "3c47fdb5-846c-4f19-87c5-c66b967b8b47",

    },

    provider: {

      name: "Hl S",

      uuid: "98091484-ccd9-46de-bc7d-3b503d5b5556",

    },

    location: null,

    startDateTime: 1617696000000,

    endDateTime: 1617699600000,

    appointmentKind: "Scheduled",

    status: "Cancelled",

    comments: null,

    additionalInfo: {

      language: "Nuer",

    },

  },

  {

    uuid: "41593020-9c32-401c-84f9-d0c821742873",

    appointmentNumber: "0000",

    patient: {

      identifier: "A07271",

      name: "Y",

      uuid: "303ef2bc-ff66-4140-b303-93975ecf6f5f",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Psychology",

      uuid: "71690f76-7e90-4215-8cda-5bd81a3ea511",

    },

    provider: {

      name: "Ga os",

      uuid: "db7814c9-550d-4013-b8f9-660c28b0bc1a",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Cancelled",

    comments: null,

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "d1475052-8d3d-473d-87f9-117bbd78bfd4",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14678",

      name: "A",

      uuid: "59621239-a59c-44a8-b724-7f9f44b1d664",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "R Rn",

      uuid: "74dea2ee-a2eb-4c78-be86-013948fd952b",

    },

    location: null,

    startDateTime: 1617696000000,

    endDateTime: 1617699600000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Physical",

    additionalInfo: {

      language: "Somali",

    },

  },

  {

    uuid: "bbd87959-1c58-440f-811f-0d9712f4cf65",

    appointmentNumber: "0000",

    patient: {

      identifier: "A10229",

      name: "D",

      uuid: "3b683baa-5045-4465-8a0b-d3cc0c0d4222",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "M",

      uuid: "8cfd8913-1725-48db-96e7-b77d72b44c42",

    },

    location: null,

    startDateTime: 1617692400000,

    endDateTime: 1617696000000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Physical Appointment-Oromo",

    additionalInfo: {

      language: "Oromo",

    },

  },

  {

    uuid: "e786a072-42a0-4eaa-b575-2ed293490ba2",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14684",

      name: "S",

      uuid: "d1f53108-1bb9-4340-9bfc-031c55be46e6",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Physiotherapy",

      uuid: "3c47fdb5-846c-4f19-87c5-c66b967b8b47",

    },

    provider: {

      name: "M F",

      uuid: "7a349fe3-5080-4e76-a1a4-069cada9bfe7",

    },

    location: null,

    startDateTime: 1617696000000,

    endDateTime: 1617699600000,

    appointmentKind: "Scheduled",

    status: "Missed",

    comments: null,

    additionalInfo: {

      language: "Tigrinya",

    },

  },

  {

    uuid: "d194d549-1249-4e13-8162-e6c6fd6ac92b",

    appointmentNumber: "0000",

    patient: {

      identifier: "A00000",

      name: "NotAvilable",

      uuid: "3c4aff88-8159-42bc-9bc0-5686c0211e6b",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Medical",

      uuid: "78204b5d-568e-4fd7-9b8e-477d21a32616",

    },

    provider: {

      name: "F H",

      uuid: "02e5c612-6967-49ba-8871-8722b1da15d2",

    },

    location: null,

    startDateTime: 1617703200000,

    endDateTime: 1617706800000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Nako\nminor\nArabic\nSTARS",

    additionalInfo: {},

  },

  {

    uuid: "807604d4-cd66-4cc8-ac8b-db3113d6950b",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14827",

      name: "RA",

      uuid: "1c47fcf6-f697-47e1-b55b-cfdaa2102671",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Psychology",

      uuid: "71690f76-7e90-4215-8cda-5bd81a3ea511",

    },

    provider: {

      name: "Ar My",

      uuid: "ed8fcada-bdc5-4372-8a63-0da52d08801c",

    },

    location: null,

    startDateTime: 1617703200000,

    endDateTime: 1617706800000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "S",

    },

  },

  {

    uuid: "845559f5-adf9-4d78-aa7a-532ce34ea75a",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14826",

      name: "M",

      uuid: "9117345b-02db-4543-a558-dc85586972cc",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Psychology",

      uuid: "71690f76-7e90-4215-8cda-5bd81a3ea511",

    },

    provider: {

      name: "A Hah",

      uuid: "9307a828-4b4e-4e88-a15b-b12f757ab50f",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Tigrinya",

    },

  },

  {

    uuid: "0f3879ba-7eaa-472b-9f80-89312abd5119",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14137",

      name: "N",

      uuid: "837e552b-8a56-4543-a018-b72af9d0e9f0",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "D E",

      uuid: "cdbb1873-bd97-4b7f-9765-97e1ef396c28",

    },

    location: null,

    startDateTime: 1617703200000,

    endDateTime: 1617706800000,

    appointmentKind: "Scheduled",

    status: "Missed",

    comments: "NA - physical _ Nur - green",

    additionalInfo: {

      language: "Nuer",

    },

  },

  {

    uuid: "af6b0454-11c1-4d32-aba7-0462bb66cb15",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14698",

      name: "S",

      uuid: "acaa9a44-abf5-4412-9aa1-2c466644ec0b",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Medical",

      uuid: "78204b5d-568e-4fd7-9b8e-477d21a32616",

    },

    provider: {

      name: "N H",

      uuid: "f20d6d30-89ec-4b53-bba9-4c2604782de1",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Cancelled",

    comments: "MDT",

    additionalInfo: {

      language: "Oromo",

    },

  },

  {

    uuid: "0a22cdb8-abe5-4c1d-bdc1-e3dfc00e1b97",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14705",

      name: "K",

      uuid: "2c340685-c91b-440d-aa4b-aec426632ef7",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Medical",

      uuid: "78204b5d-568e-4fd7-9b8e-477d21a32616",

    },

    provider: {

      name: "M Ma",

      uuid: "877962ea-097e-4067-8c2f-985bcfb85bbe",

    },

    location: null,

    startDateTime: 1617715800000,

    endDateTime: 1617717600000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "MDT",

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "53870676-7845-4e08-a78e-6a94d4ed7341",

    appointmentNumber: "0000",

    patient: {

      identifier: "A13281",

      name: "F",

      uuid: "a7fea9f6-3560-4006-b031-c94ec6309681",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Physiotherapy",

      uuid: "3c47fdb5-846c-4f19-87c5-c66b967b8b47",

    },

    provider: {

      name: "A S",

      uuid: "c55fef28-f26f-450b-a77f-2e6658523860",

    },

    location: null,

    startDateTime: 1617717600000,

    endDateTime: 1617721200000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Somali",

    },

  },

  {

    uuid: "defb5349-b361-48e8-9221-5e0530e05315",

    appointmentNumber: "0000",

    patient: {

      identifier: "A13600",

      name: "S",

      uuid: "44bf33ef-f3c2-418e-abca-14e1c335aa7c",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Physiotherapy",

      uuid: "3c47fdb5-846c-4f19-87c5-c66b967b8b47",

    },

    provider: {

      name: "Ad Sun",

      uuid: "c55fef28-f26f-450b-a77f-2e6658523860",

    },

    location: null,

    startDateTime: 1617692400000,

    endDateTime: 1617696000000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Oromo",

    },

  },

  {

    uuid: "b9e1b9a9-0177-42ac-b1a1-eceaa5825ed6",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14698",

      name: "S",

      uuid: "acaa9a44-abf5-4412-9aa1-2c466644ec0b",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Psychology",

      uuid: "71690f76-7e90-4215-8cda-5bd81a3ea511",

    },

    provider: {

      name: "E A",

      uuid: "ba06748e-36b5-4076-93a4-b0c103f51701",

    },

    location: null,

    startDateTime: 1617699600000,

    endDateTime: 1617703200000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Oromo",

    },

  },

  {

    uuid: "d413f7a4-966c-4d3a-8862-d4c6449159a3",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14680",

      name: "T",

      uuid: "af242ad6-9898-4fbe-b9fa-a5db30375958",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "G A",

      uuid: "2276ce0d-7bf0-4601-b3ff-d792c1edbf62",

    },

    location: null,

    startDateTime: 1617696000000,

    endDateTime: 1617699600000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Physical",

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "eb27c94f-ceda-4076-b927-ae8de45abca4",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14863",

      name: "N",

      uuid: "a1590114-857b-46f5-ae7a-b69ce633ab25",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "G A",

      uuid: "2276ce0d-7bf0-4601-b3ff-d792c1edbf62",

    },

    location: null,

    startDateTime: 1617706800000,

    endDateTime: 1617710400000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Physical",

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "9078e877-ae73-4b2f-89c8-b049ab0d539b",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14698",

      name: "S",

      uuid: "acaa9a44-abf5-4412-9aa1-2c466644ec0b",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Medical",

      uuid: "78204b5d-568e-4fd7-9b8e-477d21a32616",

    },

    provider: {

      name: "S G",

      uuid: "07986d69-7708-4fab-97db-4cf022b7bdf8",

    },

    location: null,

    startDateTime: 1617703200000,

    endDateTime: 1617706800000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "Su",

    additionalInfo: {

      language: "Oromo",

    },

  },

  {

    uuid: "ac391c9a-b4a7-4cce-9b2e-c858a54c81e5",

    appointmentNumber: "0000",

    patient: {

      identifier: "A13017",

      name: "F",

      uuid: "2a5db338-3382-4540-a969-cf3ac6318e94",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: null,

    provider: {

      name: "Ha R",

      uuid: "ad5158fa-b81a-4c97-8cee-d24e72e7ffe3",

    },

    location: null,

    startDateTime: 1617715800000,

    endDateTime: 1617717600000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: "su",

    additionalInfo: {

      language: "Arabic",

    },

  },

  {

    uuid: "149c587d-b17a-442f-8d9b-d497905eccd0",

    appointmentNumber: "0000",

    patient: {

      identifier: "A14001",

      name: "ra",

      uuid: "0a835633-ef18-482c-b7d3-5634c9e4b637",

    },

    service: {

      appointmentServiceId: 7,

      name: "New Assessment",

      description: null,

      speciality: {},

      startTime: "08:00:00",

      endTime: "21:00:00",

      maxAppointmentsLimit: null,

      durationMins: null,

      location: {},

      uuid: "2def7a59-c309-4e4b-bd71-a7385d340011",

      color: "#FF1493",

      creatorName: null,

    },

    serviceType: {

      duration: 60,

      name: "NA - Social Worker",

      uuid: "24c08c40-6d57-4cd7-88c5-4c5eb61bbc7b",

    },

    provider: {

      name: "M R",

      uuid: "3994dd78-e145-436e-b9eb-33966f0ba9d9",

    },

    location: null,

    startDateTime: 1617717600000,

    endDateTime: 1617721200000,

    appointmentKind: "Scheduled",

    status: "Completed",

    comments: null,

    additionalInfo: {

      language: "Arabic",

    },

  },

];

mksd · April 29, 2021, 9:31am

@mahmoud2020 you mean that this returns 0 records?

SELECT * FROM patient_appointment WHERE uuid = '287068df-81e9-4151-af7c-516e2525ce0d';

mahmoud2020 · April 29, 2021, 9:38am

It returns the record, that was a mistake. I meant I could found it in the database but not the excel sheet still.

mksd · April 29, 2021, 10:59am

Is this specific appointment of the kind ‘Scheduled’?
Is its start datetime in the boundaries of your reporting period?

My point is: it’s there, but not being reported, so there must be criteria in your report’s query that filter it out. The first place to look is the WHERE clause. In other words, if you remove criteria from you WHERE, does it start showing up?

(Step 1) Start with that:

WHERE
  DATE(pai.start_date_time) BETWEEN DATE('#startDate#') AND DATE('#endDate#')
  AND pai.appointment_kind='Scheduled'
  AND pai.appointment_service_id IN
  (
    SELECT appointment_service_id
    FROM appointment_service
    where voided = 0
  )
  AND identifierOfPatient.identifier not in ('A00000')/*removing dummy patient from reports*/

(Step 2) Try that:

WHERE
  DATE(pai.start_date_time) BETWEEN DATE('#startDate#') AND DATE('#endDate#')
  AND pai.appointment_service_id IN
  (
    SELECT appointment_service_id
    FROM appointment_service
    where voided = 0
  )
  AND identifierOfPatient.identifier not in ('A00000')/*removing dummy patient from reports*/

(Step 3) Try that:

WHERE
  DATE(pai.start_date_time) BETWEEN DATE('#startDate#') AND DATE('#endDate#')
  AND identifierOfPatient.identifier not in ('A00000')/*removing dummy patient from reports*/

… etc.