Hi All,
Our IT department made a select query long time ago in excel and my client wants me to add another field name from the sql server. The field name is "client_status" from database "Reporting" table named "SXD_Client". Here is the query:
SELECT DISTINCT (svl.omr_fk_metric_id) AS metric_id, svl.fk_organization_id AS client_id, svl.Client_Name, svl.geography_name , svl.region_name, svl.svl_name AS metric_name, svl.GartnerGroupCategoryName, svl.GartnerGroupName, client_specific='N', ESPortfolio = ( CASE svl.GartnerGroupCategoryName WHEN 'Infrastructure Technology Outsourcing' THEN 'ITO' WHEN 'Applications' THEN 'APPS' WHEN 'Business Process Outsourcing' THEN 'BPO' WHEN 'Industry Solutions' THEN 'Industry Solutions' Else 'Other' END ), svl.GartnerGroupName as ESServiceLine, SXD_Client.dbo.client_status
FROM (SXD_SHADOW.dbo.business_line AS bl INNER JOIN SXD_SHADOW.dbo.category_type AS cat ON bl.category_type_id=cat.category_type_id) INNER JOIN (SELECT mblr.fk_business_line_id, mg.name AS svl_name, ceRel.ce_relation, m.client_viewable as svl_client_viewable, ceRel.fk_organization_id, ceRel.omr_fk_metric_id, ceRel.Client_Name, gg.GartnerGroupName, ggc.GartnerGroupCategoryName, ceRel.geography_name, ceRel.region_name FROM SXD_SHADOW.dbo.metric_business_line_relationship mblr INNER JOIN SXD_SHADOW.dbo.metric m ON mblr.fk_metric_id = m.metric_id INNER JOIN SXD_SHADOW.dbo.metric_group_relationship mgr ON m.metric_id = mgr.fk_metric_id INNER JOIN SXD_SHADOW.dbo.metric_group mg ON mgr.fk_metric_group_id = mg.metric_group_id Left join SXD_SHADOW.dbo.MetricGartnerGroup metgg on metgg.metric_id = m.metric_id Left join SXD_SHADOW.dbo.GartnerGroup gg on gg.GartnerGroupId = metgg.GartnerGroupId Left join SXD_SHADOW.dbo.GartnerGroupCategory ggc on ggc.GartnerGroupCategoryId = gg.GartnerGroupCategoryId INNER JOIN ( SELECT fk_metric_id as omr_fk_metric_id, ce_relation=1, Client_viewable, fk_organization_id, org.name as Client_Name, loc.geography_name, loc.region_name FROM SXD_SHADOW.dbo.organization_metric_relationship omr INNER JOIN SXD_SHADOW.dbo.organization org on org.organization_id = fk_organization_id INNER JOIN Reporting.dbo.SXD_Client client on org.organization_id = client.client_id INNER JOIN Reporting.dbo.SXD_Location loc on client.location_id = loc.location_id WHERE (omr.end_date>=getdate()) ) ceRel ON m.metric_id=ceRel.omr_fk_metric_id WHERE (m.end_date >= getdate()) ) AS svl ON bl.business_line_id=svl.fk_business_line_id WHERE ce_relation = 1 And cat.category_type_id = 2 and svl.client_name not like 'ztest%' ORDER BY svl.omr_fk_metric_id, svl_name
As you can see its pulling data from other sources as well, but i cannot figure out where and how to include the needed field.
Can somebody help me on this?:banghead:
Our IT department made a select query long time ago in excel and my client wants me to add another field name from the sql server. The field name is "client_status" from database "Reporting" table named "SXD_Client". Here is the query:
SELECT DISTINCT (svl.omr_fk_metric_id) AS metric_id, svl.fk_organization_id AS client_id, svl.Client_Name, svl.geography_name , svl.region_name, svl.svl_name AS metric_name, svl.GartnerGroupCategoryName, svl.GartnerGroupName, client_specific='N', ESPortfolio = ( CASE svl.GartnerGroupCategoryName WHEN 'Infrastructure Technology Outsourcing' THEN 'ITO' WHEN 'Applications' THEN 'APPS' WHEN 'Business Process Outsourcing' THEN 'BPO' WHEN 'Industry Solutions' THEN 'Industry Solutions' Else 'Other' END ), svl.GartnerGroupName as ESServiceLine, SXD_Client.dbo.client_status
FROM (SXD_SHADOW.dbo.business_line AS bl INNER JOIN SXD_SHADOW.dbo.category_type AS cat ON bl.category_type_id=cat.category_type_id) INNER JOIN (SELECT mblr.fk_business_line_id, mg.name AS svl_name, ceRel.ce_relation, m.client_viewable as svl_client_viewable, ceRel.fk_organization_id, ceRel.omr_fk_metric_id, ceRel.Client_Name, gg.GartnerGroupName, ggc.GartnerGroupCategoryName, ceRel.geography_name, ceRel.region_name FROM SXD_SHADOW.dbo.metric_business_line_relationship mblr INNER JOIN SXD_SHADOW.dbo.metric m ON mblr.fk_metric_id = m.metric_id INNER JOIN SXD_SHADOW.dbo.metric_group_relationship mgr ON m.metric_id = mgr.fk_metric_id INNER JOIN SXD_SHADOW.dbo.metric_group mg ON mgr.fk_metric_group_id = mg.metric_group_id Left join SXD_SHADOW.dbo.MetricGartnerGroup metgg on metgg.metric_id = m.metric_id Left join SXD_SHADOW.dbo.GartnerGroup gg on gg.GartnerGroupId = metgg.GartnerGroupId Left join SXD_SHADOW.dbo.GartnerGroupCategory ggc on ggc.GartnerGroupCategoryId = gg.GartnerGroupCategoryId INNER JOIN ( SELECT fk_metric_id as omr_fk_metric_id, ce_relation=1, Client_viewable, fk_organization_id, org.name as Client_Name, loc.geography_name, loc.region_name FROM SXD_SHADOW.dbo.organization_metric_relationship omr INNER JOIN SXD_SHADOW.dbo.organization org on org.organization_id = fk_organization_id INNER JOIN Reporting.dbo.SXD_Client client on org.organization_id = client.client_id INNER JOIN Reporting.dbo.SXD_Location loc on client.location_id = loc.location_id WHERE (omr.end_date>=getdate()) ) ceRel ON m.metric_id=ceRel.omr_fk_metric_id WHERE (m.end_date >= getdate()) ) AS svl ON bl.business_line_id=svl.fk_business_line_id WHERE ce_relation = 1 And cat.category_type_id = 2 and svl.client_name not like 'ztest%' ORDER BY svl.omr_fk_metric_id, svl_name
As you can see its pulling data from other sources as well, but i cannot figure out where and how to include the needed field.
Can somebody help me on this?:banghead: