Start date: 99 June 9999

1) Request info

Requestor Name: Ninety Niner

Email Address:

Phone: 999-999-9999

Details: We’re looking for the first and last names of employees who attended 99 so we can…[fill in the blank]

Date Needed: 9999-19-09

2) Some SQL tables definitions

2.1) PEBEMPL_INTERNAL_FT_PT_IND

Internal Part Time/Full Time Indicator, used for EEO reporting. Valid values are F - Full-time, P - Part-time, O - Other.

2.2) PEBEMPL_EMPL_STATUS

Status of the job. Valid values are A = Active, B = Leave without pay with benefits, L = Leave without pay without benefits, F = Leave with full pay and benefits, P = Leave with partial pay and benefits, T = Terminated.

2.3) PEBEMPL_TERM_DATE

Termination date of the employee.

2.4) NBBPOSN_TYPE

Single or pooled position.


SELECT DISTINCT NBRJOBS_PIDM,NBRJOBS_DESC,NBRJOBS_APPT_PCT,NBRJOBS_EFFECTIVE_DATE,PEBEMPL_EMPL_STATUS, PEBEMPL_TERM_DATE,PEBEMPL_INTERNAL_FT_PT_IND,NBBPOSN_TYPE,NBBPOSN_TITLE
  
FROM PEBEMPL

JOIN NBRJOBS
ON NBRJOBS_PIDM=PEBEMPL_PIDM
AND NBRJOBS_STATUS = PEBEMPL_EMPL_STATUS

JOIN NBBPOSN
ON NBRJOBS_POSN = NBBPOSN_POSN
AND NBRJOBS_STATUS = NBBPOSN_STATUS

WHERE NBRJOBS_DESC != 'Work Study'

SELECT DISTINCT SPRIDEN_PIDM,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,SHRDGMR_DEGS_CODE, SHRDGMR_GRAD_DATE,STVMAJR_DESC,SHRDGMR_MAJR_CODE_1
  
FROM SHRDGMR

JOIN SPRIDEN
ON SPRIDEN_PIDM = SHRDGMR_PIDM

JOIN STVMAJR
ON SHRDGMR_MAJR_CODE_1 = STVMAJR_CODE

WHERE SHRDGMR_DEGS_CODE = 'AW'

AND SPRIDEN_CHANGE_IND IS NULL
## SQL query
query=tbl(db_connection,dbplyr::sql("
SELECT *
  
FROM NBRJOBS

LEFT JOIN SFRSTCR
ON NBRJOBS_PIDM = SFRSTCR_PIDM

JOIN SPRIDEN
ON SPRIDEN_PIDM = NBRJOBS_PIDM

JOIN STVTERM
ON SFRSTCR_TERM_CODE = STVTERM_CODE

WHERE SPRIDEN_CHANGE_IND IS NULL

"))
JOINED = SQL_EMPLOYED |> 
  left_join(SQL_ALUMNI,by=join_by(NBRJOBS_PIDM==SPRIDEN_PIDM)) |> 
  select(SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME,SPRIDEN_MI,NBRJOBS_DESC,NBBPOSN_TITLE,everything())
#include PT or FT status
JOINED1 = JOINED |> 
  #filter(PEBEMPL_INTERNAL_FT_PT_IND =='F') |> 
  filter(SHRDGMR_DEGS_CODE=='AW' & PEBEMPL_EMPL_STATUS =='A'& NBBPOSN_TYPE == 'S') |> 
  mutate(Graduation_Date = ymd(SHRDGMR_GRAD_DATE)) |> 
  mutate(FTE = case_when(PEBEMPL_INTERNAL_FT_PT_IND == 'F' ~ "Yes",
                               TRUE ~ "No")) |> 
  arrange(desc(NBRJOBS_EFFECTIVE_DATE)) |> 
  distinct(NBRJOBS_PIDM,.keep_all = T) |> 
  arrange(SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME) |> 
  select(First_Name=SPRIDEN_FIRST_NAME,Middle_Initial =SPRIDEN_MI,Last_Name = SPRIDEN_LAST_NAME,Job_Description = NBRJOBS_DESC,Internal_Job_Title = NBBPOSN_TITLE,Program_Major=STVMAJR_DESC,Graduation_Date,everything())

3) CCTC alumni employed at CCTC

Sorted by last names alphabetically and can be filtered by full/part time employment.

The table is exportable to Excel.

set.seed(12)
# Shuffle categorical variables
# Remove name columns entirely

JOINED2 = JOINED1 |> 
  select(First_Name, Middle_Initial, Last_Name, Job_Description, Program_Major, FTE) |>
  mutate(Person_ID = paste0("Someone_", row_number()),
    Job_Description = sample(Job_Description),
    Program_Major = sample(Program_Major)) |>
  select(Person_ID, Job_Description, Program_Major, FTE)
JOINED2 |>datatable()

3.1) Export as Excel

downloadthis::download_this(JOINED2,output_name = 'CCTC_alumni_employed',output_extension = '.xlsx')

3.2) Which programs did CCTC employees graduate from?

JOINED1 %>%
  group_by(Program_Major) %>%
  summarise(HEADCOUNT = n(), .groups = "drop") %>%
  arrange(desc(HEADCOUNT)) %>%
  ggplot(aes(y = reorder(Program_Major, HEADCOUNT), x = HEADCOUNT,fill=HEADCOUNT)) +
  geom_col() +
  labs(title = "Headcount by Major",
       y = " ",
       x = "Headcount",
       fill= " ") +
  theme_bw()