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
Last revision Both sides next revision
projects:ceo_bi_reports_2 [2019/06/20 14:18]
192.168.2.213
projects:ceo_bi_reports_2 [2019/06/21 07:59]
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>​
Line 1583: Line 1687:
 <​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 1710:
    ​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