June 15, 2012 at 8:32 am
I'm right with you on that Sean. There are some other quirks in the query which suggest that if it's generating correct results, it is, to some extent, a happy accident.
FWIW I think simplifying it may iron out some of the controversial areas.
Declare @portfolioId Varchar(8000),
@ParentWorkids Varchar(50)
Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000' -- this looks like TWO concatenated portfolioIds
set @ParentWorkids = '1801a2g0000ih0vl2abg000000'
---------------------------------------------------------------------------
SELECT child_work_id = [object_id],
PrimaryBBPProcess = MAX(CASE WHEN tagset_name = ' Primary Financial BBP Metric Impacted' THEN tag_name ELSE NULL END),
PrimaryBBPlevel2 = MAX(CASE WHEN tagset_name = 'Primary Fin. BBP Metric (Level 2)' THEN tag_name ELSE NULL END),
PrimaryBBPlevel3 = MAX(CASE WHEN tagset_name = 'Primary Fin. BBP Metric (Level 3)' THEN tag_name ELSE NULL END),
WorkstreamBusinessGroup = MAX(CASE WHEN tagset_name = 'Workstream Business Group' THEN tag_name ELSE NULL END),
WorkBusArea = MAX(CASE WHEN tagset_name = 'Workstream Business Focus Area' THEN tag_name ELSE NULL END),
WorkProjectArea = MAX(CASE WHEN tagset_name = 'Workstream Project Focus Area' THEN tag_name ELSE NULL END)
INTO #View_Tag
FROM view_tag
GROUP BY [object_id]
---------------------------------------------------------------------------
SelectTOP 20
vh.parent_work_id As ParentWorkId,
vh.parent_name As ParentName,
vh.child_work_id As ChildWorkId,
vh.child_name As ChildName,
vh.child_sequence_id As SequencesNo,
vh.parent_owner_id As OwnerNo,
vh.child_work_type_code As ChildworkCode,
vh.child_status_current As ChildStatus,
(Select (first_name+ ' ' + last_name)
from view_user
where user_id = vh.parent_owner_id) as ProjectManager,
Stuff((select ',' + (first_name+ ' ' + last_name)
from vieW_user vw
join view_work_role r on vw.user_id = r.user_id
Join view_configurable_role cr On cr.role_id =r.role_id and cr.name = 'Process Owner' and r.work_id = vh.child_work_id
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ProjectSponsor,
(select name
from View_Work
where work_id = vh.child_work_id and type_name = 'Location') As Location,
Stuff((select ',' +(first_name+ ' ' + last_name)
from vieW_user vw
join view_work_role r on vw.user_id = r.user_id
where r. role = 'Championship' and r.work_id = vh.child_work_id
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as OtherTeamMembers,
Stuff((select (first_name+ ' ' + last_name)
from vieW_user vw
join view_work_role r on vw.user_id = r.user_id
Join view_configurable_role cr On cr.role_id = r.role_id and cr.name = 'CI Manager' and r.work_id = vh.child_work_id
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS CIManager,
(Select (first_name+ ' ' + last_name)
From view_User vw
join View_work_role r on vw.user_id = r.user_id
Join view_tag vt on vt.object_id = r.work_id and vt.tag_name = 'Controller' and r.work_id = vh.child_work_id) AS Controller,
(Select (first_name+ ' ' + last_name)
From view_User vw
join View_work_role r on vw.user_id = r.user_id
Join view_tag vt on vt.object_id = r.work_id
and vt.tag_name = 'Master Black Belt' and r.work_id = vh.child_work_id) as MasterBB,
(Select value from view_work_custom_field where work_id = vh.child_work_id and name = 'Project Objective') as ProjectObjective,
(Select value from view_work_custom_field where work_id = vh.child_work_id and name = 'Problem Statement') as ProblemStatement,
vt.PrimaryBBPProcess,
vt.PrimaryBBPlevel2,
vt.PrimaryBBPlevel3,
vt.WorkstreamBusinessGroup,
vt.WorkBusArea,
vt.WorkProjectArea,
---------------------------------------------------------------------------
x.HardSavingFY10,
x.HardSavingFY11,
HardSavingTotal = x.HardSavingFY10 + x.HardSavingFY11,
x.CostAvoidanceFY10,
x.CostAvoidanceFY11,
x.OneTimeImpCostsFY10,
x.OneTimeImpCostsFY11,
x.CapitalExpenditures,
x.CapitalExpenditures
---------------------------------------------------------------------------
FROM View_Work_Hierarchy vh
LEFT JOIN #View_Tag vt ON vt.child_work_id = vh.child_work_id
---------------------------------------------------------------------------
CROSS APPLY (
SELECT mt.item_name,
HardSavingFY10= Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),
HardSavingFY11= Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),
CostAvoidanceFY10= Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),
CostAvoidanceFY11= Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),
OneTimeImpCostsFY10 = Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),
OneTimeImpCostsFY11 = Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),
CapitalExpenditures = Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Capital Expenditures' then m.value End),
CapitalExpenditures =Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Capital Expenditures' then m.value End)
FROM View_Shared_Portfolio vp
--Inner Join View_Metric_Instance mi on mi.linked_project_id = vh.child_work_id -- outer ref
Inner Join View_Metric_Instance mi on mi.linked_project_id = vp.work_id -- doesn't need to be outer ref (vp.work_id = vh.child_work_id)
Inner Join View_Metric_Template mt on mt.metric_template_id = mi.metric_template_id
Inner Join fn_Metrics('2011-01-01', '2012-12-31' )m ON m.metric_instance_id = mi.metric_instance_id
and mt.item_id = m.template_item_id
WHERE vp.work_id = vh.child_work_id -- outer ref
AND (vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)
and mt.template_name = N'Savings / Controller Validation'
and mt.locale_id = N'en'
and mt.item_name IN ( 'Hard Savings','Cost Avoidance','One-Time / Impl. Costs','Capital Expenditures')
GROUP BY mt.item_name
) x
---------------------------------------------------------------------------
Where--(vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)
--And
(vh.parent_work_id = @ParentWorkids Or @ParentWorkids Is Null)
and vh.child_work_type_code Like 'Tollgate%'
and vh.child_depth != 0
--and vh.child_work_id = '1800m380000ioetr0e5g000000'
--and mt.template_name = N'Savings / Controller Validation'
--and mt.locale_id = N'en'
--and mt.item_name IN ( 'Hard Savings','Cost Avoidance','One-Time / Impl. Costs','Capital Expenditures')
GROUP BY vh.parent_work_id,vh.parent_name,vh.child_work_id,vh.child_name,vh.child_sequence_id,vh.parent_owner_id,
vh.child_work_type_code,vh.child_status_current --,mt.item_name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 17, 2012 at 11:29 pm
Hey Guys
I want to add column value and show in the next column.
Like (HardSavingFY10 + HardSavingFY11)
And show it in the another value for each rows
Is there any possibilities..??? If the Column is Null then it is not summin
June 17, 2012 at 11:35 pm
If i Execute the above query
I'm getting Result set as
Msg 2714, Level 16, State 6, Line 8
There is already an object named '#View_Tag' in the database.
June 18, 2012 at 2:45 am
farooq.hbs (6/17/2012)
If i Execute the above queryI'm getting Result set as
Msg 2714, Level 16, State 6, Line 8
There is already an object named '#View_Tag' in the database.
The #temp table will exist when the code is successfully run. For repeated runs of the same batch you must drop the #temp table.
IF OBJECT_ID('tempdb..#View_Tag') IS NOT NULL DROP TABLE #View_Tag
This statement can be placed anywhere in the batch before the temp table is created or after it's finished with. Most folks would put the statement at the top of the batch.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 21, 2012 at 1:25 am
Hey
My Metric Values returning two times Lyk
IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY12,
IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY12,
IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY12,
HardSavingFY11 and Cost AvoidingFY12 Value are not coming in one row for the Project.it is making two rows for hardsaving and cost avoidingFY12 if two value is not null..
I used Max Option for this but i got an error
Help me on this
June 21, 2012 at 1:41 am
farooq.hbs (6/21/2012)
HeyMy Metric Values returning two times Lyk
IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY12,
IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY12,
IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY12,
HardSavingFY11 and Cost AvoidingFY12 Value are not coming in one row for the Project.it is making two rows for hardsaving and cost avoidingFY12 if two value is not null..
I used Max Option for this but i got an error
Help me on this
Not enough information. Can you post the whole query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 21, 2012 at 1:50 am
Hey Chris
I got the Solution
I removed Group by item_name in the Query
thankz For the reply..
I have one more issue in "catch All" queries
Without declaring Local Variable to check in the Where Condition for the Query
How to Check
This is Query With local variable
Declare @portfolioId Varchar(8000),
@ParentWorkids Varchar(50)
Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000'
set @ParentWorkids = '1801a2g0000ih0vl2abg000000'
Select * from Mytable1 join My table2 On
(vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)
And
(vh.parent_work_id = @ParentWorkids Or @ParentWorkids Is Null)
Now i want to write a query Without Declaring local variable because vp.shared_portfolio_id can be Null Or Single Id Or Multiple Id So i should use IN Condition in the Where Clause
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply