User Tools

Site Tools


projects:ceo_bi_reports

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
projects:ceo_bi_reports [2019/06/20 10:30]
192.168.2.152
projects:ceo_bi_reports [2019/06/20 10:39] (current)
192.168.2.152
Line 160: Line 160:
  
 === Request 4: Contracts not Converted based on Contract Checked date and Contract Signed = null === === Request 4: Contracts not Converted based on Contract Checked date and Contract Signed = null ===
-Updated on 12/06/2019.+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.pbix]] tab ''​Contracts not Converted''​. See Power Bi Desktop report [[\\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix]] tab ''​Contracts not Converted''​.
Line 205: Line 205:
  
 === Request 5: Sales location === === Request 5: Sales location ===
-Updated on 12/06/2019.+Updated on 20/06/2019.
  
-See Power Bi Desktop report [[\\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO ​reports.pbix]] tab ''​Sales Location''​.+See Power Bi Desktop report [[\\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO ​Reports\CEO Reports.pbix]] tab ''​Sales Location''​.
  
 <​code>​ <​code>​
 SELECT DISTINCT SELECT DISTINCT
 +   ​job.s_job_num AS "Job Number",​
 +   ​jobStatus.s_name AS "Job Status",​
 +   ​rDiv.s_name AS Company,
    ​udf_job1.d_contchecked AS CHECK_DATE,    ​udf_job1.d_contchecked AS CHECK_DATE,
-   ​ISNULL(addr.s_lot_num + ', ', ''​) + ISNULL(addr.s_street_num + ', ', ''​) + ISNULL(addr.s_street_name + ', ', ''​) + ISNULL(suburb.s_name + ', ', ''​) + ISNULL(state.s_name + ', ', ''​) + ISNULL(suburb.s_postcode,​ ''​) AS "​Land ​Full Address" ​+   ​ISNULL(addr.s_lot_num + ', ', ''​) + ISNULL(addr.s_street_num + ', ', ''​) + ISNULL(addr.s_street_name + ', ', ''​) + ISNULL(suburb.s_name + ', ', ''​) + ISNULL(state.s_name + ', ', ''​) + ISNULL(suburb.s_postcode,​ ''​) AS "​Land ​FULL Address" ​
 FROM FROM
    ​FworkSQLEcm.dbo.job ​    ​FworkSQLEcm.dbo.job ​
Line 236: Line 239:
       FworkSQLEcm.dbo.state ​       FworkSQLEcm.dbo.state ​
       ON state.l_state_id = suburb.l_state_id ​       ON state.l_state_id = suburb.l_state_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
    ​client.l_client_id > 0     ​client.l_client_id > 0 
projects/ceo_bi_reports.txt ยท Last modified: 2019/06/20 10:39 by 192.168.2.152