This is a step-by-step tutorial on how to export your data from PCRecruiter so that your hireanalytiX application can be setup with historical data. To begin, go to System and then in the search box type "sql", minus the quotes, as shown in the figure below.
Click on the SQL Workbench.
Copy and paste the following Placements SQL script into the SQL field:
SELECT '' AS old_id, '' AS user_id, pla.Sendout_Id AS PlacementId, FORMAT(pla.Appointment_Date, 'yyyy-MM-dd') AS StartDate, FORMAT(pla.End_Date, 'yyyy-MM-dd') AS EndDate, FORMAT(pla.Arranged_Date, 'yyyy-MM-dd') AS PlacementDate,
CASE WHEN pla.Interview_Type = '4' THEN '2'
WHEN pla.Interview_Type = '0' THEN '1'
ELSE '2'
END AS PlacementType,
pla.Company_Id AS CompanyId, co.Company_Name AS CompanyName, co.Email_Address AS CompanyURL, co.Industry_Code AS IndustryCode,
FORMAT(pos.Date_Posted, 'yyyy-MM-dd') AS DatePosted,
pos.Job_Title AS JobTitle, pos.Position_Id AS PositionId, pla.Job_Id AS JobId, pla.Last_Name AS CandidateName, pla.Candidate_Id AS CandidateId, cand.Address AS candidate_address, cand.Address2 AS candidate_address_2, cand.City AS candidate_city, cand.State AS candidate_state, cand.Zip AS postal_code, cand.Car_Phone AS candidate_phone, cand.Email_Address AS candidate_email, pla.Written_By AS PlacedBy,
REPLACE(REPLACE(REPLACE(pla.Fee_Percent, '$', ''), ',', ''),'USD','') AS BillRate,
REPLACE(REPLACE(REPLACE(pla.Fee_Range, '$', ''), ',', ''),'USD','') AS PayRate,
pla.Guarantee, '0000-00-00' AS GuaranteeDate, pla.Feerange_Text AS PlacementStatus,
REPLACE(REPLACE(pla.Starting_Salary, '$', ''), ',', '') AS StartingSalary,
'0' AS bonus,
REPLACE(REPLACE(pla.Placement_Fee, '$', ''), ',', '') AS PlacementFee,
'1' AS PlacementCredit,
REPLACE(pla.Placement_Rate, '$', '') AS FeePercentage,
FORMAT(pla.Last_Modified, 'yyyy-MM-dd HH:mm:ss') AS LastModified,
'0' AS SplitPercentage, '' AS split_notes, '0000-00-00' AS CashInDate, '0.00' AS CashInAmount, '' AS PortfolioCompany, '0000-00-00' AS SaleDate,
DATEDIFF(day,pos.Date_Posted,pla.Arranged_Date) AS TimeToFill,
DATEDIFF(day,pos.Date_Posted,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Job_Id = pla.Job_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC)) AS TimeToInterview,
FORMAT((SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Job_Id = pla.Job_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC),'yyyy-MM-dd') AS InterviewDate,
DATEDIFF(day,pos.Date_Posted,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Candidate_Id = pla.Candidate_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC)) AS TimeToInterviewCandidate,
FORMAT((SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Candidate_Id = pla.Candidate_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC),'yyyy-MM-dd') AS InterviewDateCandidate,
DATEDIFF(day,pos.Date_Posted,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Job_Id = pla.Job_Id AND so.Interview_Type = '3' ORDER BY so.Appointment_Date ASC)) AS TimeToResmail,
FORMAT((SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Job_Id = pla.Job_Id AND so.Interview_Type = '3' ORDER BY so.Appointment_Date ASC),'yyyy-MM-dd') AS ResmailDate,
DATEDIFF(day,pos.Date_Posted,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Candidate_Id = pla.Candidate_Id AND so.Interview_Type = '3' ORDER BY so.Appointment_Date ASC)) AS TimeToResmailCandidate,
FORMAT((SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Candidate_Id = pla.Candidate_Id AND so.Interview_Type = '3' ORDER BY so.Appointment_Date ASC),'yyyy-MM-dd') AS ResmailDateCandidate,
'0000-00-00' AS arrange_date,
DATEDIFF(day,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Job_Id = pla.Job_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC),pla.Arranged_Date) AS TTFInterview,
DATEDIFF(day,(SELECT TOP 1 so.Appointment_Date FROM Sendout_Records so WHERE so.Candidate_Id = pla.Candidate_Id AND (so.Interview_Type = '1' OR so.Interview_Type = '2') ORDER BY so.Appointment_Date ASC),pla.Arranged_Date) AS TTFInterviewCandidate,
'0' AS hide_placement, '0' AS is_validate, '0000-00-00' AS validate_date,
FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS ts,
'1' AS active, co.Industry_1 AS industry, co.Specialty_1 AS sub_industry, '' AS mri_job_function, '' AS mri_job_classification, '' AS updated_by, pos.Position_Id AS InvoiceNumber, 'Due within 30 days' AS InvoiceTerms, 'Start Date' AS invoice_terms, '' AS invoice_send_details,
FORMAT(DATEADD(month, 1, pla.Appointment_Date),'yyyy-MM-dd') AS invoice_due,
'1' AS invoice_candidate_name, '0' AS invoice_salary, '0' AS invoice_fee_amount, '1' AS invoice_fee_percentage, '1' AS invoice_total_candidates, '1' AS invoice_total_resumes, '1' AS invoice_total_interviews, '1' AS invoice_total_out_of_process, '1' AS invoice_time_to_resmail, '1' AS invoice_time_to_interview, '1' AS invoice_ttf_interview, '1' AS invoice_ttf, '1' AS invoice_time_to_resmail_candidate, '1' AS invoice_time_to_interview_candidate, '1' AS invoice_ttf_interview_candidate, '0' AS invoice_pos_candidate_salary, '0' AS invoice_pos_eeo_data, '1' AS invoice_include_position_report, '0.00' AS discount, '0' AS discount_percentage, '' AS discount_text, '0' AS 'revert', '0.00' AS revert_amount, '0.00' AS revert_percentage, '' AS revert_text, co.Address_1 AS company_address, co.Address_2 AS company_address_2, co.City AS company_city, co.State AS company_state, co.Zip AS company_zip, co.Phone AS company_phone, cand.Phone_1 AS candidate_phone_home, cand.Work_Phone AS candidate_phone_work,
STUFF((SELECT '; ' + e.FText FROM EEOC e WHERE e.Record_Id = pla.Candidate_Id AND e.FName = 'Source' FOR XML PATH('')), 1, 1, '') [candidate_source],
pos.Candidate_Id AS contact_id, pos.Contact_Name AS contact_name, cont.Email_Address AS contact_email, pos.Contact_Phone AS contact_phone, '' AS send_invoice_to, '0' AS past_due_reminder, '0' AS fee_adjustment, '0' AS fall_off, '' AS original_fee, '' AS adjusted_fee, '' AS fee_change, '0000-00-00' AS adjustment_date, '' AS adjusted_fee_reason, '' AS adjusted_placements, '0' AS related, '' AS related_placements, '' AS contract_original_id, '' AS contract_classification, '' AS contract_length, '0' AS contract_conversion_fee, '0' AS contract_net_margin, '' AS contract_billing_added_by,
REPLACE(REPLACE(REPLACE(pla.OT_Pay_Rate, '$', ''), ',', ''),'USD','') AS pay_rate_ot,
REPLACE(REPLACE(REPLACE(pla.OT_Bill_Rate, '$', ''), ',', ''),'USD','') AS bill_rate_ot,
'0' AS stats_edited
FROM Sendout_Records pla
LEFT JOIN positions pos ON pos.Job_Id = pla.Job_Id
LEFT JOIN companies co ON co.Company_Id = pla.Company_Id
LEFT JOIN candidates cand ON cand.Candidate_Id = pla.Candidate_Id
LEFT JOIN candidates cont ON cont.Candidate_Id = pos.Candidate_Id
WHERE pla.Interview_Type = 0 or pla.Interview_Type = 4
ORDER BY pla.Arranged_Date ASC
Click on Export To File as shown in the figure above. In the Export Results screen, keep the defaults and simply click on the EXPORT TO FILE button as pointed out in the figure below.
When you click EXPORT TO FILE you will see a dialogue window similar to the one in the figure below. More than likely, the location, as pointed out in purple, will be your Downloads folder. Please take note of where you're saving the file as you'll need to access it in the next step. The default name of the file will be export.csv but it may be helpful to rename the file to something like placements.csv so that you can distinguish the exports from each other. Then, click Save to save the file to your computer. Depending upon the size of the file, this could take a few minutes to finish.
Once you have exported the placements data, the second and third steps of the process is to repeat this again for interviews and positions.
Interviews SQL Script:
SELECT sendout_id AS interview_id, company_id, last_name AS candidate_name, job_id, candidate_id, written_by, feerange_text AS interview_status, feerange_text AS original_status,
CASE WHEN interview_type = '1' THEN 'Telephone'
WHEN interview_type = '2' THEN 'InPerson'
WHEN interview_type = '3' THEN 'Resume'
WHEN interview_type = '14' THEN 'UserDefined1'
WHEN interview_type = '15' THEN 'UserDefined2'
ELSE 'Telephone'
END AS interview_type,
FORMAT(last_modified, 'yyyy-MM-ddTHH:mm:ss') AS last_modified, FORMAT(arranged_date, 'yyyy-MM-ddTHH:mm:ss') AS arranged_date, FORMAT(appointment_date, 'yyyy-MM-ddTHH:mm:ss') AS appointment_date, '' AS candidate_source, '' AS candidate_gender, '' AS candidate_race, '' AS candidate_disability, '' AS candidate_veteran, '' AS candidate_veterantype, '' AS split_users, '0' AS 'Key'
FROM sendout_records i
WHERE interview_type = 1 OR interview_type = 2 OR interview_type = 3 OR interview_type = 14 OR interview_type = 15
ORDER BY arranged_date ASC
Positions SQL Script:
SELECT Job_Id AS job_id, Position_Id AS position_id, Job_Title AS JobTitle, p.Company_Id AS company_id, c.Company_Name AS company_name, '' AS PortfolioCompany, '' AS SaleDate, '0' AS user_id, p.User_Name AS username, p.City AS city, p.State AS state, p.Zip AS postal_code, p.PCountry AS country, FORMAT(Date_Posted, 'yyyy-MM-dd') AS dateposted, '' AS ExternalPostingOptions, FORMAT(p.Last_Modified, 'yyyy-MM-dd HH:mm:ss') AS LastModified, Status AS PositionStatus, Contact_Name AS contact_name, p.Candidate_Id AS contact_id, p.Contact_Phone AS contact_phone, cont.Email_Address AS contact_email, REPLACE(REPLACE(REPLACE(p.Salary_Desired, '$', ''), ',', ''),'USD','') AS min_salary, REPLACE(REPLACE(REPLACE(p.Upper_Salary, '$', ''), ',', ''),'USD','') AS max_salary, Fee_Arrangement AS fee_percentage, '0' AS email_count, '0' AS TotalCandidates, '0' AS TotalResumes, '0000-00-00' AS first_resume, '0' AS TotalPhoneInterviews, '0' AS TotalInPersonInterviews, '0000-00-00' AS first_interview, '0' AS TotalOutOfProcess, '0' AS TotalApplicants, '0' AS hide_position, p.Industry AS functional_area, '' AS job_level, '0.00' AS bill_rate, '3' AS job_type, '0.00' AS pay_rate, '' AS contract_type, '' AS referring_user, '' AS split_users, '1' AS report_first_resume, '1' AS report_first_interview, '1' AS report_total_candidates, '1' AS report_total_resumes, '1' AS report_total_phone_interviews, '1' AS report_total_in_person_interviews, '1' AS report_total_out_of_process, '1' AS report_resume_to_interview_ratio, '1' AS report_position_age, '1' AS report_salary, '1' AS report_eeo_data, '1' AS report_billable_hours, '' AS updated_by, '' AS position_summary_url, '0' AS position_summary_validated, '0' AS stats_edited
FROM positions p
JOIN companies c ON c.Company_Id = p.Company_Id
LEFT JOIN candidates cont ON cont.Candidate_Id = p.Candidate_Id
ORDER BY p.Date_Posted ASC
Once you have exported all three of these files, email them to your hireanalytiX Account Manager so that your account can be setup properly and with your historical data.