Waitlist Data Load

Daily Waitlist table refreshes at 7:30 am:

Steps:

(1) The a1_sps_waitlist view was created to retrieve the waitlist data

(2) The vw_sps_waitlist_build script builds the vw_sps_waitlist table from the a1_sps_waitlist view.

(3) The vw_sps_waitlist_build script is added to the vw_table_build script to refresh the waitlist table at 7:30 am daily through Crystal Report.

(4) The vw_sps_waitlist table is used in the 'Course Review Tool' Power BI report

Note: Run the following SQL Script manually to refresh the 'vw_sps_waitlist' table:

(Run daily, anytime during the day, but preferably between 7:30 am and 8 am.)

-----------------------------
USE [cereports]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[vw_sps_waitlist_build]
SELECT 'Return Value' = @return_value
GO
---------------------------

(The following steps are inactive now)

Steps to load the waitlist data into the WaitLists_by_PID table from the SSOL Waitlist view

Login to SSOL Waitlist view - You must have access to SSOL and have access to all courses mounted in SPS.

Scroll to the bottom of the page and click on:

Once the page loads scroll to the bottom of the page and click on the "download" link located on the bottom left.

Once the csv file is downloaded to your local computer move the file to I:\DATA_IMPORTS\Waitlists on the H drive

Delete the existing Waitlists.csv file and rename the current file to WaitLists.csv

Login to the terminal server and use file explorer to view I:\DATA_IMPORTS\Waitlists on the H drive

Execute the integration service package called WaitLists_by_PID.dtsx - this script will delete the current data in the WaitLists_by PID table and replace it with the current downloaded file.

1. How to change the @waitlist_term_id manually in the a2_crt_pb.sql procedure

Open the a2_crt_pbi procedure, and look for the  @waitlist_term_id variable, and change it to the current term.

For example, if @waitlist_term_id  is 20241 then change it to 20242 and save the stored procedure.