select p.personId_pk as 'Patient ID',concat(p.lastname,' ',p.middlename,' ',p.firstname) as 'Patient Name',
       h.name as 'Health Care Facility Name',concat(pr.lastname,' ',pr.middlename,' ',pr.firstname) as 'Practitioner Name',
       sa.occuranceDateTime as 'Date & Time',nc.shortDescription as 'System Notifications'
from person p, person pr,healthCareProvider h,healthCareProviderStaff ph,patientPractitionerHealthcareProvider pph,personrole pl,personContact pc,systemAlertInfo sa,notificationConfig nc,patienthealthcareprovider phcp
  where (pph.healthCareProviderStaff_fk = ph.healthCareProviderStaff_pk and ph.personid_fk = pr.personid_pk and pl.roletype_fk = 2)
       and pph.patientid_fk = p.personid_pk and (phcp.statusCode = 'A' and phcp.patientId_fk = p.personId_pk)
       and h.healthCareProvider_pk = ph.healthCareProvider_fk and p.personId_pk = pc.personId_fk and sa.patientId_fk = p.personId_pk
       and (sa.notificationConfig_fk=nc.notificationConfig_pk and sa.patientId_fk = p.personId_pk)
       and (sa.occuranceDateTime IN (select max(occuranceDateTime) from systemAlertInfo where datediff(now(),occuranceDateTime)< 8 and    group by patientId_fk)) group by p.personId_pk;

               Criteria criteria = session
            .createCriteria(Person.class)
            .createAlias("personrolesByPersonIdFk", "person")
            .createAlias("person.roletype", "rtype")
            .createAlias(
               "patienthealthcareprovidersByPatientIdFk",
               "patHealthcareProv")
            .add(Expression.eq("rtype.roleTypePk", 1))
            .createAlias("systemalertinfosByPatientIdFk",
                  "patNotification")
            .createAlias("patNotification.notificationconfig",
                  "notifyConfig")
            .add(Expression.eq("patHealthcareProv.statuscode", STATUS))
            .add(Expression.eq("notifyConfig.category", CATEGORY))
            .add(Expression.between("patNotification.occuranceDateTime",
                  dateEarly, dateLater))
            .setProjection(
               Projections
               .distinct(Projections
                   .property("patNotification.personByPatientIdFk")));