User Tools

Site Tools


projects:ceo_bi_reports_2

CEO BI Reports

Job Workflow Requests

Job Workflow - Sales

Request 1: Sales - Opportunities

Updated on 12/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Sales.

<span style="color:red;">Use the FworkSQLSA database for these 2 following requests.</span>

SELECT
   doc.d_prepared AS prepared_date,
   sales_advice.s_name AS name,
   prd_htype.s_name AS houe_type,
   doc.c_retail_total AS total_price 
FROM
   fworksqlsa.dbo.sales_advice 
   INNER JOIN
      fworksqlsa.dbo.doc 
      ON doc.l_sales_advice_id = sales_advice.l_sales_advice_id 
   INNER JOIN
      fworksqlsa.dbo.prd_htype 
      ON prd_htype.l_prd_htype_id = sales_advice.l_prd_htype_id 
   INNER JOIN
      fworksqlsa.dbo.client 
      ON client.l_client_id = sales_advice.l_client_id 
   INNER JOIN
      fworksqlsa.dbo.list_item 
      ON list_item.l_list_item_id = client.l_status_gl_id 
WHERE
   list_item.s_name = 'Active' 
   AND sales_advice.f_synchronised = 0
SELECT
   COUNT(*) AS count_opportunities 
FROM
   fworksqlsa.dbo.sales_advice 
   INNER JOIN
      fworksqlsa.dbo.doc 
      ON doc.l_sales_advice_id = sales_advice.l_sales_advice_id 
   INNER JOIN
      fworksqlsa.dbo.prd_htype 
      ON prd_htype.l_prd_htype_id = sales_advice.l_prd_htype_id 
   INNER JOIN
      fworksqlsa.dbo.client 
      ON client.l_client_id = sales_advice.l_client_id 
   INNER JOIN
      fworksqlsa.dbo.list_item 
      ON list_item.l_list_item_id = client.l_status_gl_id 
WHERE
   list_item.s_name = 'Active' 
   AND sales_advice.f_synchronised = 0

Job Workflow - PDA

Request 2: PDA - Stage 1 - Administration – Set Up

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

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,
   udf_job1.d_designfile_rec AS design_review_file_received 
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.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
   cont.d_pay_ideposit_act IS NOT NULL 
   AND udf_job1.d_designfile_rec IS NULL
SELECT
   COUNT(*) AS count_stage_1_administration_setup 
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.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
   cont.d_pay_ideposit_act IS NOT NULL 
   AND udf_job1.d_designfile_rec IS NULL

Request 3: PDA - Stage 1 - Council Compliance Check

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_designfile_rec AS design_review_file_received,
   udf_job1.d_desrev_comp AS design_review_completed 
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_desrev_comp IS NULL 
   AND udf_job1.d_designfile_rec IS NOT NULL
SELECT
   COUNT(*) AS count_stage_1_council_compliance_check
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_desrev_comp IS NULL 
   AND udf_job1.d_designfile_rec IS NOT NULL   

Request 4: PDA - Stage 1 - Administration – Order Prelim Plans

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_ord AS prelim_plans_ordered,
   udf_job1.d_desrev_comp AS design_review_completed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin 
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND udf_job1.d_desrev_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_ord IS NULL   
SELECT
   COUNT(*) AS count_stage_1_administration_order_prelim_plans 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin 
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND udf_job1.d_desrev_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_ord IS NULL   

Request 5: PDA - Stage 1 - Drafting - Prelim Plans

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_comp AS completed,
   admin_dwgs.d_dwgs_ord AS ordered 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND admin_dwgs.d_dwgs_comp IS NULL 
   AND admin_dwgs.d_dwgs_ord IS NOT NULL 
SELECT
   COUNT(*) AS count_stage_1_drafting_prelim_plans 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND admin_dwgs.d_dwgs_comp IS NULL 
   AND admin_dwgs.d_dwgs_ord IS NOT NULL 

Request 6: PDA - Stage 1 - Administration - Prelim Plans Signed

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_comp AS completed,
   admin_dwgs.d_dwgs_fclient AS signed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND admin_dwgs.d_dwgs_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_fclient IS NULL 
SELECT
   COUNT(*) AS count_stage_1_admin_plans_signed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND admin_dwgs.d_dwgs_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_fclient IS NULL 

Request 7: PDA - Stage 2 - Administration – Order Final Plans

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   li1.s_name AS type_signed,
   e1.s_name AS supplier_signed,
   ad1.d_dwgs_fclient AS prelim_plans_signed,
   li2.s_name AS type_ordered,
   ad2.d_dwgs_ord AS final_plans_ordered 
FROM
   fworksqlecm.dbo.admin_dwgs ad1 
   INNER JOIN
      fworksqlecm.dbo.list_item li1 
      ON li1.l_list_item_id = ad1.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity e1 
      ON e1.l_entity_id = ad1.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin ada1 
      ON ada1.l_admin_dwgs_id = ad1.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin a1 
      ON a1.l_admin_id = ada1.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = a1.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin a2 
      ON a2.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin ada2 
      ON ada2.l_admin_id = a2.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgs ad2 
      ON ad2.l_admin_dwgs_id = ada2.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.list_item li2 
      ON li2.l_list_item_id = ad2.l_dwgs_type_gl_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
   (
      li1.s_name = 'Preliminary Plans' 
      OR e1.s_name = 'Rev -' 
   )
   AND ad1.d_dwgs_fclient IS NOT NULL 
   AND li2.s_name = 'Final Plans' 
   AND ad2.d_dwgs_ord IS NULL   
SELECT
   COUNT(*) AS count_stage_2_admin_order_final_plans 
FROM
   fworksqlecm.dbo.admin_dwgs ad1 
   INNER JOIN
      fworksqlecm.dbo.list_item li1 
      ON li1.l_list_item_id = ad1.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity e1 
      ON e1.l_entity_id = ad1.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin ada1 
      ON ada1.l_admin_dwgs_id = ad1.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin a1 
      ON a1.l_admin_id = ada1.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = a1.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin a2 
      ON a2.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin ada2 
      ON ada2.l_admin_id = a2.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgs ad2 
      ON ad2.l_admin_dwgs_id = ada2.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.list_item li2 
      ON li2.l_list_item_id = ad2.l_dwgs_type_gl_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
   (
      li1.s_name = 'Preliminary Plans' 
      OR e1.s_name = 'Rev -' 
   )
   AND ad1.d_dwgs_fclient IS NOT NULL 
   AND li2.s_name = 'Final Plans' 
   AND ad2.d_dwgs_ord IS NULL 

Request 8: PDA - Stage 2 - Drafting – Final Plans

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_ord AS ordered,
   admin_dwgs.d_dwgs_comp AS completed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   list_item.s_name = 'Final Plans' 
   AND admin_dwgs.d_dwgs_comp IS NULL 
   AND admin_dwgs.d_dwgs_ord IS NOT NULL   
SELECT
   COUNT(*) AS count_stage_2_drafting_final_plans 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   list_item.s_name = 'Final Plans' 
   AND admin_dwgs.d_dwgs_comp IS NULL 
   AND admin_dwgs.d_dwgs_ord IS NOT NULL   

Request 9: PDA - Stage 2 - Administration - Final Plans Signed

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_comp AS completed,
   admin_dwgs.d_dwgs_fclient AS signed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   list_item.s_name = 'Final Plans' 
   AND admin_dwgs.d_dwgs_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_fclient IS NULL 
SELECT
   COUNT(*) AS count_stage_2_admin_final_plans_signed 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id  
   INNER JOIN
      fworksqlecm.dbo.admin
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id       
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   list_item.s_name = 'Final Plans' 
   AND admin_dwgs.d_dwgs_comp IS NOT NULL 
   AND admin_dwgs.d_dwgs_fclient IS NULL 

Request 10: PDA - Stage 2 - Sales Estimates

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   list_item.s_name AS type,
   entity.s_name AS supplier,
   admin_dwgs.d_dwgs_fclient AS final_plans_signed,
   udf_job1.d_salesest_comp AS sales_estimates_completed
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin 
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Final Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND udf_job1.d_salesest_comp IS NULL 
   AND admin_dwgs.d_dwgs_fclient IS NOT NULL   
SELECT
   COUNT(*) AS count_stage_2_sales_estimates 
FROM
   fworksqlecm.dbo.admin_dwgs 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = admin_dwgs.l_dwgs_type_gl_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin_dwgs.l_dwgs_e_id 
   INNER JOIN
      fworksqlecm.dbo.admin_dwgsadmin 
      ON admin_dwgsadmin.l_admin_dwgs_id = admin_dwgs.l_admin_dwgs_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_admin_id = admin_dwgsadmin.l_admin_id 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = admin.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
   (
      list_item.s_name = 'Final Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND udf_job1.d_salesest_comp IS NULL 
   AND admin_dwgs.d_dwgs_fclient IS NOT NULL   

Request 11: PDA - Stage 2 - Footings

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_salesest_comp AS sales_estimates_completed,
   udf_job1.d_orig_fest_rec AS original_received_from_estimator 
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_salesest_comp IS NOT NULL 
   AND udf_job1.d_orig_fest_rec IS NULL   
SELECT
   COUNT(*) AS count_stage_2_footings 
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_salesest_comp IS NOT NULL 
   AND udf_job1.d_orig_fest_rec IS NULL   

Request 12: PDA - Stage 2 - Contracts Check

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

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,
   list_item.s_name AS estimator,
   udf_job1.d_contchecked AS contract_checked 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = udf_job1.l_id_footestim_id 
   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 NULL   
SELECT
   COUNT(*) AS count_stage_2_contracts_check 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = udf_job1.l_id_footestim_id 
   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 NULL   

Request 13: PDA - Stage 2 - Sales – Contracts to be Converted

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab PDA.

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,
   doc.d_signed_act AS contract_signed 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = udf_job1.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
   udf_job1.d_orig_fest_rec IS NOT NULL 
   AND doc.d_signed_act IS NULL   
SELECT
   COUNT(*) AS count_stage_2_sales_contract_to_be_converted 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = udf_job1.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
   udf_job1.d_orig_fest_rec IS NOT NULL 
   AND doc.d_signed_act IS NULL   

Job Workflow - Contract

Request 14: Contract - Administration – Planning Approval

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

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_bperm_rec_act AS planning_application_received 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.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
   doc.d_signed_act IS NOT NULL 
   AND admin.d_bperm_rec_act IS NULL   
SELECT
   COUNT(*) AS count_stage_2_admin_planning_approval 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.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
   doc.d_signed_act IS NOT NULL 
   AND admin.d_bperm_rec_act IS NULL   

Request 15: Contract - Administration – FSC to be held

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin.d_bperm_rec_act AS planning_application_received,
   dia_dentry.d_dentry_fman AS final_selections_consultation,
   CAST(CAST(getdate() AS DATE) AS DATETIME) AS today 
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
   admin.d_bperm_rec_act IS NOT NULL 
   AND dia_dentry.d_dentry_fman > CAST(CAST(getdate() AS DATE) AS DATETIME)   
SELECT
   COUNT(*) AS count_stage_2_admin_fsc_to_be_held 
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
   admin.d_bperm_rec_act IS NOT NULL 
   AND dia_dentry.d_dentry_fman > CAST(CAST(getdate() AS DATE) AS DATETIME)        

Request 16: Contract - Selections

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

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,
   CAST(CAST(getdate() AS DATE) AS DATETIME) AS today 
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 <= CAST(CAST(getdate() AS DATE) AS DATETIME) 
   AND admin.d_csel_comp IS NULL   
SELECT
   COUNT(*) AS count_stage_2_selections 
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 <= CAST(CAST(getdate() AS DATE) AS DATETIME) 
   AND admin.d_csel_comp IS NULL   

Request 17: Contract - Administration - BRC Approval

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin.d_csel_comp AS selection_completed,
   udf_job1.d_privatecert_rec AS building_rules_consent_received 
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
   admin.d_csel_comp IS NOT NULL 
   AND udf_job1.d_privatecert_rec IS NULL
SELECT
   COUNT(*) AS count_stage_2_admin_brc_approval 
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
   admin.d_csel_comp IS NOT NULL 
   AND udf_job1.d_privatecert_rec IS NULL

Request 18: Contract - Administration - Full DA Approval

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_privatecert_rec AS building_rules_consent_received,
   admin.d_bperm_da_rec AS full_da_received 
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_privatecert_rec IS NOT NULL 
   AND admin.d_bperm_da_rec IS NULL   
SELECT
   COUNT(*) AS count_stage_2_admin_full_da_approval 
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_privatecert_rec IS NOT NULL 
   AND admin.d_bperm_da_rec IS NULL   

Request 19: Contract - Administration – Ready to Est

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin.d_bperm_da_rec AS full_da_received,
   udf_job1.d_readytoest AS ready_to_estimating 
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
   admin.d_bperm_da_rec IS NOT NULL 
   AND udf_job1.d_readytoest IS NULL   
SELECT
   COUNT(*) AS count_stage_2_ready_to_est 
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
   admin.d_bperm_da_rec IS NOT NULL 
   AND udf_job1.d_readytoest IS NULL   

Request 20: Contract - Estimating

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

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 
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 NULL   
SELECT
   COUNT(*) AS count_stage_2_estimating 
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 NULL   

Request 21: Contract - Scheduling

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin.d_ordering_comp AS estimating_completed,
   udf_job1.d_scheduling_comp AS scheduling_completed 
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
   admin.d_ordering_comp IS NOT NULL 
   AND udf_job1.d_scheduling_comp IS NULL   
SELECT
   COUNT(*) AS count_stage_2_scheduling  
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
   admin.d_ordering_comp IS NOT NULL 
   AND udf_job1.d_scheduling_comp IS NULL   

Request 22: Contract - Administration – Land & Finance

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Contract.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_scheduling_comp AS scheduling_completed,
   admin.d_admin_comp_act AS administration_actual_completed 
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_scheduling_comp IS NOT NULL 
   AND admin.d_admin_comp_act IS NULL   
SELECT
   COUNT(*) AS count_stage_2_admin_land_and_finance 
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_scheduling_comp IS NOT NULL 
   AND admin.d_admin_comp_act IS NULL   

Job Workflow - Construction

Request 23: Construction - Footings

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin.d_admin_comp_act AS adminsitration_actual_completed,
   cst.d_base_comp_act AS footing_actual_completed 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_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
   admin.d_admin_comp_act IS NOT NULL 
   AND cst.d_base_comp_act IS NULL   
SELECT
   COUNT(*) AS count_construction_footings 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = job.l_job_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
   admin.d_admin_comp_act IS NOT NULL 
   AND cst.d_base_comp_act IS NULL   

Request 24: Construction - Frames

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_base_comp_act AS footing_actual_completed,
   cst.d_frame_comp_act AS frame_actual_completed 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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 cst.d_frame_comp_act IS NULL
SELECT
   COUNT(*) AS count_construction_frames 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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 cst.d_frame_comp_act IS NULL

Request 25: Construction - Brickwork & Roof (SS) / Brickwork (DS)

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_base_comp_act AS frame_actual_completed,
   cst.d_lockup_comp_act AS brickwork_and_roof_actual_completed 
FROM
   fworksqlecm.dbo.cst
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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 cst.d_lockup_comp_act IS NULL
SELECT
   COUNT(*) AS count_construction_brickword_and_roof_brickwork 
FROM
   fworksqlecm.dbo.cst
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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 cst.d_lockup_comp_act IS NULL

Request 26: Construction - Internal Linings

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_lockup_comp_act AS brickwork_and_roof_actual_completed,
   cst.d_fix1_comp_act AS internal_linings_actual_completed 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_lockup_comp_act IS NOT NULL 
   AND cst.d_fix1_comp_act IS NULL   
SELECT
   COUNT(*) AS count_construction_internal_linings 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_lockup_comp_act IS NOT NULL 
   AND cst.d_fix1_comp_act IS NULL  

Request 27: Construction - 2nd Fix

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_fix1_comp_act AS internal_linings_actual_completed,
   cst.d_fix_comp_act AS second_fix_actual_completed
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_fix1_comp_act IS NOT NULL 
   AND cst.d_fix_comp_act IS NULL         
SELECT
   COUNT(*) AS count_second_fix 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_fix1_comp_act IS NOT NULL 
   AND cst.d_fix_comp_act IS NULL         

Request 28: Construction - PCI

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Construction.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_fix_comp_act AS second_fix_actual_completed,
   cst.d_pci_comp_act AS pci_actual_completed 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_fix_comp_act IS NOT NULL 
   AND cst.d_pci_comp_act IS NULL   
SELECT
   COUNT(*) AS count_pci 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_fix_comp_act IS NOT NULL 
   AND cst.d_pci_comp_act IS NULL   

Job Workflow - Maintenance

Request 29: Maintenance - 3 Month Maintenance

Updated on 21/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports 2.pbix tab Maintenance.

<span style="color:red;">This SQL request transcribes the initial condition in the requirements document: Construction Actual Completed < = 90 days from today.</span>

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_cst_comp_act AS construction_actual_completed,
   dateadd(DAY, - 90, CAST(CAST(getdate() AS DATE) AS DATETIME)) AS ninety_days_backward_from_today 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_cst_comp_act <= dateadd(DAY, - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))
SELECT
   COUNT(*) AS count_three_month_maintenance 
FROM
   fworksqlecm.dbo.cst 
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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_cst_comp_act <= dateadd(DAY, - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))

<span style="color:red;">Here a new version that relates the 3-month maintenance delay: today < = 90 days from the Construction Actual Completed.</span>

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cst.d_cst_comp_act AS construction_actual_completed,
   dateadd(DAY, 90, cst.d_cst_comp_act) AS ninety_days_forward_from_construction_completed 
FROM
   fworksqlecm.dbo.cst
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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
   CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY, 90, cst.d_cst_comp_act)   
SELECT
   COUNT(*) AS count_three_month_maintenace_bis 
FROM
   fworksqlecm.dbo.cst
   INNER JOIN
      fworksqlecm.dbo.job 
      ON job.l_job_id = cst.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
   CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY, 90, cst.d_cst_comp_act)   

Other Requests

projects/ceo_bi_reports_2.txt · Last modified: 2019/06/21 08:01 by 192.168.2.159