<Query Kind="Statements">
  <Connection>
    <ID>ce6f5caf-a955-42be-a634-5eb79966aca4</ID>
    <Persist>true</Persist>
    <Server>D0017-SQL-01.sdt.local</Server>
    <Database>jazz tst Aurora 20160126.1</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <Reference>&lt;ProgramFilesX64&gt;\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Converters</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <Namespace>Newtonsoft.Json.Schema</Namespace>
  <Namespace>Newtonsoft.Json.Serialization</Namespace>
</Query>

var dataRun = from i in (
	(from fpes in FactPatientEncounterSummaries
	where fpes.DischargeDateTime > fpes.AdmitDateTime 
	&& fpes.DateofDeath >= fpes.AdmitDateTime
	&& fpes.DateofDeath >= fpes.DischargeDateTime.AddDays(-30) && fpes.DateofDeath <= fpes.DischargeDateTime.AddDays(30)
	&& fpes.AdmitDateTime >= new DateTime(2015,1,1)
	select new { fpes.FiscalYearID, fpes.EntityID, fpes.FacilityID, fpes.AttendPhysicianID, fpes.PrimaryPhysicianID, fpes.MSDRGCaseTypeFamilyID
	, fpes.AdmitDateTime, fpes.DischargeDateTime, fpes.DateofDeath, fpes.PatientID, fpes.LengthOfStay })
	.Take(10000)
)
group i by new { i.FiscalYearID, i.EntityID, i.FacilityID
, i.AttendPhysicianID
, i.PrimaryPhysicianID
, AdmitDateTimeYr = i.AdmitDateTime.Year
, AdmitDateTimeMo = i.AdmitDateTime.Month
, CaseTypeFamilyID= i.MSDRGCaseTypeFamilyID
, DischargeDateTimeYr = i.DischargeDateTime.Year
, DischargeDateTimeMo = i.DischargeDateTime.Month
, DateofDeathYr = i.DateofDeath.Year 
, DateofDeathMo = i.DateofDeath.Month 
} into g
join de in Clientdss_DimEntities on g.Key.EntityID equals de.EntityID
join df in DimFacilities on g.Key.FacilityID equals df.FacilityID
join dpAttend in DimPhysicians on g.Key.AttendPhysicianID equals dpAttend.PhysicianID
join dpPrimary in DimPhysicians on g.Key.PrimaryPhysicianID equals dpPrimary.PhysicianID
join ctf in CaseTypeFamilies on g.Key.CaseTypeFamilyID equals ctf.CaseTypeFamilyID
orderby g.Key.FiscalYearID, g.Key.EntityID, g.Key.FacilityID
select new { g.Key.FiscalYearID
//, g.Key.EntityID
, Entity = de.Name
//, g.Key.FacilityID
, Facility = df.Name
//, g.Key.AttendPhysicianID
, Attenring = dpAttend.Physician
//, g.Key.PrimaryPhysicianID
, Primary = dpPrimary.Physician
, CaseTypeFamily = ctf.Name
, g.Key.AdmitDateTimeYr
, g.Key.AdmitDateTimeMo
, g.Key.CaseTypeFamilyID
, g.Key.DischargeDateTimeYr
, g.Key.DischargeDateTimeMo
, g.Key.DateofDeathYr
, g.Key.DateofDeathMo
, MinDeath = (int)g.Min(rl => Math.Abs((rl.DateofDeath - rl.DischargeDateTime).TotalDays))
, MaxDeath = (int)g.Max(rl => Math.Abs((rl.DateofDeath - rl.DischargeDateTime).TotalDays))
, AvgDeath = (int)g.Average(rl => Math.Abs((rl.DateofDeath - rl.DischargeDateTime).TotalDays))
, DeceasedPatients = g.Count()
, AvgLOS = (int)g.Average(rl => rl.LengthOfStay) }
;

dataRun.Dump();
/*
(from i in (from data in dataRun group data by new { data.AttendPhysicianID } into g select g.First())
join dp in DimPhysicians on i.AttendPhysicianID equals dp.PhysicianID
orderby i.AttendPhysicianID
select new { i.AttendPhysicianID, Attending = dp.Physician }).Dump();

(from i in (from data in dataRun group data by new { data.PrimaryPhysicianID } into g select g.First())
join dp in DimPhysicians on i.PrimaryPhysicianID equals dp.PhysicianID
orderby i.PrimaryPhysicianID
select new { i.PrimaryPhysicianID, Primary = dp.Physician }).Dump();

(from i in (from data in dataRun group data by new { data.CaseTypeFamilyID } into g select g.First())
join ctf in CaseTypeFamilies on i.CaseTypeFamilyID equals ctf.CaseTypeFamilyID
orderby i.CaseTypeFamilyID
select new { i.CaseTypeFamilyID, CaseTypeFamily = ctf.Name }).Dump();

var result = 
from data in dataRun
join de in Clientdss_DimEntities on data.EntityID equals de.EntityID
join df in DimFacilities on data.FacilityID equals df.FacilityID
join dpAttend in DimPhysicians on data.AttendPhysicianID equals dpAttend.PhysicianID
join dpPrimary in DimPhysicians on data.PrimaryPhysicianID equals dpPrimary.PhysicianID
join ctf in CaseTypeFamilies on data.CaseTypeFamilyID equals ctf.CaseTypeFamilyID
orderby data.FiscalYearID, data.EntityID, data.FacilityID, data.AdmitDateTimeYr, data.AdmitDateTimeMo, data.CaseTypeFamilyID
select new { data.FiscalYearID, data.EntityID
, Entity = de.Name
, data.FacilityID
, Facility = df.Name
, data.AttendPhysicianID
, Attending = dpAttend.Physician
, data.PrimaryPhysicianID
, Primary = dpPrimary.Physician
, AdmitDate = $"{data.AdmitDateTimeYr:0000}/{data.AdmitDateTimeMo:00}"
, data.CaseTypeFamilyID
, CaseTypeFamily = ctf.Name
, DischargeDate = $"{data.DischargeDateTimeYr:0000}/{data.DischargeDateTimeMo:00}"
, DateOfDeath = $"{data.DateofDeathYr:0000}/{data.DateofDeathMo:00}"
, data.MinDeath
, data.MaxDeath
, data.AvgDeath
, data.DeceasedPatients
, data.AvgLOS};
*/