User Tools

Site Tools


projects:ceo_bi_reports_2

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
projects:ceo_bi_reports_2 [2019/06/20 14:18]
192.168.2.213
projects:ceo_bi_reports_2 [2019/06/21 08:01]
192.168.2.159
Line 1424: Line 1424:
  
 === Request 24: Construction - Frames === === Request 24: Construction - Frames ===
-Updated on 14/06/2019.+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''​. 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''​.
Line 1430: Line 1430:
 <​code>​ <​code>​
 SELECT 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_base_comp_act AS footing_actual_completed,​
    ​cst.d_frame_comp_act AS frame_actual_completed ​    ​cst.d_frame_comp_act AS frame_actual_completed ​
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_base_comp_act IS NOT NULL     ​cst.d_base_comp_act IS NOT NULL 
Line 1443: Line 1455:
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_base_comp_act IS NOT NULL     ​cst.d_base_comp_act IS NOT NULL 
Line 1449: Line 1470:
  
 === Request 25: Construction - Brickwork & Roof (SS) / Brickwork (DS) === === Request 25: Construction - Brickwork & Roof (SS) / Brickwork (DS) ===
-Updated on 14/06/2019.+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''​. 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''​.
Line 1455: Line 1476:
 <​code>​ <​code>​
 SELECT 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_base_comp_act AS frame_actual_completed,​
    ​cst.d_lockup_comp_act AS brickwork_and_roof_actual_completed ​    ​cst.d_lockup_comp_act AS brickwork_and_roof_actual_completed ​
 FROM FROM
-   ​fworksqlecm.dbo.cst ​+   ​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 WHERE
    ​cst.d_base_comp_act IS NOT NULL     ​cst.d_base_comp_act IS NOT NULL 
Line 1467: Line 1500:
    ​COUNT(*) AS count_construction_brickword_and_roof_brickwork ​    ​COUNT(*) AS count_construction_brickword_and_roof_brickwork ​
 FROM FROM
-   ​fworksqlecm.dbo.cst ​+   ​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 WHERE
    ​cst.d_base_comp_act IS NOT NULL     ​cst.d_base_comp_act IS NOT NULL 
Line 1474: Line 1516:
  
 === Request 26: Construction - Internal Linings === === Request 26: Construction - Internal Linings ===
-Updated on 14/06/2019.+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''​. 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''​.
Line 1480: Line 1522:
 <​code>​ <​code>​
 SELECT 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_lockup_comp_act AS brickwork_and_roof_actual_completed,​
    ​cst.d_fix1_comp_act AS internal_linings_actual_completed ​    ​cst.d_fix1_comp_act AS internal_linings_actual_completed ​
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_lockup_comp_act IS NOT NULL     ​cst.d_lockup_comp_act IS NOT NULL 
-   AND cst.d_fix1_comp_act IS NULL+   AND cst.d_fix1_comp_act IS NULL   ​
 </​code>​ </​code>​
 <​code>​ <​code>​
Line 1493: Line 1547:
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_lockup_comp_act IS NOT NULL     ​cst.d_lockup_comp_act IS NOT NULL 
-   AND cst.d_fix1_comp_act IS NULL+   AND cst.d_fix1_comp_act IS NULL  
 </​code>​ </​code>​
  
 === Request 27: Construction - 2nd Fix === === Request 27: Construction - 2nd Fix ===
-Updated on 14/06/2019.+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''​. 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''​.
Line 1505: Line 1568:
 <​code>​ <​code>​
 SELECT 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_fix1_comp_act AS internal_linings_actual_completed,​
    ​cst.d_fix_comp_act AS second_fix_actual_completed    ​cst.d_fix_comp_act AS second_fix_actual_completed
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_fix1_comp_act IS NOT NULL     ​cst.d_fix1_comp_act IS NOT NULL 
-   AND cst.d_fix_comp_act IS NULL      +   AND cst.d_fix_comp_act IS NULL         ​
 </​code>​ </​code>​
 <​code>​ <​code>​
Line 1518: Line 1593:
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_fix1_comp_act IS NOT NULL     ​cst.d_fix1_comp_act IS NOT NULL 
-   AND cst.d_fix_comp_act IS NULL     ​+   AND cst.d_fix_comp_act IS NULL         ​
 </​code>​ </​code>​
  
 === Request 28: Construction - PCI === === Request 28: Construction - PCI ===
-Updated on 14/06/2019.+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''​. 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''​.
Line 1530: Line 1614:
 <​code>​ <​code>​
 SELECT 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_fix_comp_act AS second_fix_actual_completed,​
    ​cst.d_pci_comp_act AS pci_actual_completed ​    ​cst.d_pci_comp_act AS pci_actual_completed ​
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_fix_comp_act IS NOT NULL     ​cst.d_fix_comp_act IS NOT NULL 
-   AND cst.d_pci_comp_act IS NULL+   AND cst.d_pci_comp_act IS NULL   ​
 </​code>​ </​code>​
 <​code>​ <​code>​
Line 1543: Line 1639:
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_fix_comp_act IS NOT NULL     ​cst.d_fix_comp_act IS NOT NULL 
-   AND cst.d_pci_comp_act IS NULL+   AND cst.d_pci_comp_act IS NULL   ​
 </​code>​ </​code>​
  
 ==== Job Workflow - Maintenance === ==== Job Workflow - Maintenance ===
 === Request 29: Maintenance - 3 Month Maintenance === === Request 29: Maintenance - 3 Month Maintenance ===
-Updated on 14/06/2019.+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''​.\\ 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''​.\\
Line 1557: Line 1662:
 <span style="​color:​red;">​This SQL request transcribes the initial condition in the requirements document: Construction Actual Completed < = 90 days from today.</​span>​ <span style="​color:​red;">​This SQL request transcribes the initial condition in the requirements document: Construction Actual Completed < = 90 days from today.</​span>​
 </​html>​ </​html>​
- 
  
 <​code>​ <​code>​
 SELECT 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,​    ​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 ​    ​dateadd(DAY,​ - 90, CAST(CAST(getdate() AS DATE) AS DATETIME)) AS ninety_days_backward_from_today ​
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_cst_comp_act <= dateadd(DAY,​ - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))    ​cst.d_cst_comp_act <= dateadd(DAY,​ - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))
Line 1573: Line 1689:
 FROM FROM
    ​fworksqlecm.dbo.cst ​    ​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 WHERE
    ​cst.d_cst_comp_act <= dateadd(DAY,​ - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))    ​cst.d_cst_comp_act <= dateadd(DAY,​ - 90, CAST(CAST(getdate() AS DATE) AS DATETIME ))
Line 1583: Line 1708:
 <​code>​ <​code>​
 SELECT 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,​    ​cst.d_cst_comp_act AS construction_actual_completed,​
    ​dateadd(DAY,​ 90, cst.d_cst_comp_act) AS ninety_days_forward_from_construction_completed ​    ​dateadd(DAY,​ 90, cst.d_cst_comp_act) AS ninety_days_forward_from_construction_completed ​
 FROM FROM
-   ​fworksqlecm.dbo.cst ​+   ​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 WHERE
-   ​CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY,​ 90, cst.d_cst_comp_act)+   ​CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY,​ 90, cst.d_cst_comp_act) ​  ​
 </​code>​ </​code>​
 <​code>​ <​code>​
Line 1594: Line 1731:
    ​COUNT(*) AS count_three_month_maintenace_bis ​    ​COUNT(*) AS count_three_month_maintenace_bis ​
 FROM FROM
-   ​fworksqlecm.dbo.cst ​+   ​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 WHERE
-   ​CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY,​ 90, cst.d_cst_comp_act)+   ​CAST(CAST(getdate() AS DATE) AS DATETIME) <= dateadd(DAY,​ 90, cst.d_cst_comp_act) ​  ​
 </​code>​ </​code>​
  
projects/ceo_bi_reports_2.txt · Last modified: 2019/06/21 08:01 by 192.168.2.159