QUERIES USING THE OHR DATABASE
| No. | SQL Queries (Instructions) |
Required Tables (Instructions) |
Query Output |
| 1. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, dbo_cempstat.description AS [Employee Status], dbo_curr_month_employee.CODE_APPT AS [Appointment Code], dbo_cclass.description AS [Employee Classification], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_employee.NAME_POSN_ACAD_TITLE AS [Academic Title] INTO tblEmployees FROM (dbo_curr_month_employee INNER JOIN dbo_cempstat ON dbo_curr_month_employee.CODE_EMPL_STATUS = dbo_cempstat.code_value) INNER JOIN dbo_cclass ON dbo_curr_month_employee.CODE_EMPL_CLASS = dbo_cclass.code_value WHERE (((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.REPORT_DISPLAY_NAME; | curr_month_employee, cempstat, cclass | Faculty & Staff information (name, classification, hire date, appt. begin and end date) |
| 2. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.ADDR_EMPL_STREET_HOME AS [Home Street Address], dbo_curr_month_employee.ADDR_EMPL_CITY_HOME AS [Home City Address], dbo_curr_month_employee.CODE_EMPL_STATE_HOME AS [Home State Address], dbo_curr_month_employee.CODE_EMPL_ZIP_HOME_PRIME AS [Zip Code], dbo_curr_month_employee.CODE_EMPL_STATUS AS [Employment Status], dbo_curr_month_employee.NAME_POSN_ACAD_TITLE AS [Academic Title] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS]) AND ((dbo_curr_month_employee.CODE_APPT) Not In ("WAG","GRD")) AND ((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT")) ORDER BY dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL, dbo_curr_month_employee.NAME_POSN_ACAD_TITLE; | curr_month_employee | (Active) Faculty & Staff home address |
| 3. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.CODE_APPT AS [Appoint Code], dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_salary.AMNT_POSN_ANNUAL_SAL AS [Annual Salary] FROM dbo_curr_month_employee INNER JOIN dbo_curr_month_salary ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_month_salary.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_APPT) Not Like "WAG") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL; | curr_month_employee, curr_month_salary | Faculty & Staff Salary information |
| 4. | SELECT dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.CODE_POSN_RANK AS [Position Rank Code], dbo_cemprank.description AS [Position Rank], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date] FROM dbo_curr_month_employee INNER JOIN dbo_cemprank ON dbo_curr_month_employee.CODE_POSN_RANK = dbo_cemprank.code_value WHERE (((dbo_curr_month_employee.CODE_POSN_RANK) Like "A*") AND ((dbo_curr_month_employee.CODE_EMPL_STATUS)="RET") AND ((dbo_curr_month_employee.CODE_EMPL_CLASS)="ACAD") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.DATE_APPT_END; | curr_month_employee, cemprank | Retired faculty listing |
| 5. | SELECT dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.CODE_POSN_RANK AS [Position Rank Code], dbo_cemprank.description AS [Position Rank] FROM dbo_curr_month_employee INNER JOIN dbo_cemprank ON dbo_curr_month_employee.CODE_POSN_RANK = dbo_cemprank.code_value WHERE (((dbo_curr_month_employee.CODE_POSN_RANK)=[Enter the Rank: ( such as A10 for Prof, A20 for Asso Prof and so on)]) AND ((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT") AND ((dbo_curr_month_employee.CODE_EMPL_CLASS)="ACAD") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.DATE_EMPL_HIRED DESC; | curr_employee_month, cemprank | Hire date for active faculty members based on their rank |
| 6. | SELECT dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.CODE_POSN_RANK AS [Position Rank Code], dbo_cemprank.description AS [Position Rank] FROM dbo_curr_month_employee INNER JOIN dbo_cemprank ON dbo_curr_month_employee.CODE_POSN_RANK = dbo_cemprank.code_value WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT") AND ((dbo_curr_month_employee.CODE_EMPL_CLASS)="ACAD") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.DATE_EMPL_HIRED DESC; | curr_employee_month, cemprank | Hire date for all the active faculty members |
| 7. | SELECT dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_salary.AMNT_POSN_ANNUAL_SAL AS Salary, dbo_curr_month_employee.CODE_EMPL_HIDEGR AS [Highest Degree], dbo_curr_month_employee.DATE_EMPL_HIGH_DEGR AS [Degree Date], dbo_curr_month_employee.NAME_EMPL_DEGR_DSPLN AS Degree, dbo_curr_month_employee.NAME_POSN_ACAD_TITLE AS [Academic Title] FROM dbo_curr_month_employee INNER JOIN dbo_curr_month_salary ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_month_salary.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS]) AND ((dbo_curr_month_employee.CODE_POSN_RANK)="A10")) OR (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT") AND ((dbo_curr_month_employee.CODE_POSN_RANK)="A20")) OR (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT") AND ((dbo_curr_month_employee.CODE_POSN_RANK)="A30")) ORDER BY dbo_curr_month_employee.DATE_EMPL_HIRED DESC; | curr_month_employee, curr_month_salary | Report showing facultyâs highest degree date, hire date and title. |
| 8. | SELECT dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.DATE_EMPL_TENR_GRNT AS [Date Tenure Received] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.CODE_APPT)="Stn") AND ((dbo_curr_month_employee.CODE_EMPL_CLASS)="Acad") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee | Report showing the date tenure was received by all the standing appointments |
| 9. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, dbo_curr_month_employee.CODE_EMPL_STATUS AS Status, dbo_curr_month_employee.CODE_APPT AS [Appt Code], dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_employee.NAME_JOBD_TITLE_SHORT AS [Job Title], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_BEG AS [Visa Begin Date], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_END AS [Visa End Date], dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE AS [Visa Type], dbo_curr_month_employee.DATE_EMPL_I9_SIGNED AS [Date I9 Signed] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="FT1" Or (dbo_curr_month_employee.CODE_APPT)="FT2" Or (dbo_curr_month_employee.CODE_APPT)="FTM" Or (dbo_curr_month_employee.CODE_APPT)="GRD" Or (dbo_curr_month_employee.CODE_APPT)="FEL" Or (dbo_curr_month_employee.CODE_APPT)="SCH") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS]) AND ((dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE)<>"na")) ORDER BY dbo_curr_month_employee.DATE_APPT_END; | curr_month_employee | Report showing visa and I-9 information for all the employees |
| 10. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_BEG AS [Visa Begin Date], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_END AS [Visa End Date], dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE AS [Visa Type], dbo_curr_month_salary.AMNT_POSN_ANNUAL_SAL AS [Annual Salary] FROM (dbo_curr_month_employee INNER JOIN dbo_curr_month_salary ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_month_salary.NUMB_EMPL_ID) INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="SCH") AND ((dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE)<>"na") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.DATE_APPT_END; | curr_month_employee, curr_month_salary, curr_accessid | Post Doc visa type, visa end date, and salary information |
| 11. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_BEG AS [Visa Begin Date], dbo_curr_month_employee.DATE_EMPL_FNAT_VISA_END AS [Visa End Date], dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE AS [Visa Type], dbo_curr_month_salary.AMNT_POSN_ANNUAL_SAL AS [Annual Salary] FROM (dbo_curr_month_employee INNER JOIN dbo_curr_month_salary ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_month_salary.NUMB_EMPL_ID) INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE)<>"na") AND ((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="FT1" Or (dbo_curr_month_employee.CODE_APPT)="FT2" Or (dbo_curr_month_employee.CODE_APPT)="FTM" Or (dbo_curr_month_employee.CODE_APPT)="STN") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS]) AND ((dbo_curr_month_employee.CODE_EMPL_CLASS)="ACAD")) ORDER BY dbo_curr_month_employee.DATE_APPT_END; | curr_month_employee, curr_month_salary, curr_accessid | Visa information for faculty members |
| 12. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.NUMB_EMPL_PHONE_OFFC AS Phone, dbo_curr_month_employee.ADDR_EMPL_OFFC_ROOM_PHYS AS [Room Number], dbo_curr_month_employee.ADDR_EMPL_OFFC_BUILDING_PHYS AS Building, dbo_curr_month_employee.CODE_EMPL_STATUS AS Status, dbo_curr_month_employee.CODE_APPT AS [Appt Code] FROM dbo_curr_month_employee INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="FT1" Or (dbo_curr_month_employee.CODE_APPT)="FT2" Or (dbo_curr_month_employee.CODE_APPT)="FTM") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee, curr_accessid | Report showing office address, phone, email id for all Fixed Term employees |
| 13. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.NUMB_EMPL_PHONE_OFFC AS Phone, dbo_curr_month_employee.ADDR_EMPL_OFFC_ROOM_PHYS AS [Room Number], dbo_curr_month_employee.ADDR_EMPL_OFFC_BUILDING_PHYS AS Building, dbo_curr_month_employee.CODE_EMPL_STATUS AS Status FROM dbo_curr_month_employee INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="GRD") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee, curr_accessid | Report showing office address, phone, email id for all Graduate Assistants |
| 14. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.NUMB_EMPL_PHONE_OFFC AS Phone, dbo_curr_month_employee.ADDR_EMPL_OFFC_ROOM_PHYS AS [Room Number], dbo_curr_month_employee.ADDR_EMPL_OFFC_BUILDING_PHYS AS Building, dbo_curr_month_employee.CODE_EMPL_STATUS AS Status FROM dbo_curr_month_employee INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="STN") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee, curr_accessid | Report showing office address, phone, email id for all Standing Appointments |
| 15. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS Name, Trim([NUMB_PERS_ACCESS_ACCT_ID]) & '@psu.edu;' AS Email, dbo_curr_month_employee.NUMB_EMPL_PHONE_OFFC AS Phone, dbo_curr_month_employee.ADDR_EMPL_OFFC_ROOM_PHYS AS [Room Number], dbo_curr_month_employee.ADDR_EMPL_OFFC_BUILDING_PHYS AS Building, dbo_curr_month_employee.CODE_EMPL_STATUS AS Status, dbo_curr_month_employee.CODE_APPT FROM dbo_curr_month_employee INNER JOIN dbo_curr_accessid ON dbo_curr_month_employee.NUMB_EMPL_ID = dbo_curr_accessid.NUMB_EMPL_ID WHERE (((dbo_curr_month_employee.CODE_EMPL_STATUS)="ACT" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LNP" Or (dbo_curr_month_employee.CODE_EMPL_STATUS)="LWP") AND ((dbo_curr_month_employee.CODE_APPT)="FEL" Or (dbo_curr_month_employee.CODE_APPT)="SCH") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee, curr_accessid | Report showing office address, phone, email id for all Fellow and Post Docs |
| 16. | SELECT dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.CODE_EMPL_CLASS AS [Classification Code], dbo_curr_month_employee.ADDR_EMPL_STREET_HOME AS [Street Addr], dbo_curr_month_employee.ADDR_EMPL_CITY_HOME AS [City Addr], dbo_curr_month_employee.CODE_EMPL_STATE_HOME AS [State Addr], dbo_curr_month_employee.CODE_EMPL_ZIP_HOME_PRIME AS [Zip Code], dbo_curr_month_employee.NUMB_EMPL_PHONE_HOME AS [Home Phone] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.CODE_EMPL_CLASS)="ACAD" Or (dbo_curr_month_employee.CODE_EMPL_CLASS)="STFF") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])); | curr_month_employee | Home address for employees in academics and staff classification |
| 17. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.REPORT_DISPLAY_NAME AS [Empoyee Name], dbo_curr_month_employee.DATE_EMPL_HIRED AS [Hire Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date], dbo_curr_month_employee.INDC_EMPL_CZNSHP AS [US Citizenship N indicating No], dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE AS [Visa Status], dbo_curr_month_employee.NAME_JOBD_TITLE_SHORT AS [Job Title] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.DATE_APPT_END)>Date()) AND ((dbo_curr_month_employee.CODE_EMPL_FNAT_VISA_TYPE) Not Like "NA") AND ((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS])) ORDER BY dbo_curr_month_employee.REPORT_DISPLAY_NAME; | curr_month_employee | List of all employees by title, citizenship status, hire date and termination date |
| 18. | SELECT dbo_curr_month_employee.NUMB_EMPL_ID AS [Employee ID], dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL AS [Last Name], dbo_curr_month_employee.NAME_EMPL_FIRST_LEGAL AS [First Name], dbo_curr_month_employee.DATE_APPT_BEG AS [Appt Begin Date], dbo_curr_month_employee.DATE_APPT_END AS [Appt End Date] FROM dbo_curr_month_employee WHERE (((dbo_curr_month_employee.NAME_EMPL_DEPARTMENT)=[Enter your department: for eg: ASTRONOMY, BIOCHEM/MOLEC BIO, BIOLOGY, CHEMISTRY, MATHEMATICS, PHYSICS, STATISTICS]) AND ((dbo_curr_month_employee.DATE_APPT_END)>Date()) AND ((dbo_curr_month_employee.CODE_APPT)="wag")) ORDER BY dbo_curr_month_employee.NAME_EMPL_LAST_LEGAL; | curr_month_employee | Listing of wage payroll employees with appointment start and end dates |
| Go top | |||
This page is maintained by Rubina V. Nashine: rvn2@psu.edu, (814) 360-1882
Eberly College of Science, 517 Thomas Building, University Park, PA 16802-2112
This page was last updated on 10 December 2007 © Penn State University
If you would like to communicate with the keepers of the Eberly College of Science Web server, send electronic mail to: science-web@science.psu.edu
Technology Webmaster: Brian Broking < brb10@psu.edu >
Content Webmaster: Barbara Kennedy < science@psu.edu >