E2ESynapseDemo/synapseCMSddls.sql at main · kunal333/E2ESynapseDemo · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Password0~';
GO

--DROP TABLE[dbo].[States];
CREATE TABLE [dbo].[States](
	[State] [varchar](2) NULL,
	[StateKey] [int] NULL
)
WITH
(
    DISTRIBUTION = REPLICATE
)
;

--DROP TABLE[dbo].[Specialty];
CREATE TABLE [dbo].[Specialty](
	[SpecialtyDescriptionFlag] [varchar](2) NULL,
	[Year] [int] NULL,
	[YearSpecialtyKey] [int] NULL,
	[Specialty] [varchar](100) NULL
)
WITH
(
	DISTRIBUTION = REPLICATE
);

--DROP TABLE[dbo].[Providers];
CREATE TABLE [dbo].[Providers](
	[npi] [int] NULL,
	[LastName] [varchar](100) NULL,
	[FirstName] [varchar](50) NULL,
	[FullName] [varchar](150) NULL,
	[Year] [int] NULL,
	[YearNPI] [int] NOT NULL
)
WITH
(
	CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH([YearNPI])
);

--DROP TABLE[dbo].[Geography];
CREATE TABLE [dbo].[Geography](
	[City] [varchar](50) NULL,
	[State] [varchar](2) NULL,
	[Year] [int] NULL,
	[YearGeoKey] [int] NULL,
	[CityState] [varchar](50) NULL,
	[StateKey] [int] NULL
)
WITH
(
	DISTRIBUTION = REPLICATE
);

--DROP TABLE[dbo].[Drugs];
CREATE TABLE [dbo].[Drugs](
	[DrugName] [varchar](50) NULL,
	[GenericName] [varchar](50) NULL,
	[Year] [int] NULL,
	[YearDrugKey] [int] NULL
)
WITH
(
	DISTRIBUTION = REPLICATE
);

--DROP TABLE[dbo].[Details];
CREATE TABLE [dbo].[Details](
	[BeneficiaryCount] [int] NULL,
	[TotalClaimCount] [int] NULL,
	[Total30DayFillCount] [decimal](10, 2) NULL,
	[TotalDaySupply] [int] NULL,
	[TotalDrugCost] [decimal](10, 2) NULL,
	[BeneCountGe65] [int] NULL,
	[BeneCountGe65SuppressFlag] [varchar](2) NULL,
	[TotalClaimCountGe65] [int] NULL,
	[Ge65SuppressFlag] [varchar](2) NULL,
	[Total30DayFillCountGe65] [decimal](10, 2) NULL,
	[TotalDrugCostGe65] [decimal](10, 2) NULL,
	[TotalDaySupplyGe65] [int] NULL,
	[Year] [int] NULL,
	[YearNPI] [int] NULL,
	[YearGeoKey] [int] NULL,
	[YearSpecialtyKey] [int] NULL,
	[YearDrugKey] [int] NULL,
	[CostPerDay] [decimal](10, 2) NULL
)
WITH
(
	CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH([YearNPI])
);
GO

--DROP VIEW [dbo].[Agg-Drug-Specialty-State-Year];
CREATE VIEW [dbo].[Agg-Drug-Specialty-State-Year]
AS
SELECT SUM(dbo.Details.BeneficiaryCount) AS BeneficiaryCount, SUM(dbo.Details.TotalClaimCount) AS TotalClaimCount, SUM(dbo.Details.Total30DayFillCount) AS Total30DayFillCount, SUM(dbo.Details.TotalDaySupply) AS TotalDaySupply, SUM(dbo.Details.TotalDrugCost) AS TotalDrugCost, dbo.Details.Year, dbo.Geography.StateKey, dbo.Details.YearSpecialtyKey,
         dbo.Details.YearDrugKey
FROM  dbo.Details LEFT OUTER JOIN
         dbo.Geography ON dbo.Details.YearGeoKey = dbo.Geography.YearGeoKey
GROUP BY dbo.Details.Year, dbo.Geography.StateKey, dbo.Details.YearSpecialtyKey, dbo.Details.YearDrugKey
;
GO