private void includeRegDate(SQLStringBuilder queryString, bool filterByDate) { var subQuery = new SQLStringBuilder(); subQuery.Scope("Registration Dates", () => { string[] subColumns = { "learnerKey", "MIN(regDate) as theRegDate" }; subQuery.Select(subColumns); subQuery.From("enrollments"); subQuery.GroupBy("learnerKey"); if (filterByDate) { filterHavingByDate(subQuery); } }); if (filterByDate) { queryString.RightJoin(String.Format("{0} as e", subQuery.ToString()), "e.LearnerKey = ld.LearnerKey"); } else { queryString.LeftJoin(String.Format("{0} as e", subQuery.ToString()), "e.LearnerKey = ld.LearnerKey"); } }
private string buildCondensedQuery() { SQLStringBuilder queryString = new SQLStringBuilder(); var report = new rptLearnerStatusComprehensive(); selectCondensedColumns(queryString); includeRegDate(queryString, UseDate); includeDivision(queryString); limitToRegisteredEnrollments(queryString); if (ShouldFilterByStatus) { filterByStatus(queryString); } if (!IncludeECD) { excludeECD(queryString); } if (ShouldFilterByCollege) { filterByCollege(queryString); } groupByDivision(queryString); orderByLearnerNumber(queryString); return queryString.ToString(); }