How to Create SQL Statements to Simplify Vista Uploads
Every once in a while, Payroll and Human Resources have new requests for changes to employee records that encompass a large number of employees. Some examples include a new accrual leave type with different hours for PT vs FT employees or an upload into the timeth table. Even those of us that support the application in our Department of Innovation and Technology have requests – I created an External Interface file that contained over 100 columns in our development environment. I knew I did not want to rekey all of those columns into both test and production. Our solution is simple – use Crystal Reports to produce SQL statements that we can run in SQL Server Management Studio to update our database.
Let’s take a look at the new accrual leave type. For the Clerk’s office HR requested a new leave type of ‘Birthday’ with all FT employees receiving 8 hours and PT employees 5 hours. (For clarification purposes this is used only for existing employees and a new leave type. For new hires/rehires the system automatically gives them their leave types (vacation/sick/personal etc.) based upon eligibility rules we have set up.)
Example 1 – New Accrual Code
Step 1 – A Crystal Report was created that produced a listing of 1) all Clerk employees 2) who were active (not termed) and 3) had a default earnings code of FT, PT or Elected Official.
Step 2 – A ‘formula field’ was created in Crystal called ‘BDay Code’ that determined if the employee was FT/PT or Elected Official and their corresponding accrual code.
Step 3 – The ‘SQL’ code was produced as a formula and inserted into the Crystal Report. The code from step one was suppressed.
Step 5 – Payroll verifies the information in the test environment is correct. The Crystal Report is modified to point to production and the SQL steps are executed. Going forward all rehires/new employees will automatically have the ‘Birthday’ leave accrual automatically added based upon the eligibility rules that we have set up.
Example 2 – External Interface
Step 1 – A new External Interface (EI) was created that contained over 100 columns in our development environment. In our test environment, the same EI was created except for the columns. For the SQL upload to work correctly, make note of the ‘eistep_id’ in table eistep from the test environment (in this case ‘1017’) as the report will be pointing to the development database.
Step 2 – My coworker created a Crystal Report and created a formula field called ‘Step ID’ that contained the value of the ‘eistep_id’.
Step 3 – The ‘SQL’ code was produced as a formula and inserted into the Crystal Report using table ‘eistepcol’.
Step 4 – Execute the Crystal Report.
Step 5 – Export the results and run the SQL code in the test environment to produce the EI columns and you are done!
Example 3 – Insert Into Timeth
Step 1 – After calendar year end, we receive a spreadsheet from the Board of County Commissioners HR department containing employee names, person_ids and number of hours they will be donating to the ‘Sick Bank’ pool. A Crystal Report has been created to point to the spreadsheet and create timeth records that will deduct the number hours contained in the spreadsheet from the employee’s sick accrual code.
Step 2 – Create the ‘SQL’ code based upon the spreadsheet information.
Step 3 – Execute the Crystal Report and upload the data into timeth in the test environment.
Step 4 – Payroll verifies the information is correct and the SQL is uploaded into production. This has saved payroll the time and effort of manually entering over 800 employees into the timeth table.
These are just a few examples of how we use Crystal Reports to make our job and those in HR and Payroll easier. I am sure there are ways you too could use Crystal Reports to create SQL statements that will lighten the load for your departments.
Barbara Cobb
Application Architect
Lee County Clerk Of Courts
bcobb@leeclerk.org