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();
}