linqpad/CaseType Analytics/CaseType Analysis.linq

<Query Kind="Program">
  <Connection>
    <ID>77b65bea-e766-4e3c-a1ed-c4c2534b6019</ID>
    <Persist>true</Persist>
    <Server>D0001-SQL-11.sdt.local</Server>
    <Database>jazz dev master Prod</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <Reference>&lt;ProgramFilesX64&gt;\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
  <NuGetReference>Amazon.Lambda.S3Events</NuGetReference>
  <NuGetReference>AWSSDK.Core</NuGetReference>
  <NuGetReference>AWSSDK.S3</NuGetReference>
  <Namespace>Amazon</Namespace>
  <Namespace>Amazon.Auth.AccessControlPolicy</Namespace>
  <Namespace>Amazon.Auth.AccessControlPolicy.ActionIdentifiers</Namespace>
  <Namespace>Amazon.Internal</Namespace>
  <Namespace>Amazon.Lambda.S3Events</Namespace>
  <Namespace>Amazon.MissingTypes</Namespace>
  <Namespace>Amazon.Runtime</Namespace>
  <Namespace>Amazon.Runtime.CredentialManagement</Namespace>
  <Namespace>Amazon.Runtime.CredentialManagement.Internal</Namespace>
  <Namespace>Amazon.Runtime.EventStreams</Namespace>
  <Namespace>Amazon.Runtime.EventStreams.Internal</Namespace>
  <Namespace>Amazon.Runtime.Internal</Namespace>
  <Namespace>Amazon.Runtime.Internal.Auth</Namespace>
  <Namespace>Amazon.Runtime.Internal.Settings</Namespace>
  <Namespace>Amazon.Runtime.Internal.Transform</Namespace>
  <Namespace>Amazon.Runtime.Internal.Util</Namespace>
  <Namespace>Amazon.Runtime.SharedInterfaces</Namespace>
  <Namespace>Amazon.Runtime.SharedInterfaces.Internal</Namespace>
  <Namespace>Amazon.S3</Namespace>
  <Namespace>Amazon.S3.Encryption</Namespace>
  <Namespace>Amazon.S3.Encryption.Internal</Namespace>
  <Namespace>Amazon.S3.Internal</Namespace>
  <Namespace>Amazon.S3.IO</Namespace>
  <Namespace>Amazon.S3.Model</Namespace>
  <Namespace>Amazon.S3.Model.Internal.MarshallTransformations</Namespace>
  <Namespace>Amazon.S3.Transfer</Namespace>
  <Namespace>Amazon.S3.Util</Namespace>
  <Namespace>Amazon.Util</Namespace>
  <Namespace>Amazon.Util.Internal</Namespace>
  <Namespace>Amazon.Util.Internal.PlatformServices</Namespace>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Converters</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <Namespace>Newtonsoft.Json.Schema</Namespace>
  <Namespace>Newtonsoft.Json.Serialization</Namespace>
  <Namespace>System</Namespace>
  <Namespace>System.Diagnostics</Namespace>
  <Namespace>System.Diagnostics.Tracing</Namespace>
  <Namespace>System.IO.Compression</Namespace>
  <Namespace>ThirdParty.BouncyCastle.Asn1</Namespace>
  <Namespace>ThirdParty.BouncyCastle.Asn1.Utilities</Namespace>
  <Namespace>ThirdParty.BouncyCastle.Math</Namespace>
  <Namespace>ThirdParty.BouncyCastle.OpenSsl</Namespace>
  <Namespace>ThirdParty.BouncyCastle.Utilities.IO.Pem</Namespace>
  <Namespace>ThirdParty.Ionic.Zlib</Namespace>
  <Namespace>ThirdParty.Json.LitJson</Namespace>
  <Namespace>ThirdParty.MD5</Namespace>
  <Namespace>System.ComponentModel</Namespace>
</Query>

void Main()
{
	var caseTypes = (from fpes in FactPatientEncounterSummaries
					 join ves in VariationEncounterSavings on fpes.EncounterID equals ves.EncounterID
					 join vo in VariationOpportunities on ves.OpportunityGUID equals vo.OpportunityGUID
					 join config in Configurations on vo.ConfigurationGUID equals config.ConfigurationGUID
					 join cte in CaseTypeEntities on vo.CaseTypeEntityID equals cte.CaseTypeEntityID
					 join dct in DimCaseTypes on cte.CaseTypeGUID equals dct.MemberGUID
					 join d in DimMSDRGs on fpes.ReportingDRGID equals d.MSDRGID into da
					 from e in da.DefaultIfEmpty()
					 join f in DimAPRDRGs on fpes.APDRGID equals f.APRDRGID into fa
					 from g in fa.DefaultIfEmpty()
					 join cpt in DimCPTs on fpes.PrimaryCPTID equals cpt.CPTID into cpta
					 from ca in cpta.DefaultIfEmpty()
					 where config.FiscalYearID == 2020
					 select new
					 {
						 vo.ConfigurationGUID,
						 config.FiscalYearID,
						 dct.CaseTypeId,
						 dct.Name,
						 CCMCCCategory = e.CCMCCCategory ?? "",
						 SOI = g.APRSOI ?? "",
						 ROM = g.APRROM ?? "",
						 CPTCode = ca.CPTCode ?? "",
						 CaseTypeConfig = JsonConvert.DeserializeObject<CaseTypeDRGConfiguration>(dct.CaseTypeConfigJSON)
					 })
	.Distinct()
	.Select(t => new
	{
		t.ConfigurationGUID,
		t.FiscalYearID,
		t.CaseTypeId,
		t.Name,
		t.CCMCCCategory,
		t.SOI,
		t.ROM,
		t.CPTCode,
		t.CaseTypeConfig.AgeCohortIDInclusions,
		t.CaseTypeConfig.PhysicianIDExclusions,
		t.CaseTypeConfig.StandardDeviationExcludedTypeEnum,
		t.CaseTypeConfig.LengthOfStayThreshold,
		t.CaseTypeConfig.SOIROMInclusions,
		t.CaseTypeConfig.CCMCCCategories,
		t.CaseTypeConfig.CPTCodes,
		t.CaseTypeConfig.PatientTypeRollupIDInclusions,
		t.CaseTypeConfig.PhysicianMinVolume
	})
	.OrderBy(t => t.Name);
	caseTypes.Dump();
}

// Define other methods and classes here
public const string CCMCCCATEGORY_CC = "CC";
public const string CCMCCCATEGORY_MCC = "MCC";
public const string CCMCCCATEGORY_WO = "W/O";
public const string CCMCCCATEGORY_NA = "N/A";
public const string CCMCCCATEGORY_CCMCC = "CC/MCC";


public enum CCMCCCategoryTypes
{
	/// <summary>
	/// No Category
	/// </summary>
	[Description("")]
	None,
	/// <summary>
	/// Complication or Comorbidity
	/// </summary>
	[Description(CCMCCCATEGORY_CC)]
	CC,
	/// <summary>
	/// Major Complication or Comorbidity
	/// </summary>
	[Description(CCMCCCATEGORY_MCC)]
	MCC,
	/// <summary>
	/// Without Complication or Comorbidity OR Major Complication or Comorbidity
	/// </summary>
	[Description(CCMCCCATEGORY_WO)]
	WO,
	/// <summary>
	/// Not Available
	/// </summary>
	[Description(CCMCCCATEGORY_NA)]
	NA,
	/// <summary>
	/// Both Complication or Comorbidity AND Major Complication or Comorbidity
	/// </summary>
	[Description(CCMCCCATEGORY_CCMCC)]
	CCMCC
}

public class SOIROMInclusion
{
	public string SOI { get; }
	public string ROM { get; }

	public SOIROMInclusion(string soi, string rom)
	{
		SOI = soi;
		ROM = rom;
	}
}

public class CaseTypeDRGConfiguration
{
	public int PhysicianMinVolume { get; set; }
	public List<int> AgeCohortIDInclusions { get; set; }
	public List<int> PhysicianIDExclusions { get; set; }
	public int StandardDeviationExcludedTypeEnum { get; set; }
	public int LengthOfStayThreshold { get; set; }
	public List<int> PatientTypeRollupIDInclusions { get; set; }
	public List<SOIROMInclusion> SOIROMInclusions { get; set; }
	public HashSet<CCMCCCategoryTypes> CCMCCCategories { get; set; }
	public HashSet<string> CPTCodes { get; set; }

	public CaseTypeDRGConfiguration()
	{
		PhysicianMinVolume = 0;
		AgeCohortIDInclusions = new List<int>();
		PhysicianIDExclusions = new List<int>();
		PatientTypeRollupIDInclusions = new List<int>();
		StandardDeviationExcludedTypeEnum = 0;
		LengthOfStayThreshold = 0;
		CCMCCCategories = new HashSet<CCMCCCategoryTypes>();
		CPTCodes = new HashSet<string>();
	}

}