User Tools

Site Tools


projects:ceo_bi_reports_4

CEO BI Reports

KPIs Requests

KPIs - Duration

KPIs - By financial year

<span style="color:red;">Financial Year filters should be managed on the PowerBI software.</span>

Request 1: PDA to Contract Converted

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cont.d_pay_ideposit_act AS "Initial Deposit",
   doc.d_signed_act AS "Contract Signed",
   DATEDIFF(day, cont.d_pay_ideposit_act, doc.d_signed_act) AS "Duration (days) PDA to Contract Converted" 
FROM
   fworksqlecm.dbo.cont 
   INNER JOIN
      fworksqlecm.dbo.job_cont 
      ON job_cont.l_cont_id = cont.l_cont_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = job_cont.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id        
WHERE
   cont.d_pay_ideposit_act IS NOT NULL 
   AND doc.d_signed_act IS NOT NULL

Request 2: Contract Signed to Job to Site

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   doc.d_signed_act AS "Contract Signed",
   admin.d_admin_comp_act AS "Administration Completed",
   DATEDIFF(day, doc.d_signed_act, admin.d_admin_comp_act) AS "Duration (days) Contract Signed to Job to Site" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.udf_job1 
      ON udf_job1.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = udf_job1.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id       
WHERE
   doc.d_signed_act IS NOT NULL 
   AND admin.d_admin_comp_act IS NOT NULL   

Request 3: Drafting - Rev - Plans Complete

Request 4: Drafting - Amendments

<span style="color:red;">Need more information to identify start/end.</span>

Request 5: Contracts - Original Received from Estimator to Contract Checked

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_orig_fest_rec AS "Original Received from Estimator",
   udf_job1.d_contchecked AS "Contract Checked",
   DATEDIFF(day, udf_job1.d_orig_fest_rec, udf_job1.d_contchecked) AS "Duration (days) Original Contract Received to Contract Checked" 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = udf_job1.l_job_id
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id       
WHERE
   udf_job1.d_orig_fest_rec IS NOT NULL 
   AND udf_job1.d_contchecked IS NOT NULL

Request 6: Selections - Final Selections Consultation to Selections Complete

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   dia_dentry.d_dentry_fman AS final_selections_consultation,
   admin.d_csel_comp AS selection_completed,
   DATEDIFF(day, dia_dentry.d_dentry_fman, admin.d_csel_comp) AS "Duration (days) Final Selections Consultation to Selections Complete" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.dia_dentry 
      ON dia_dentry.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id       
WHERE
   dia_dentry.d_dentry_fman IS NOT NULL 
   AND admin.d_csel_comp IS NOT NULL

Request 7: Estimating - Ready to Est to Estimating Complete

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_readytoest AS ready_to_estimating,
   admin.d_ordering_comp AS estimating_completed,
   DATEDIFF(day, udf_job1.d_readytoest, admin.d_ordering_comp) AS "Duration (days) Ready to Est to Estimating Complete" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.udf_job1 
      ON udf_job1.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id       
WHERE
   udf_job1.d_readytoest IS NOT NULL 
   AND admin.d_ordering_comp IS NOT NULL

Request 8: Sent to Site to Handover - Administration Complete to Handover Actual Complete

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   prd_hElev.s_name AS "Elevation",
   admin.d_admin_comp_act AS "Adminsitration Completed",
   cont.d_settle_act AS "Handover Actual",
   DATEDIFF(DAY, admin.d_admin_comp_act, cont.d_settle_act) AS "Duration (days) Administration Complete TO Handover Actual Complete" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = job_cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.prd_hElev 
      ON job.l_prd_hElev_id = prd_hElev.l_prd_hElev_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id      
WHERE
   admin.d_admin_comp_act IS NOT NULL 
   AND cont.d_settle_act IS NOT NULL

Request 9: Footings to Handover - Footings Pour Actual Complete to Handover Actual Complete

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   prd_hElev.s_name AS "Elevation",
   cst.d_base_comp_act AS "Footings Pour Actual Complete",
   cont.d_settle_act AS "Handover Actual",
   DATEDIFF(DAY, cst.d_base_comp_act, cont.d_settle_act) AS "Duration (days) Footings Pour Actual Complete TO Handover Actual Complete" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = job_cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.prd_hElev 
      ON job.l_prd_hElev_id = prd_hElev.l_prd_hElev_id 
   INNER JOIN
      fworksqlecm.dbo.cst 
      ON cst.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id       
WHERE
   cst.d_base_comp_act IS NOT NULL 
   AND cont.d_settle_act IS NOT NULL

Other Requests

projects/ceo_bi_reports_4.txt · Last modified: 2019/06/21 12:47 by 192.168.2.211