private static string ByApptStatusQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin) { string queryBrokenApts = "SELECT " + DbHelper.DateTFormatColumn("appointment.AptDateTime", "%m/%d/%Y %H:%i:%s") + " AptDateTime, " + "" + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,doctor.Abbr Doctor,hygienist.Abbr Hygienist, " + "appointment.IsHygiene IsHygieneApt "; if (hasClinicsEnabled) { queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc "; //Coalesce is Oracle compatible } queryBrokenApts += "FROM appointment " + "INNER JOIN patient ON appointment.PatNum=patient.PatNum " + "LEFT JOIN provider doctor ON doctor.ProvNum=appointment.ProvNum " + "LEFT JOIN provider hygienist ON hygienist.ProvNum=appointment.ProvHyg "; if (hasClinicsEnabled) { queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum "; } queryBrokenApts += "WHERE " + DbHelper.DtimeToDate("appointment.AptDateTime") + " BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " " + "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Broken) + " " + whereProv; if (hasClinicsEnabled) { queryBrokenApts += whereClin + " " + "ORDER BY clinic.Description,appointment.AptDateTime,patient.LName,patient.FName"; } else { queryBrokenApts += "ORDER BY appointment.AptDateTime,patient.LName,patient.FName "; } return(queryBrokenApts); }