-
Notifications
You must be signed in to change notification settings - Fork 0
/
pullAllData.R
72 lines (69 loc) · 4.69 KB
/
pullAllData.R
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
pullAllData <- function (FY)
{
### SQL connection
con <- RPostgres::dbConnect(
RPostgres::Postgres(),
dbname = "d629vjn37pbl3l",
host = "ec2-3-209-200-73.compute-1.amazonaws.com",
port = 5432,
user = "reason_readonly",
password = "p88088bd28ea68027ee96c65996f7ea3b56db0e27d7c9928c05edc6c23ef2bc27",
sslmode = "require")
list <- c("year",
"plan_id",
"display_name",
"state",
"employee_contribution_dollar",
"actuarial_funded_ratio_percentage",
"actuarially_required_contribution_dollar",
"actuarially_accrued_liabilities_dollar",
"employer_state_contribution_dollar",
"type_of_employees_covered",
"discount_rate_assumption",
"covered_payroll_dollar",
"actuarial_cost_method_in_gasb_reporting",
"actuarial_value_of_assets_gasb_dollar",
"number_of_years_remaining_on_amortization_schedule",
"actuarially_required_contribution_paid_percentage",
"market_assets_reported_for_asset_smoothing",
"total_pension_liability_dollar",
"total_benefits_paid_dollar",
"fiscal_year_of_contribution",
"fiscal_year_end_date",
"asset_valuation_method_for_gasb_reporting",
"employer_contribution_regular_dollar",
"total_normal_cost_percentage",
"unfunded_actuarially_accrued_liabilities_dollar",
"employee_normal_cost_percentage",
"total_amortization_payment_percentage",
"total_normal_cost_dollar",
"statutory_payment_dollar",
"payroll_growth_assumption",
"employers_projected_actuarial_required_contribution_percentage_of_payroll",
"amortizaton_method",
"wage_inflation",
"refunds_dollar",
"market_investment_return_mva_basis",
"market_value_of_assets_dollar",
"statutory_payment_percentage",
"investment_return_assumption_for_gasb_reporting",
"administrative_expense_dollar",
"benefit_payments_dollar",
"total_contribution_dollar",
"x1_year_investment_return_percentage",
"other_contribution_dollar",
"employer_normal_cost_dollar")
#### SQL query
query <- paste("select * from pull_data_state_only()\nwhere year > '",
paste(FY - 1), "'")
result <- RPostgres::dbSendQuery(con, query)
all_data <- RPostgres::dbFetch(result) %>% janitor::clean_names()
RPostgres::dbClearResult(result)
RPostgres::dbDisconnect(con)
all_data %>% dplyr::group_by_at(dplyr::vars(-.data$attribute_value)) %>%
dplyr::mutate(row_id = 1:dplyr::n()) %>% dplyr::ungroup() %>%
tidyr::pivot_wider(names_from = attribute_name, values_from = attribute_value) %>%
dplyr::select(-.data$row_id) %>% dplyr::arrange(display_name,
year) %>% janitor::clean_names() %>%
dplyr::select(print(list))
}