112 lines
4.8 KiB
C#
112 lines
4.8 KiB
C#
<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><ProgramFilesX64>\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};
|
|
*/
|