
----MODIFY QUERY PARAMATERS BELOW---

CREATE  DATABASE "Testqueryfix1"
--Enter the Name you'd like to assign to your Summary Table Database. The name should be in " ". 
GO
USE "Testqueryfix1"
--Enter the same name you used for the create database paramater above. The name should be in " ".

---------------------Do not modify anything below this line---------------------------------------------

CREATE TABLE AGE_GROUPS
(
    id int NULL,
    strat10_name NVARCHAR(255) NULL,
    strat10_sort_order int NULL,
	strat7_name NVARCHAR(255) NULL,
	strat7_sort_order int Null,
	strat4_name NVARCHAR(255) NULL,
	strat4_sort_order int null,
	strat2_name NVARCHAR(255) null,
	strat2_sort_order int NULL
	
)

CREATE TABLE DRUG_CLASS
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	drugclass NVARCHAR(255) NULL,
	members int NULL,
	dispensings int NULL,
	dayssupply int NULL,
	age_group_id int NULL

)

CREATE TABLE ENROLLMENT
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	year NVARCHAR(255) NULL,
	MedCov NVARCHAR(255) NULL,
	DrugCov NVARCHAR(255) NULL,
	dayscovered bigint NULL,
	members int NULL,
	age_group_id int NULL

)

CREATE TABLE GENERIC_NAME
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	genericname NVARCHAR(255) NULL,
	members int NULL,
	dispensings int NULL,
	dayssupply int NULL,
	age_group_id int NULL

)

CREATE TABLE HCPCS
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	px_code NVARCHAR(255) NULL,
	pxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE ICD9_DIAGNOSIS
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	code NVARCHAR(255) NULL,
	dxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE ICD9_DIAGNOSIS_4_DIGIT
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	code NVARCHAR(255) NULL,
	dxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE ICD9_DIAGNOSIS_5_DIGIT
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	code NVARCHAR(255) NULL,
	dxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE ICD9_PROCEDURE
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	code NVARCHAR(255) NULL,
	pxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE ICD9_PROCEDURE_4_DIGIT
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	px_code NVARCHAR(255) NULL,
	pxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members int NULL,
	events int NULL,
	age_group_id int NULL

)

CREATE TABLE INCIDENT_DRUG_CLASS
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	drugclass NVARCHAR(255) NULL,
	members90 int NULL,
	Episodespan90 int NULL,
	Dispensings90 int NULL,
	Dayssupply90 int NULL,
	Members90Q1 int NULL,
	Members90Q2 int NULL,
	Members90Q3 int NULL,
	Members90Q4 int NULL,
	members180 int NULL,
	Episodespan180 int NULL,
	Dispensings180 int NULL,
	Dayssupply180 int NULL,
	Members180Q1 int NULL,
	Members180Q2 int NULL,
	Members180Q3 int NULL,
	Members180Q4 int NULL,
	members270 int NULL,
	Episodespan270 int NULL,
	Dispensings270 int NULL,
	Dayssupply270 int NULL,
	Members270Q1 int NULL,
	Members270Q2 int NULL,
	Members270Q3 int NULL,
	Members270Q4 int NULL,
	age_group_id int NULL
	
)

CREATE TABLE INCIDENT_GENERIC_NAME
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	genericname NVARCHAR(255) NULL,
	members90 int NULL,
	Episodespan90 int NULL,
	Dispensings90 int NULL,
	Dayssupply90 int NULL,
	Members90Q1 int NULL,
	Members90Q2 int NULL,
	Members90Q3 int NULL,
	Members90Q4 int NULL,
	members180 int NULL,
	Episodespan180 int NULL,
	Dispensings180 int NULL,
	Dayssupply180 int NULL,
	Members180Q1 int NULL,
	Members180Q2 int NULL,
	Members180Q3 int NULL,
	Members180Q4 int NULL,
	members270 int NULL,
	Episodespan270 int NULL,
	Dispensings270 int NULL,
	Dayssupply270 int NULL,
	Members270Q1 int NULL,
	Members270Q2 int NULL,
	Members270Q3 int NULL,
	Members270Q4 int NULL,
	age_group_id int NULL
	
)

CREATE TABLE INCIDENT_ICD9_DIAGNOSIS
(

	age_group NVARCHAR(255) NULL,
	Sex NVARCHAR(255) NULL,
	period NVARCHAR(255) NULL,
	code NVARCHAR(255) NULL,
	dxname NVARCHAR(255) NULL,
	setting NVARCHAR(255) NULL,
	members90 int NULL,
	events90 int NULL,
	members180 int NULL,
	events180 int NULL,
	members270 int NULL,
	events270 int NULL,
	age_group_id int NULL

)