bd4e97c5-84e7-4c61-80ca-8d53d7876af2 true D0001-SQL-11.sdt.local jazz dev master Prod true var startDate = new DateTime(2019, 1, 1); var endDate = new DateTime(2020, 1, 1); var months = Enumerable.Range(1, 12).Select(i => new { month = i }).ToList(); var data = (from fpes in FactPatientEncounterSummaries join ctf in CaseTypeFamilies on fpes.MSDRGCaseTypeFamilyID equals ctf.CaseTypeFamilyID where fpes.DischargeDateTime >= startDate && fpes.DischargeDateTime < endDate && fpes.MSDRGCaseTypeFamilyID != 0 group fpes by new { DischargeMonth = fpes.DischargeDateTime.Month, ctf.CaseTypeFamilyID, ctf.Name } into gpes orderby gpes.Key.CaseTypeFamilyID, gpes.Key.DischargeMonth select new { LevelID = 0, Month = gpes.Key.DischargeMonth, gpes.Key.CaseTypeFamilyID, gpes.Key.Name, Volume = gpes.Select(g => g.EncounterID).Distinct().Count(), Revenue = gpes.Sum(g => g.NetRevenue), TotalCost = gpes.Sum(g => g.TotalCosts) }).ToList(); var pes = months.GroupJoin(data, m => m.month, d => d.Month, (m, d) => d.DefaultIfEmpty()).SelectMany(m => m) .OrderBy(m => m.CaseTypeFamilyID) .ThenBy(m => m.Month); pes.Dump();