User Tools

Site Tools


projects:ceo_bi_reports_3

CEO BI Reports

Job on Hold Requests

Job on Hold - Count

Request 1: Count

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   list_item_status.s_name AS "job status",
   rDiv.s_name AS Company,
   list_item_reason.s_name AS "job reason",
   doc.c_retail_total AS "total amount" 
FROM
   fworksqlecm.dbo.job 
   INNER JOIN
      job_hold 
      ON job.l_job_id = job_hold.l_job_id 
      AND job_hold.l_job_hold_id = 
      (
         SELECT
            MAX(job_hold.l_job_hold_id) 
         FROM
            job_hold 
         WHERE
            job_hold.l_job_id = job.l_job_id 
      )
   INNER JOIN
      list_item AS list_item_status 
      ON list_item_status.l_list_item_id = job.l_job_status_gl_id 
   LEFT JOIN
      list_item AS list_item_reason 
      ON list_item_reason.l_list_item_id = job_hold.l_hold_reas_gl_id 
   INNER JOIN
      doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id        
WHERE
   list_item_status.s_name = 'On Hold' 
   AND job.l_job_id > 0 
   AND doc.s_name = 'Tender 1'   

Location

Location - Count

Request 1: Count

Updated on 21/06/2019.

SELECT
   DISTINCT job.s_job_num AS "Job Number",
   rDiv.s_name AS Company,
   prd_sType.s_name_ref AS 'Sales Type',
   list_item_jobStatus.s_name_ref AS 'Job Status',
   wfl_stgMajor.s_name AS 'Major Stage',
   suburb.s_name AS 'Suburb Name' 
FROM
   dbo.client client 
   LEFT OUTER JOIN
      dbo.clientFile clientFile 
      ON client.l_client_id = clientFile.l_client_id 
   LEFT OUTER JOIN
      dbo.land land 
      ON clientFile.l_land_id = land.l_land_id 
   LEFT OUTER JOIN
      dbo.job job 
      ON clientFile.l_file_id = job.l_file_id 
   INNER JOIN
      dbo.cont cont_building 
      ON job.l_cont_build_id = cont_building.l_cont_id 
   INNER JOIN
      dbo.addr addr_land 
      ON land.l_addr_id = addr_land.l_addr_id 
   INNER JOIN
      dbo.rDiv rDiv 
      ON job.l_context_id = rDiv.l_context_id 
   RIGHT OUTER JOIN
      dbo.doc doc 
      ON job.l_job_id = doc.l_job_id 
   INNER JOIN
      dbo.wfl_stgMajor wfl_stgMajor 
      ON job.l_wfl_stgMajor_id = wfl_StgMajor.l_wfl_stgMajor_id 
   INNER JOIN
      dbo.wfl_stgMinor wfl_stgMinor 
      ON job.l_wfl_stgMinor_id = wfl_stgMinor.l_wfl_stgMinor_id 
   INNER JOIN
      list_item list_item_jobStatus 
      ON job.l_job_status_gl_id = list_item_jobStatus.l_list_item_id 
   INNER JOIN
      dbo.preA preA 
      ON job.l_job_id = preA.l_job_id 
   INNER JOIN
      dbo.udf_job1 udf_job1 
      ON job.l_job_id = udf_job1.l_job_id 
   INNER JOIN
      dbo.prd_hElev prd_hElev 
      ON job.l_prd_hElev_id = prd_hElev.l_prd_hElev_id 
   LEFT OUTER JOIN
      dbo.entity entity_rDiv 
      ON rDiv.l_rDiv_e_id = entity_rDiv.l_entity_id 
   INNER JOIN
      dbo.admin admin 
      ON preA.l_job_id = admin.l_job_id 
   INNER JOIN
      dbo.cst cst 
      ON admin.l_job_id = cst.l_job_id 
   INNER JOIN
      dbo.prd_sType prd_sType 
      ON job.l_prd_sType_id = prd_sType.l_prd_sType_id 
   INNER JOIN
      dbo.entity entity_adminAdmin 
      ON admin.l_admin_e_id = entity_adminAdmin.l_entity_id 
   INNER JOIN
      dbo.entity entity_cstAdmin 
      ON cst.l_cst_admin_e_id = entity_cstAdmin.l_entity_id 
   INNER JOIN
      dbo.entity entity_cstSuper 
      ON cst.l_super_e_id = entity_cstSuper.l_entity_id 
   INNER JOIN
      dbo.suburb 
      ON addr_land.l_suburb_id = suburb.l_suburb_id 
WHERE
      wfl_stgMajor.s_name_ref = 'Pre-Admin' 
      OR wfl_stgMajor.s_name_ref = 'Admin' 
      OR wfl_stgMajor.s_name_ref = 'Cst'     

Job Count - Count

Request 1: Preliminary (Rev –) plans completed

Updated on 21/06/2019.

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

SELECT
   job.s_job_num AS "Job Number",
   list_item.s_name AS "Type",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin_dwgs.d_dwgs_comp AS "Completed",
   entity2.s_name AS "Draftsperson",
   prd_hElev.s_name AS "Elevation" 
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.entity entity2 
      ON entity2.l_entity_id = admin_dwgs.l_dPers_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.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
   (
      list_item.s_name = 'Preliminary Plans' 
      OR entity.s_name = 'Rev -' 
   )
   AND admin_dwgs.d_dwgs_comp IS NOT NULL   

Request 2: Drafting amendments completed

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   list_item.s_name AS "Type",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   admin_dwgs.d_dwgs_comp AS "Completed",
   entity2.s_name AS "Draftsperson",
   prd_hElev.s_name AS "Elevation" 
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.entity entity2 
      ON entity2.l_entity_id = admin_dwgs.l_dPers_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.prd_hElev 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
   (
      list_item.s_name = 'Amended Plans' 
      OR 
      (
         entity.s_name LIKE 'Rev %' 
         AND entity.s_name <> 'Rev -'
      )
   )
   AND admin_dwgs.d_dwgs_comp IS NOT NULL   

Request 3: Footings Completed

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   list_item.s_name AS "Type",
   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" 
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 

Request 4: Contracts Check completed

Updated on 04/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_contchecked AS "Contract Checked",
   entity.s_name AS "Administrator" 
FROM
   FworkSQLEcm.dbo.job 
   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 = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin.l_admin_e_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_contchecked IS NOT NULL   

Request 5: Final Selections Consultations conducted

Updated on 21/06/2019.

<span style="color:red;">For this following request, we assume that Final Selections Consultations conducted = Sent to Sign is NULL and Sent to Complete is NULL</span>

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   entity.s_name AS "Selections Consultant",
   dia_dEntry.d_dEntry_fMan AS "Final Selections Consultation" 
FROM
   FworkSQLEcm.dbo.job 
   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 = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.dia_dEntry 
      ON admin.l_cSel_cAppt_id = dia_dEntry.l_dia_dEntry_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON dia_dEntry.l_staff_e_id = entity.l_entity_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 udf_job1.d_sentToSign IS NULL 
   AND admin.d_cSel_comp IS NULL   

Request 6: 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,
   entity.s_name AS "Selections Consultant",
   admin.d_cSel_comp AS "Selections Complete" 
FROM
   FworkSQLEcm.dbo.job 
   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 = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.dia_dEntry 
      ON admin.l_cSel_cAppt_id = dia_dEntry.l_dia_dEntry_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON dia_dEntry.l_staff_e_id = entity.l_entity_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 

Request 7: Estimating Completed

Updated on 21/06/2019.

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",
   list_item.s_name AS "Estimator" 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = udf_job1.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = udf_job1.l_id_esimatedBy_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
   admin.d_ordering_comp IS NOT NULL   

Request 8: Scheduling Completed

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_scheduling_comp AS "Scheduling Completed",
   list_item.s_name AS "Scheduler" 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = udf_job1.l_job_id    
   INNER JOIN
      fworksqlecm.dbo.list_item 
      ON list_item.l_list_item_id = udf_job1.l_id_schedBy_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
   udf_job1.d_scheduling_comp IS NOT NULL   

Request 9: Administration Completed

Updated on 21/06/2019.

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 administration_actual_completed,
   entity.s_name AS "Administrator" 
FROM
   fworksqlecm.dbo.udf_job1 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = udf_job1.l_job_id      
   INNER JOIN
      fworksqlecm.dbo.admin 
      ON admin.l_job_id = udf_job1.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = admin.l_admin_e_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   

Request 10: Stage Claims - By elevation & Site Manager

Updated on 21/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,  
   entity.s_name AS "Site Manager",
   prd_hElev.s_name AS "Elevation",
   cst.d_base_comp_fman AS "Footings Complete Forecast",
   cst.d_frame_comp_fman AS "Wall / Roof Frames Complete Forecast",
   cst.d_lockup_comp_fman AS "Brickwork & Roof (SS) / Brickwork (DS) Complete Forecast",
   cst.d_fix1_comp_fman AS "Internal Linings (SS) / Roof & Linings (DS) Complete Forecast",
   cst.d_fix_comp_fman AS "2nd Fix Complete Forecast",
   cont.d_settle_act AS "Handover Actual" 
FROM
   fworksqlecm.dbo.job 
   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.cst 
      ON cst.l_job_id = job.l_job_id 
   INNER JOIN
      fworksqlecm.dbo.entity 
      ON entity.l_entity_id = cst.l_super_e_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
   cont.d_settle_act IS NOT NULL 
   AND cst.d_base_comp_fman IS NOT NULL 
   AND cst.d_frame_comp_fman IS NOT NULL 
   AND cst.d_lockup_comp_fman IS NOT NULL 
   AND cst.d_fix1_comp_fman IS NOT NULL 
   AND cst.d_fix_comp_fman IS NOT NULL   

Other Requests

projects/ceo_bi_reports_3.txt · Last modified: 2019/06/21 10:02 by 192.168.2.159