private string GenerateSqlQuery(Medical.Models.MedicalRequestModel m)
{
var strat = m.AgeStratification == null ? null : Stratifications.Find(m.AgeStratification.Value, "age");
if (strat == null)
{
return(null);
}
var stratValues = strat.ClassificationText.Split(',');
var codes = string.Join(",", m.Codes.Split(',').Select(c => string.Format("'{0}'", c.Trim())));
return(string.Format(@"
Select
SummaryData.*,
EnrollmentData.EnrollmentMembers as [Total Enrollment in Strata(Members)],
round(SummaryData.Members / EnrollmentData.EnrollmentMembers * 1000, 1) as [Prevalence Rate (Users per 1000 enrollees)],
round(SummaryData.Events / EnrollmentData.EnrollmentMembers * 1000, 1) as [Event Rate (Events per 1000 enrollees)],
round(SummaryData.Events/SummaryData.Members ,1) as [Events Per member]
From
(
Select
AgeGroup, gender as Sex, Period, Code as DXCode, DXName, Setting,
Sum(Event) as Events, Sum(Member) as Members
From ({0}) as OuterTable
Group by AgeGroup, gender, Period, Code, DXName,Setting
)
as SummaryData
Left JOIN
(
Select AgeGroup, gender as Sex, Year, Sum(Member) as EnrollmentMembers
From ({1}) as OuterEnrollmentTable
Group by AgeGroup, gender, Year
)
as EnrollmentData
ON
EnrollmentData.AgeGroup = SummaryData.Agegroup AND
EnrollmentData.sex = Summarydata.Sex AND
EnrollmentData.Year = Summarydata.Period
",
string.Join("\n Union All \n", stratValues.Select(v => GenerateDiagQuery(v, codes))),
string.Join("\n Union All \n", stratValues.Select(GenerateEnrollmentQuery))
));
}
