CIGNA payment vs zip

--CIGNA SUM CHARGE vs ZIP

SELECT SUM(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP

--,NTILE(10) OVER (ORDER BY SUM(convert(float, [CHRG_AMT]))) AS Decile

  FROM [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

  group by [prov_zip]

  --order by Payment DESC 

  order by [prov_zip]

  

--CIGNA AVG CHARGE (per claim) vs ZIP

SELECT AVG(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP

--,NTILE(10) OVER (ORDER BY AVG(convert(float, [CHRG_AMT]))) AS Decile

  FROM [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

  group by [prov_zip]

  --order by Payment DESC 

  order by [prov_zip]

  

  

--CIGNA AVG CHARGE (per subscriber) vs ZIP

SELECT

AVG(Q1.Payment) AvgPayment, Q1.ZIP

FROM (

    SELECT  SUM(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP, [patient_id]

    FROM    [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

    GROUP BY [patient_id], [prov_zip]

) AS Q1

GROUP BY Q1.ZIP

order by Q1.ZIP 

Payment vs Zip and diagnosis

SELECT AVG([ Net_Paid ]) Payment, [ Employee_ZIP_Code ] ZIP,[SHORT DESCRIPTION]
FROM [SiemensHealthPlanData].[dbo].[unitedHealth],
[SiemensHealthPlanData].[dbo].[procedureCodeICD9]
where [ Primary_Diagnosis ] = [PROCEDURE CODE]
group by [ Employee_ZIP_Code ],[SHORT DESCRIPTION]



SELECT AVG([ Net_Paid ]) Payment,[SHORT DESCRIPTION]
FROM [SiemensHealthPlanData].[dbo].[unitedHealth],
[SiemensHealthPlanData].[dbo].[procedureCodeICD9]
where [ Primary_Diagnosis ] = [PROCEDURE CODE]
group by [SHORT DESCRIPTION]

UNITED Payment vs ZipCode

Employee_ZIP_Code 

--UNITED SUM CHARGE vs ZIP
SELECT SUM([ Net_Paid ]) Payment, [ Employee_ZIP_Code ] ZIP,
NTILE(10) OVER (ORDER BY SUM([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Employee_ZIP_Code ]
  order by Payment DESC

--UNITED AVG CHARGE (per claim) vs ZIP
SELECT AVG([ Net_Paid ]) Payment, [ Employee_ZIP_Code ] ZIP,
NTILE(10) OVER (ORDER BY AVG([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Employee_ZIP_Code ]
  order by Payment DESC

 

--UNITED AVG CHARGE (per subscriber) vs ZIP
SELECT
AVG(Q1.Payment) AvgPayment, Q1.ZIP
FROM (
    SELECT  SUM([ Net_Paid ]) Payment, [ Employee_ZIP_Code ] ZIP
    FROM    [SiemensHealthPlanData].[dbo].[unitedHealth]
    GROUP BY [Policy_Number ],
      [ Medco_SSN ]
      ,[ Employee_ID ]
      ,[ Member_Last_Name ]
      ,[ Employee_Sex ]
      ,[ Employee_Date_of_Birth ]
      ,[ Employee_ZIP_Code ]
      ,[ Dependent_Number ]
      ,[ Member_Relationship_Code ]
      ,[ Member_First_Name ]
      ,[ Member_Date_of_Birth ]
      ,[ Member_Sex ]
) AS Q1
GROUP BY Q1.ZIP
order by Q1.ZIP

Provider_ZIP_Code 

--UNITED SUM CHARGE vs ZIP
SELECT SUM([ Net_Paid ]) Payment, [ Provider_ZIP_Code ] ZIP,
NTILE(10) OVER (ORDER BY SUM([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Provider_ZIP_Code ]
  order by Payment DESC
 

--UNITED AVG CHARGE (per claim) vs ZIP
SELECT AVG([ Net_Paid ]) Payment, [ Provider_ZIP_Code ] ZIP,
NTILE(10) OVER (ORDER BY AVG([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Provider_ZIP_Code ]
  order by Payment DESC
 
 
--UNITED AVG CHARGE (per patient) vs ZIP
SELECT
AVG(Q1.Payment) AvgPayment, Q1.ZIP
FROM (
    SELECT  SUM([ Net_Paid ]) Payment, [ Provider_ZIP_Code ] ZIP
    FROM    [SiemensHealthPlanData].[dbo].[unitedHealth]
    GROUP BY [Policy_Number ],
      [ Medco_SSN ]
      ,[ Employee_ID ]
      ,[ Member_Last_Name ]
      ,[ Employee_Sex ]
      ,[ Employee_Date_of_Birth ]
      ,[ Employee_ZIP_Code ]
      ,[ Dependent_Number ]
      ,[ Member_Relationship_Code ]
      ,[ Member_First_Name ]
      ,[ Member_Date_of_Birth ]
      ,[ Member_Sex ],
      [ Provider_ZIP_Code ]
) AS Q1
GROUP BY Q1.ZIP
order by Q1.ZIP

Sum of Payment per ZipCode, ordered

SELECT SUM([ Net_Paid ]) Payment,
[ Employee_ZIP_Code ] ZIP,
NTILE(10) OVER (ORDER BY SUM([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Employee_ZIP_Code ]

Patient percentile vs Net paid

SELECT SUM([ Net_Paid ]) Payment,
NTILE(10) OVER (ORDER BY SUM([ Net_Paid ])) AS Decile
  FROM [SiemensHealthPlanData].[dbo].[unitedHealth]
  group by [ Employee_Sex ]
      ,[ Employee_ZIP_Code ]
      ,[ Employee_Status ]
      ,[ Employee_Date_of_Birth ]
      ,[ Dependent_Number ]
      ,[ Member_Relationship_Code ]
      ,[ Member_Date_of_Birth ]
      ,[ Member_Sex ]
      ,[ Member_Market ]  

Payor

SELECT distinct DRG_CD

from [SiemensHealthPlanData].[dbo].[unitedHealth9digitsDrugCode] as digits

where DRG_CD !='' and not exists(

select *

from [SiemensHealthPlanData].[dbo].[nationalDrugCode] as code

where digits.DRG_CD = code.DRG_CD

)



select COUNT(*)

from [SiemensHealthPlanData].[dbo].[unitedHealth9digitsDrugCode]

where DRG_CD != ''





SELECT [key]

,digits.[DRG_CD], Description

FROM [SiemensHealthPlanData].[dbo].[unitedHealth9digitsDrugCode] as digits,

[SiemensHealthPlanData].[dbo].[nationalDrugCode] as code

where digits.DRG_CD = code.DRG_CD