34 lines
1.3 KiB
C#
34 lines
1.3 KiB
C#
<Query Kind="Statements">
|
|
<Connection>
|
|
<ID>bd4e97c5-84e7-4c61-80ca-8d53d7876af2</ID>
|
|
<Persist>true</Persist>
|
|
<Server>D0001-SQL-11.sdt.local</Server>
|
|
<Database>jazz dev master Prod</Database>
|
|
<ShowServer>true</ShowServer>
|
|
</Connection>
|
|
</Query>
|
|
|
|
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(); |