May 20, 2014 at 2:09 am
Hi
I have the following Stored Proc
But when i run it using only division
I give the the following errors
BEGIN TRANSACTION
EXEC NODE_POPULATE_STRUCTURED_DETAIL '', 'DV04', '', '0', '', '', 1, 1, 0, 0, '', '', 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 1, 0, 1, '', 0, 0
ROLLBACK TRANSACTION
----------------------------------------------
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Group'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Group'.
Msg 156, Level 15, State 1, Line 46
Incorrect syntax near the keyword 'UNION'.
Can someone please help to see where i am going wrong...?
Regards,
-------------------------------------------------------------------------------------------------------------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[Node_Populate_Structured_Detail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Node_Populate_Structured_Detail]
GO
create procedure dbo.Node_Populate_Structured_Detail (
@Node Node = '',
@Division Division = '',
@Node_Group varchar(20) = '',
@Levels Char(2) = 'A' ,
@Financial_Period_From Financial_Period = '',
@Financial_Period_To Financial_Period = '',
@Labour bit = 1 ,
@Material bit = 1 ,
@Cost_Centre_Include bit = 0 ,
@Resource_Include bit = 0 ,
@Cost_Centres varchar(32) = '',
@Resources varchar(32) = '',
@CostLimit bit = 1 ,
@Sales bit = 1 ,
@OtherSales bit = 1 ,
@COS bit = 1 ,
@AvailableBudget bit = 1 ,
@AvailableSpend bit = 1 ,
@BCWS bit = 1 ,
@LCWS bit = 1 ,
@Scenario bit = 1 ,
@PreComm bit = 1 ,
@Comm bit = 1 ,
@ACWP bit = 1 ,
@AvailableCost bit = 1 ,
@Exclude_Disposed bit = 1,
@Budget_Scenario Budget_Scenario = '',
@ProjectACWP bit = 1,
@ProjectCOS bit = 1)
With Encryption AS
/* $Revision: 1 $ $Author: Arno.legrand $ $Date: 08/12/10 12:58p $ $Modtime: 08/11/12 1:57p $ */
Declare @UserHostName varchar(255),
@MaxFromWorkDate datetime,
@MaxToWorkDate datetime,
@Node_List varchar(8000),
@IndivNode Node,
@Resource_List varchar(8000),
@Resource Material_Resource,
@Cost_Centre_List varchar(8000),
@Cost_Centre Cost_Centre,
@ExecString varchar(8000),
@ExecString2 varchar(8000),
@ExecString3 varchar(8000),
@Fin_List varchar(8000),
@Fin_Period Financial_Period,
@HugeList bit,
@Min_Date varchar(20),
@Max_Date varchar(20),
@Incl_CC char(1),
@Incl_Res char(1),
@IncludeStatus varchar(35),
@Node_Cnt Node,
@Available_To_Spend_F decimal(16,5),
@Available_To_Budget_F decimal(16,5),
@Node_COS_F decimal(16,5),
@Node_Calculate_View char(1),
@Pre_Commitment_Labour_F decimal(16,5),
@Pre_Commitment_Overhead_F decimal(16,5),
@Pre_Commitment_Fixed_Overhead_F decimal(16,5),
@Commitment_Labour_F decimal(16,5),
@Commitment_Overhead_F decimal(16,5),
@Commitment_Fixed_Overhead_F decimal(16,5)
Set nocount on
Exec Get_User_Host_Name @UserHostName OUTPUT
If @@Error <> 0 Return
if @Division = '' and @Node = '' And @Node_Group = ''
Begin
--Please specify a division or a node to inquire on
Exec Show_Application_message 78725
Set nocount off
Return
End
Select @Node_Calculate_View = Node_Calculation_View
From System_Configuration
set @HugeList = 0
if @Node <> ''
Begin
-- List of Nodes (explode structure)
Declare Node_Cursor Insensitive Cursor for
Select Node
From dbo.fn_Node_Explode(@Node, @Levels, 'D')
End
Else If @Division <> ''
Begin
Declare Node_Cursor Insensitive Cursor for
Select Node
From Node
Where Division_Code = @Division
End
Else If @Node_Group <> ''
Begin
Declare Node_Cursor Insensitive Cursor for
Select Node
From Node_Group_Link
Where Node_Group = @Node_Group
End
Select @Node_List = '(''@@@'''
Open Node_Cursor
Fetch Node_Cursor into @IndivNode
While @@Fetch_Status = 0
Begin
if (len(@Node_List) + len(@IndivNode) + 5) > 8000
Begin
Set @HugeList = 1
End
Select @Node_List = @Node_List + ',''' + @IndivNode + ''' '
Fetch Node_Cursor into @IndivNode
End
Select @Node_List = @Node_List + ')'
Close Node_Cursor
Deallocate Node_Cursor
If @Resources = '#TMP1'
Begin
--THE Resource TEMPORARY TABLE NAME MAY NOT BE #TMP1. PLEASE CREATE A TEMPORARY TABLE WITH A DIFFERENT NAME
Exec Show_Application_Message 78685
Set nocount off
Return
End
If @Cost_Centres = '#TMP1'
Begin
--THE Cost centre TEMPORARY TABLE NAME MAY NOT BE #TMP1. PLEASE CREATE A TEMPORARY TABLE WITH A DIFFERENT NAME
Exec Show_Application_Message 78721
Set nocount off
Return
End
if (not exists (select * from tempdb..sysobjects where id = object_id(N'tempdb..' + @Resources)) or
Left(Ltrim(@Resources),1) <> '#') and @Material = 1 and @Resources <> ''
Begin
--The Temporary Table ~~ Does Not Exist
Exec Show_Application_Message 78686, @Resources
Set nocount off
Return
End
if (not exists (select * from tempdb..sysobjects where id = object_id(N'tempdb..' + @Cost_Centres)) or
Left(Ltrim(@Cost_Centres),1) <> '#') and @Labour = 1 and @Cost_Centres <> ''
Begin
--The Temporary Table ~~ Does Not Exist
Exec Show_Application_Message 78687, @Cost_Centres
Set nocount off
Return
End
--Check if the temp tables has the correct field names and types
if (Not Exists(Select * from tempdb..Syscolumns
Where ID = Object_ID('tempdb..' + @Resources) and
Name = 'Resource' and prec = 25)) and @Material = 1 and @Resources <> ''
Begin
--THE RESOURCE TEMPORARY TABLE MUST HAVE A FIELD: RESOURCE OF TYPE VARCHAR(25)
Exec Show_Application_Message 78688
Set nocount off
Return
End
if Not Exists(Select * from tempdb..Syscolumns
Where ID = Object_ID('tempdb..' + @Cost_Centres) and
Name = 'Cost_Centre' and prec = 10) and @Labour = 1 and @Cost_Centres <> ''
Begin
--THE COST CENTRE TEMPORARY TABLE MUST HAVE A FIELD: COST_CENTRE OF TYPE VARCHAR(10)
Exec Show_Application_Message 78722
Set nocount off
Return
End
--Temporary tables exist
--Build resource and cost_centre string to use in the select from the views
--Simply joining to a view causes bad performance
if @Material = 1
Begin
if @Resources <> ''
Begin
Select @Resource_List = '('''''
Exec ('Declare ResourceCursor insensitive cursor for
Select Resource From ' + @Resources)
Open ResourceCursor
Fetch ResourceCursor into @Resource
While @@Fetch_Status = 0
Begin
if (len(@Resource_List) + len(@Resource) + 5) > 8000
Begin
--THE TEMPORARY TABLE ~~ CONTAINS TOO MANY RESOURCES. TRY TO SELECT LESS RESOURCES
Exec Show_Application_Message 78723 , @Resources
Set nocount off
Close ResourceCursor
Deallocate ResourceCursor
Return
End
Select @Resource_List = @Resource_List + ', ''' + @Resource + ''''
Fetch ResourceCursor into @Resource
End
Close ResourceCursor
Deallocate ResourceCursor
--Select @Resource_List = Left(@Resource_List, len(@Resource_List) - 1)
Select @Resource_List = @Resource_List + ')'
End
Else
Select @Resource_List = '(''@@@'')'
End
if @Labour = 1
Begin
if @Cost_Centres <> ''
Begin
Select @Cost_Centre_List = '(''@@@'''
Exec ('Declare CostCentreCursor insensitive cursor for
Select Cost_Centre From ' + @Cost_Centres)
Open CostCentreCursor
Fetch CostCentreCursor into @Cost_Centre
While @@Fetch_Status = 0
Begin
if (len(@Cost_Centre_List) + len(@Cost_Centre) + 5) > 8000
Begin
--THE TEMPORARY TABLE ~~ CONTAINS TOO MANY COST CENTRES. TRY TO SELECT LESS COST CENTRES
Exec Show_Application_Message 78724 , @Cost_Centres
Set nocount off
Close CostCentreCursor
Deallocate CostCentreCursor
Return
End
Select @Cost_Centre_List = @Cost_Centre_List + ', ''' + @Cost_Centre + ''''
Fetch CostCentreCursor into @Cost_Centre
End
Close CostCentreCursor
Deallocate CostCentreCursor
--Select @Cost_Centre_List = Left(@Cost_Centre_List, len(@Cost_Centre_List) - 1)
Select @Cost_Centre_List = @Cost_Centre_List + ')'
End
Else
Select @Cost_Centre_List = '(''@@@'')'
End
--changed from getting all fin periods in company_calendar
If @Node <> ''
Begin
Select @MaxFromWorkDate = Start_Date,
@MaxToWorkDate = Case
When End_Date > getdate()
Then End_Date
Else getdate()
End
From Node
Where Node = @Node
End
If @Node_Group <> ''
Begin
Select @MaxFromWorkDate = Min(n.Start_Date),
@MaxToWorkDate = Case
When Max(n.End_Date) > getdate()
Then Max(n.End_Date)
Else getdate()
End
From Node n (nolock)
Join Node_Group_Link ngl (nolock)
On n.Node = ngl.Node
Where ngl.Node_Group = @Node_Group
End
/*Select @MaxFromWorkDate = min(Work_Date),
@MaxToWorkDate = max(Work_Date)
From Company_Calendar*/
if @Financial_Period_From <> ''
select @MaxFromWorkDate = max(work_date)
from Company_Calendar
where Financial_period = @Financial_Period_From
if @Financial_Period_To <> ''
select @MaxToWorkDate = max(work_date)
from Company_Calendar
where Financial_period = @Financial_Period_To
--Make a list of all the financial_periods
Select @Fin_List = '('
/*Declare Fin_Cursor Insensitive Cursor for
Select distinct Financial_Period
From Company_Calendar (nolock)
Where Work_Date between @MaxFromWorkDate and @MaxToWorkDate
Open Fin_Cursor
Fetch Fin_Cursor into @Fin_Period
While @@Fetch_Status = 0
Begin
Select @Fin_List = @Fin_List + '"' + @Fin_Period + '",'
Fetch Fin_Cursor into @Fin_Period
End
Select @Fin_List = Left(@Fin_List, len(@Fin_List) - 1)
Select @Fin_List = @Fin_List + ')'
Close Fin_Cursor
Deallocate Fin_Cursor*/
Select @Fin_List = '(Select Distinct Financial_Period
From Company_Calendar
Where Work_Date Between ''' + cast(@MaxFromWorkDate as varchar(30)) + ''' and ''' + cast(@MaxToWorkDate as varchar(30)) + ''')'
--drop the temp table
if exists (select * from tempdb..sysobjects where id = object_id ('tempdb..#TMP1') and sysstat & 0xf = 3)
drop table #TMP1
if exists (select * from tempdb..sysobjects where id = object_id ('tempdb..#Nodes') and sysstat & 0xf = 3)
drop table #Nodes
Create table #TMP1 (Node varchar(20),
Budget_Checking char(3),
Sales decimal(16,5),
Other_Sales decimal(16,5),
COS_Total decimal(16,5),
Scenario decimal(16,5),
Pre_Comm decimal(16,5),
Comm decimal(16,5),
ACWP decimal(16,5),
AvailableOverspend decimal(16,5),
AvailableBudget decimal(16,5),
AvailableSpend decimal(16,5),
ProjectACWP decimal(16,5),
ProjectCOS decimal(16,5))
Create table #Nodes (Node varchar(20))
if @Node <> ''
Insert into #Nodes
Select Node
From Node_Structure_Output (nolock)
Where User_Id = @UserHostName
Else If @Division <> ''
Insert into #Nodes
Select Node
From Node (nolock)
Where Division_Code = @Division
Else If @Node_Group <> ''
Insert Into #Nodes
Select Node
From Node_Group_Link (nolock)
Where Node_Group = @Node_Group
if @Sales = 1 or @OtherSales = 1
Begin
if @HugeList = 0
Begin
Insert into #TMP1
Exec ('Select
v3.Milestone_Node,
n.Budget_Checking,
Sales = isnull(v3.Sales_To_Date, 0) ,
[Other Sales] = isnull(v3.Other_Sales_To_Date, 0) ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Sales_Summary_V v3 join node n (nolock) on n.node = v3.Milestone_node where V3.Milestone_Node in ' + @Node_List)
End
Else
Begin
Insert into #TMP1
Select
v3.Milestone_Node,
n.Budget_Checking,
'Sales' = isnull(v3.Sales_To_Date, 0) ,
'Other Sales' = isnull(v3.Other_Sales_To_Date, 0) ,
'COS' = 0 ,
'Scenaro' = 0,
'[Pre-Comm]' = 0 ,
'Comm' = 0 ,
'ACWP' = 0,
'AvailableOverspend' = 0,
'AvailableBudget' = 0,
'AvailableSpend' = 0,
'ProjectACWP' = 0,
'ProjectCOS' = 0
from Node_Sales_Summary_V v3 join node n (nolock) on n.node = v3.Milestone_Node
where V3.Milestone_Node in (Select Node from #Nodes)
End
End
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
if (@PreComm = 1 or @Comm = 1) and @Material = 1
Begin
Select @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = isnull(sum(Pre_Comm_Material_Cost), 0) ,
Comm = isnull(sum(Comm_Material_Cost), 0) ,
ACWP = 0,
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Budget_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.budget_checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
if (@ACWP = 1) and @Material = 1 And @Node_Calculate_View = 'F'
Begin
Select @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(Amount), 0),
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Actual_Detail_V v join node n (nolock) on n.node = v.node Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Actual_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.budget_checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
if (@ACWP = 1) and @Material = 1 And @Node_Calculate_View = 'P'
Begin
Select @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(ACTUAL_MATERIAL_COST), 0),
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and BUDGET_FIN_PERIOD in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.budget_checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
if (@ProjectACWP = 1) and @Material = 1
Begin
Select @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = isnull(sum(Actual_Material_Cost), 0) , ProjectCOS = 0
from Node_Project_WIP_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Budget_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.budget_checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
/* Replaced with fn_node_cost_summary */
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
/*if (@PreComm = 1 or @Comm = 1) and @Labour = 1
Begin
Select @ExecString = 'Select
v.Node,
n.Budget_Checking,
"Sales" = 0 ,
"Other Sales" = 0,
"COS" = 0 ,
"Scenaro" = 0,
"Pre-Comm" = isnull(sum(Pre_Comm_Total_Cost), 0) ,
"Comm" = isnull(sum(Comm_Total_Cost), 0) ,
"ACWP" = 0,
"AvailableOverspend" = 0 ,
"AvailableBudget" = 0,
"AvailableSpend" = 0,
"ProjectACWP" = 0,
"ProjectCOS" = 0
from Node_Labour_Comm_Actual_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Budget_Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Budget_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End*/
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
if (@ACWP = 1) and @Labour = 1 And @Node_Calculate_View = 'F'
Begin
Select @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(Actual_Total_Cost), 0),
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Amounts_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Budget_Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Actual_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
if (@ACWP = 1) and @Labour = 1 And @Node_Calculate_View = 'P'
Begin
Declare @LabSP Table
(Node Node,
Labour_Resource Labour_Resource,
Budget_FIN_Period Financial_Period,
Budget_Cost_Centre cost_centre,
Budget_Employee employee_id,
Timesheet_Employee employee_id,
Timesheet_Date smalldatetime,
Pre_Comm_Hours decimal(16,9),
Pre_Comm_Labour_Cost decimal(16,9),
Pre_Comm_Overhead_Cost decimal(16,9),
Pre_Comm_Fixed_Overhead_Cost decimal(16,9),
Pre_Comm_Total_Cost decimal(16,9),
Comm_Hours decimal(16,9),
Comm_Labour_Cost decimal(16,9),
Comm_Overhead_Cost decimal(16,9),
Comm_Fixed_Overhead_Cost decimal(16,9),
Comm_Total_Cost decimal(16,9),
Actual_Hours decimal(16,9),
Actual_Total_Rate decimal(16,9),
Actual_Labour_Cost decimal(16,9),
Actual_Overhead_Cost decimal(16,9),
Actual_Fixed_Overhead_Cost decimal(16,9),
Actual_Total_Cost decimal(16,9)
)
Insert @LabSP
EXEC Node_Labour_Comm_Actual @Node, 0,
@Division,
@Node_Group,
@Levels,
@MaxFromWorkDate,
@MaxToWorkDate,
@Cost_Centres,
@Cost_Centre_Include
Insert into #TMP1
Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(Actual_Total_Cost), 0),
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
From @LabSP v join node n (nolock) on n.node = v.node
Group By v.Node, n.Budget_Checking
End
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
if (@ProjectACWP = 1) and @Labour = 1
Begin
Select @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = isnull(sum(Actual_Total_Cost), 0),
ProjectCOS = 0
from Node_Labour_Amounts_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Budget_Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Budget_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End
if @AvailableCost = 1 and @Material = 1
Begin
Select @ExecString = 'Select
v2.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = isnull(sum(v2.Available_Mat_Cost_Overspend), 0) ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Material_Budget_V v2 join node n (nolock) on n.node = v2.node
Where v2.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Financial_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v2.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v2.Node, n.Budget_Checking')
End
if @AvailableCost = 1 and @Labour = 1
Begin
Select @ExecString = 'Select
v2.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = isnull(sum(v2.Available_Total_With_Overspend), 0) ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Budget_Amounts_V v2 join node n (nolock) on n.node = v2.node
Where v2.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Financial_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v2.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v2.Node, n.Budget_Checking')
End
if @Scenario = 1 and @Labour = 1
Begin
Select @ExecString = 'Select
v.Node,
n.budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = isnull(sum(Budget_Total_Cost),0),
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Budget_Scenario_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Financial_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' and Scenario = ''' + @Budget_Scenario + ''' Group By v.Node, n.Budget_Checking')
End
if @Scenario = 1 and @Material = 1
Begin
Select @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = isnull(sum(Budget_Material_Cost),0),
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Material_Budget_Scen_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Material_Resource '
if @Resource_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Financial_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 +
@Fin_List + ' and Scenario = ''' + @Budget_Scenario + ''' Group By v.Node, n.Budget_Checking')
End
if @COS = 1 or @AvailableBudget = 1 or @AvailableSpend = 1 or ((@PreComm = 1 or @Comm = 1) and @Labour = 1)
Begin
Declare @Nodes_AT Table (Primary_Key int IDENTITY,
Node varchar(20))
Declare @Node_Counter int,
@Loop_Counter int
Insert Into @Nodes_AT
Select Node
From #Nodes
Select @Node_Counter = 1
Select @Loop_Counter = (Select Count(*) From @Nodes_AT)
While @Loop_Counter > 0 And @Node_Counter <= @Loop_Counter
Begin
Select @Node_Cnt = (Select Node
From @Nodes_AT
Where Primary_Key = @Node_Counter)
select @Node_COS_F = sum(COS_Material + COS_Fixed_Overhead + COS_Variable_Overhead + COS_Labour)
from order_line_cos olc
join journal j
on j.source_timestamp = olc.date_time
and j.account = olc.account
where node = @Node_Cnt
and financial_period >= Case when @Financial_Period_From = '' then financial_period else @Financial_Period_From end
and financial_period <= Case when @Financial_Period_To = '' then financial_period else @Financial_Period_To end
Select @Available_To_Budget_F = Available_To_Budget,
@Available_To_Spend_F = Available_To_Spend,
@Pre_Commitment_Labour_F = Pre_Commitment_Labour,
@Pre_Commitment_Overhead_F = Pre_Commitment_Overhead,
@Pre_Commitment_Fixed_Overhead_F = Pre_Commitment_Fixed_Overhead,
@Commitment_Labour_F = Commitment_Labour,
@Commitment_Overhead_F = Commitment_Overhead,
@Commitment_Fixed_Overhead_F = Commitment_Fixed_Overhead
From dbo.fn_Node_Cost_Summary (@Node_Cnt, @Budget_Scenario, 0)
If Not (@COS = 1 or @AvailableBudget = 1 or @AvailableSpend = 1)
Begin
Select @Available_To_Budget_F = 0,
@Available_To_Spend_F = 0,
@Node_COS_F = 0
End
If Not ((@PreComm = 1 or @Comm = 1) and @Labour = 1)
Begin
Select @Pre_Commitment_Labour_F = 0,
@Pre_Commitment_Overhead_F = 0,
@Pre_Commitment_Fixed_Overhead_F = 0,
@Commitment_Labour_F = 0,
@Commitment_Overhead_F = 0,
@Commitment_Fixed_Overhead_F = 0
End
Insert Into #TMP1
Select n.Node,
n.Budget_Checking,
'Sales' = 0,
'Other Sales' = 0 ,
'COS' = isnull(@Node_COS_F, 0),
'Scenaro' = 0,
'[Pre-Comm]' = isnull(@Pre_Commitment_Labour_F + @Pre_Commitment_Overhead_F + @Pre_Commitment_Fixed_Overhead_F, 0) ,
'Comm' = isnull(@Commitment_Labour_F + @Commitment_Overhead_F + @Commitment_Fixed_Overhead_F, 0) ,
'ACWP' = 0,
'AvailableOverspend' = 0 ,
'AvailableBudget' = isnull(@Available_To_Budget_F, 0),
'AvailableSpend' = isnull(@Available_To_Spend_F, 0),
'ProjectACWP' = 0,
'ProjectCOS' = 0
From Node n
Where n.Node = @Node_Cnt
Group By n.Node, n.Budget_Checking
Select @Node_Counter = @Node_Counter + 1
End
/* if @HugeList = 0
Insert into #TMP1
Exec (
'Select
v.Node,
n.budget_Checking,
"Sales" = 0 ,
"Other Sales" = 0 ,
"COS" = isnull(sum(COS_To_Date),0),
"Scenaro" = 0,
"Pre-Comm" = 0 ,
"Comm" = 0 ,
"ACWP" = 0,
"AvailableOverspend" = 0 ,
"AvailableBudget" = isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Cost_Of_Node),0),
"AvailableSpend" = case when n.budget_Checking = "Y" then isnull(sum(Actual),0) + isnull(sum(Commitment),0) else isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Actual),0) + isnull(sum(Commitment),0) end,
"ProjectACWP" = 0,
"ProjectCOS" = 0
from Node_Cost_Summary_V v join node n (nolock) on n.node = v.node
Where v.node in ' + @Node_List + '
GROUP BY v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Select
v.Node,
n.Budget_Checking,
'Sales' = 0 ,
'Other Sales' = 0 ,
'COS' = isnull(sum(COS_To_Date),0),
'Scenaro' = 0,
'Pre-Comm' = 0 ,
'Comm' = 0 ,
'ACWP' = 0,
'AvailableOverspend' = 0 ,
'AvailableBudget' = isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Budget),0),
'AvailableSpend' = case when n.Budget_Checking = 'Y' then isnull(sum(Actual),0) + isnull(sum(Commitment),0) else isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Actual),0) + isnull(sum(Commitment),0) end,
'ProjectACWP' = 0,
'ProjectCOS' = 0
from Node_Cost_Summary_V v join node n (nolock) on n.node = v.node
Where v.node in (Select Node from #Nodes)
GROUP BY v.Node, n.Budget_Checking*/
End
if @ProjectCOS = 1
Begin
if @HugeList = 0
Insert into #TMP1
Exec (
'Select
v.Node,
n.budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = isnull(sum(COS_To_Date),0)
from Node_Cost_Summary_V v inner merge join node n (nolock) on n.node = v.node
Where v.node in ' + @Node_List + '
GROUP BY v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Select
v.Node,
n.Budget_Checking,
'Sales' = 0 ,
'Other Sales' = 0 ,
'COS' = 0,
'Scenaro' = 0,
'[Pre-Comm]' = 0 ,
'Comm' = 0 ,
'ACWP' = 0,
'AvailableOverspend' = 0 ,
'AvailableBudget' = 0,
'AvailableSpend' = 0,
'ProjectACWP' = 0,
'ProjectCOS' = isnull(sum(COS_To_Date),0)
--CHANGED FOR TF1 BY ARNO from Node_Project_WIP_Cost_Summary_V v inner merge join node n (nolock) on n.node = v.node
from Node_Cost_Summary_V v inner merge join node n (nolock) on n.node = v.node
Where v.node in (Select Node from #Nodes)
GROUP BY v.Node, n.Budget_Checking
End
--Exclude Disposed Nodes if option selected
If @Exclude_Disposed = 1
select @IncludeStatus = '(''A'',''C'',''F'',''I'',''P'',''R'',''S'')'
else
select @IncludeStatus = '(''A'',''C'',''D'',''F'',''I'',''P'',''R'',''S'')'
Select @Min_Date = cast(@MaxFromWorkDate as varchar),
@Max_Date = cast(@MaxToWorkDate as varchar),
@Incl_CC = cast(@Cost_Centre_Include as varchar),
@Incl_Res = cast(@Resource_Include as varchar)
select @ExecString = '', @ExecString2 = '', @ExecString3 = ''
if @Material = 1
Select @ExecString = ' Select m.Node, n.budget_checking,
Budgeted = isnull(sum(Budgeted_Value), 0),
Approved = isnull(sum(Approved_Value), 0)
from Material_Budget m (nolock) join node n (nolock) on n.node = m.node
where m.Financial_Period in
(Select distinct Financial_Period
from Company_Calendar
where Work_Date between ''' + @Min_Date + ''' and
''' + @Max_Date + ''') and
((' + @Incl_Res + ' = 1 and
Resource in ' + @Resource_List + ') OR
(' + @Incl_Res + ' = 0 and
Resource not in ' + @Resource_List + '))
group by m.node, n.budget_checking '
if @Material = 1 and @Labour = 1
Select @ExecString2 = ' UNION ALL'-- LLJ 2010-10-13
select 'Begin test'
if @Labour = 1
Select @ExecString3 = ' select l.Node, n.budget_Checking,
Budgeted = isnull(sum((Budgeted_Labour_Rate + Budgeted_Overhead_Rate +
Budgeted_Fixed_Overhead_Rate) * Budgeted_Hours), 0),
Approved = isnull(sum((Approved_Labour_Rate + Approved_Overhead_Rate +
Approved_Fixed_Overhead_Rate) * Approved_Hours), 0)
from Labour_Budget l (nolock) join node n (nolock) on n.node = l.node
where l.Financial_Period in
(Select distinct Financial_Period
from Company_Calendar
where Work_Date between ''' + @Min_Date + ''' and
''' + @Max_Date + ''') and
((' + @Incl_CC + ' = 1 and
Cost_Centre in ' + @Cost_Centre_List + ') OR
(' + @Incl_CC + ' = 0 and
Cost_Centre not in ' + @Cost_Centre_List + '))
group by l.node, n.Budget_Checking '
if @Node <> ''
Begin
select 'Node Portion'
EXEC ('
Select distinct [Level/Node] =
convert(char(3), o.Level_Number)
+ '' ''
+ rtrim(o.Node)
+ '' ''
+ convert(Char(30),
n.Description),
o.Level_Number,
o.Node,
n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
o.Sequence,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
(' + @ExecString + @ExecString2 + @ExecString3 + ') as t1
group by t1.Node) as t2
right outer join Node_Structure_Output o on o.node = t2.node
join Node n (nolock) on n.Node = o.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where o.User_Id = ''' + @UserHostName + '''
and n.status in ' + @IncludeStatus + '
group by o.Level_Number, o.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit, o.Sequence
Order by o.Sequence ')
End
else if @Division <> ''
Begin
select 'Division Portion'
Exec ('
Select [Level/Node] = convert(char(3), 0) + '' '' +
''DIVISION '' + rtrim(Division_Code) + '' '' + convert(Char(30),Division_Description), 0,
''DIVISION '' + Division_Code, Division_Description,
[Budgeted Amount] = 0,
[Approved Amount] = 0,
[Cost Limit] = 0 ,
[Sales] = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenario = 0 ,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0 ,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
From Division (nolock)
Where Division_Code = ''' + @Division + '''
UNION
Select distinct [Level/Node] = convert(char(3), 1) + '' '' +
rtrim(n.Node) + '' '' + convert(Char(30),n.Description), 1,
n.Node, n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
(' + @ExecString + @ExecString2 + @ExecString3 + ') as t1
group by t1.Node) as t2
right outer join Node n (nolock) on n.Node = t2.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where n.Division_Code = ''' + @Division + '''
and n.status in ' + @IncludeStatus + '
group by n.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit
Order by 1 ')
End
Else If @Node_Group <> ''
Begin
select 'NodeGroup Portion'
Exec ('Select distinct [Level/Node] = ''0 ' + @Node_Group + ''',
0, ''' + @Node_Group + ''', ''NODE GROUP'',
[Budgeted Amount] = 0,
[Approved Amount] = 0,
[Cost Limit] = 0 ,
Sales = 0,
[Other Sales] = 0,
COS = 0,
Scenario = 0,
[Pre-Comm] = 0,
Comm = 0,
ACWP = 0,
AvailableOverspend = 0,
AvailableBudget = 0,
AvailableSpend = 0,
0,
ProjectACWP = 0,
ProjectCOS = 0
UNION
Select distinct [Level/Node] = ''1 '' +
rtrim(ngl.Node) + '' '' + convert(Char(30), n.Description),
1, ngl.Node, n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
0,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
(' + @ExecString + @ExecString2 + @ExecString3 + ') as t1
group by t1.Node) as t2
right outer join Node_Group_Link ngl on ngl.node = t2.node
join Node n (nolock) on n.Node = ngl.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where ngl.Node_Group = ''' + @Node_Group + '''
and n.status in ' + @IncludeStatus + '
group by ngl.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit')
End
Set nocount off
GO
May 20, 2014 at 2:39 am
Try building the string you're going to execute as a variable and then PRINTing that variable instead of executing it. That'll make it much easier to see what the syntax errors are.
The whole looks inefficient and, more worryingly, vulnerable to SQL injection. I recommend that you parameterise your code and use sp_executesql to run it.
John
May 20, 2014 at 4:18 am
And do you know that NOLOCK can lead to getting incorrect data as well as extra or missing rows? It's a somewhat dangerous operation unless you're sure you can afford it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 20, 2014 at 8:34 am
Wowie Zowie!!! This is a lengthy procedure. There are so many things that need to be dealt with here it would be far easier to just start from scratch.
Cursor after cursor
NOLOCK (this looks like a financial application, understand this hint and get it out of here)
WITH Encryption (this is trivial to reverse and a PITA to work with)
Table valued functions (which from what I have seen deserve a look from a performance perspective)
There is absolutely no way anybody can help you figure out without a LOT more details from you. There are quite a few user defined datatypes and all sorts of stuff that makes this impossible for somebody not on your system.
For anybody else stumbling in here I formatted this procedure.
CREATE PROCEDURE dbo.Node_Populate_Structured_Detail (
@Node Node = ''
,@Division Division = ''
,@Node_Group VARCHAR(20) = ''
,@Levels CHAR(2) = 'A'
,@Financial_Period_From Financial_Period = ''
,@Financial_Period_To Financial_Period = ''
,@Labour BIT = 1
,@Material BIT = 1
,@Cost_Centre_Include BIT = 0
,@Resource_Include BIT = 0
,@Cost_Centres VARCHAR(32) = ''
,@Resources VARCHAR(32) = ''
,@CostLimit BIT = 1
,@Sales BIT = 1
,@OtherSales BIT = 1
,@COS BIT = 1
,@AvailableBudget BIT = 1
,@AvailableSpend BIT = 1
,@BCWS BIT = 1
,@LCWS BIT = 1
,@Scenario BIT = 1
,@PreComm BIT = 1
,@Comm BIT = 1
,@ACWP BIT = 1
,@AvailableCost BIT = 1
,@Exclude_Disposed BIT = 1
,@Budget_Scenario Budget_Scenario = ''
,@ProjectACWP BIT = 1
,@ProjectCOS BIT = 1
)
WITH ENCRYPTION
AS
/* $Revision: 1 $ $Author: Arno.legrand $ $Date: 08/12/10 12:58p $ $Modtime: 08/11/12 1:57p $ */
DECLARE @UserHostName VARCHAR(255)
,@MaxFromWorkDate DATETIME
,@MaxToWorkDate DATETIME
,@Node_List VARCHAR(8000)
,@IndivNode Node
,@Resource_List VARCHAR(8000)
,@Resource Material_Resource
,@Cost_Centre_List VARCHAR(8000)
,@Cost_Centre Cost_Centre
,@ExecString VARCHAR(8000)
,@ExecString2 VARCHAR(8000)
,@ExecString3 VARCHAR(8000)
,@Fin_List VARCHAR(8000)
,@Fin_Period Financial_Period
,@HugeList BIT
,@Min_Date VARCHAR(20)
,@Max_Date VARCHAR(20)
,@Incl_CC CHAR(1)
,@Incl_Res CHAR(1)
,@IncludeStatus VARCHAR(35)
,@Node_Cnt Node
,@Available_To_Spend_F DECIMAL(16, 5)
,@Available_To_Budget_F DECIMAL(16, 5)
,@Node_COS_F DECIMAL(16, 5)
,@Node_Calculate_View CHAR(1)
,@Pre_Commitment_Labour_F DECIMAL(16, 5)
,@Pre_Commitment_Overhead_F DECIMAL(16, 5)
,@Pre_Commitment_Fixed_Overhead_F DECIMAL(16, 5)
,@Commitment_Labour_F DECIMAL(16, 5)
,@Commitment_Overhead_F DECIMAL(16, 5)
,@Commitment_Fixed_Overhead_F DECIMAL(16, 5)
SET NOCOUNT ON
EXEC Get_User_Host_Name @UserHostName OUTPUT
IF @@Error <> 0
RETURN
IF @Division = ''
AND @Node = ''
AND @Node_Group = ''
BEGIN
--Please specify a division or a node to inquire on
EXEC Show_Application_message 78725
SET NOCOUNT OFF
RETURN
END
SELECT @Node_Calculate_View = Node_Calculation_View
FROM System_Configuration
SET @HugeList = 0
IF @Node <> ''
BEGIN
-- List of Nodes (explode structure)
DECLARE Node_Cursor INSENSITIVE CURSOR
FOR
SELECT Node
FROM dbo.fn_Node_Explode(@Node, @Levels, 'D')
END
ELSE IF @Division <> ''
BEGIN
DECLARE Node_Cursor INSENSITIVE CURSOR
FOR
SELECT Node
FROM Node
WHERE Division_Code = @Division
END
ELSE IF @Node_Group <> ''
BEGIN
DECLARE Node_Cursor INSENSITIVE CURSOR
FOR
SELECT Node
FROM Node_Group_Link
WHERE Node_Group = @Node_Group
END
SELECT @Node_List = '(''@@@'''
OPEN Node_Cursor
FETCH Node_Cursor
INTO @IndivNode
WHILE @@Fetch_Status = 0
BEGIN
IF (len(@Node_List) + len(@IndivNode) + 5) > 8000
BEGIN
SET @HugeList = 1
END
SELECT @Node_List = @Node_List + ',''' + @IndivNode + ''' '
FETCH Node_Cursor
INTO @IndivNode
END
SELECT @Node_List = @Node_List + ')'
CLOSE Node_Cursor
DEALLOCATE Node_Cursor
IF @Resources = '#TMP1'
BEGIN
--THE Resource TEMPORARY TABLE NAME MAY NOT BE #TMP1. PLEASE CREATE A TEMPORARY TABLE WITH A DIFFERENT NAME
EXEC Show_Application_Message 78685
SET NOCOUNT OFF
RETURN
END
IF @Cost_Centres = '#TMP1'
BEGIN
--THE Cost centre TEMPORARY TABLE NAME MAY NOT BE #TMP1. PLEASE CREATE A TEMPORARY TABLE WITH A DIFFERENT NAME
EXEC Show_Application_Message 78721
SET NOCOUNT OFF
RETURN
END
IF (
NOT EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..' + @Resources)
)
OR Left(Ltrim(@Resources), 1) <> '#'
)
AND @Material = 1
AND @Resources <> ''
BEGIN
--The Temporary Table ~~ Does Not Exist
EXEC Show_Application_Message 78686
,@Resources
SET NOCOUNT OFF
RETURN
END
IF (
NOT EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..' + @Cost_Centres)
)
OR Left(Ltrim(@Cost_Centres), 1) <> '#'
)
AND @Labour = 1
AND @Cost_Centres <> ''
BEGIN
--The Temporary Table ~~ Does Not Exist
EXEC Show_Application_Message 78687
,@Cost_Centres
SET NOCOUNT OFF
RETURN
END
--Check if the temp tables has the correct field names and types
IF (
NOT EXISTS (
SELECT *
FROM tempdb..Syscolumns
WHERE ID = Object_ID('tempdb..' + @Resources)
AND NAME = 'Resource'
AND prec = 25
)
)
AND @Material = 1
AND @Resources <> ''
BEGIN
--THE RESOURCE TEMPORARY TABLE MUST HAVE A FIELD: RESOURCE OF TYPE VARCHAR(25)
EXEC Show_Application_Message 78688
SET NOCOUNT OFF
RETURN
END
IF NOT EXISTS (
SELECT *
FROM tempdb..Syscolumns
WHERE ID = Object_ID('tempdb..' + @Cost_Centres)
AND NAME = 'Cost_Centre'
AND prec = 10
)
AND @Labour = 1
AND @Cost_Centres <> ''
BEGIN
--THE COST CENTRE TEMPORARY TABLE MUST HAVE A FIELD: COST_CENTRE OF TYPE VARCHAR(10)
EXEC Show_Application_Message 78722
SET NOCOUNT OFF
RETURN
END
--Temporary tables exist
--Build resource and cost_centre string to use in the select from the views
--Simply joining to a view causes bad performance
IF @Material = 1
BEGIN
IF @Resources <> ''
BEGIN
SELECT @Resource_List = '('''''
EXEC (
'Declare ResourceCursor insensitive cursor for
Select Resource From ' + @Resources
)
OPEN ResourceCursor
FETCH ResourceCursor
INTO @Resource
WHILE @@Fetch_Status = 0
BEGIN
IF (len(@Resource_List) + len(@Resource) + 5) > 8000
BEGIN
--THE TEMPORARY TABLE ~~ CONTAINS TOO MANY RESOURCES. TRY TO SELECT LESS RESOURCES
EXEC Show_Application_Message 78723
,@Resources
SET NOCOUNT OFF
CLOSE ResourceCursor
DEALLOCATE ResourceCursor
RETURN
END
SELECT @Resource_List = @Resource_List + ', ''' + @Resource + ''''
FETCH ResourceCursor
INTO @Resource
END
CLOSE ResourceCursor
DEALLOCATE ResourceCursor
--Select @Resource_List = Left(@Resource_List, len(@Resource_List) - 1)
SELECT @Resource_List = @Resource_List + ')'
END
ELSE
SELECT @Resource_List = '(''@@@'')'
END
IF @Labour = 1
BEGIN
IF @Cost_Centres <> ''
BEGIN
SELECT @Cost_Centre_List = '(''@@@'''
EXEC (
'Declare CostCentreCursor insensitive cursor for
Select Cost_Centre From ' + @Cost_Centres
)
OPEN CostCentreCursor
FETCH CostCentreCursor
INTO @Cost_Centre
WHILE @@Fetch_Status = 0
BEGIN
IF (len(@Cost_Centre_List) + len(@Cost_Centre) + 5) > 8000
BEGIN
--THE TEMPORARY TABLE ~~ CONTAINS TOO MANY COST CENTRES. TRY TO SELECT LESS COST CENTRES
EXEC Show_Application_Message 78724
,@Cost_Centres
SET NOCOUNT OFF
CLOSE CostCentreCursor
DEALLOCATE CostCentreCursor
RETURN
END
SELECT @Cost_Centre_List = @Cost_Centre_List + ', ''' + @Cost_Centre + ''''
FETCH CostCentreCursor
INTO @Cost_Centre
END
CLOSE CostCentreCursor
DEALLOCATE CostCentreCursor
--Select @Cost_Centre_List = Left(@Cost_Centre_List, len(@Cost_Centre_List) - 1)
SELECT @Cost_Centre_List = @Cost_Centre_List + ')'
END
ELSE
SELECT @Cost_Centre_List = '(''@@@'')'
END
--changed from getting all fin periods in company_calendar
IF @Node <> ''
BEGIN
SELECT @MaxFromWorkDate = Start_Date
,@MaxToWorkDate = CASE
WHEN End_Date > getdate()
THEN End_Date
ELSE getdate()
END
FROM Node
WHERE Node = @Node
END
IF @Node_Group <> ''
BEGIN
SELECT @MaxFromWorkDate = Min(n.Start_Date)
,@MaxToWorkDate = CASE
WHEN Max(n.End_Date) > getdate()
THEN Max(n.End_Date)
ELSE getdate()
END
FROM Node n(NOLOCK)
INNER JOIN Node_Group_Link ngl(NOLOCK) ON n.Node = ngl.Node
WHERE ngl.Node_Group = @Node_Group
END
/*Select @MaxFromWorkDate = min(Work_Date),
@MaxToWorkDate = max(Work_Date)
From Company_Calendar*/
IF @Financial_Period_From <> ''
SELECT @MaxFromWorkDate = max(work_date)
FROM Company_Calendar
WHERE Financial_period = @Financial_Period_From
IF @Financial_Period_To <> ''
SELECT @MaxToWorkDate = max(work_date)
FROM Company_Calendar
WHERE Financial_period = @Financial_Period_To
--Make a list of all the financial_periods
SELECT @Fin_List = '('
/*Declare Fin_Cursor Insensitive Cursor for
Select distinct Financial_Period
From Company_Calendar (nolock)
Where Work_Date between @MaxFromWorkDate and @MaxToWorkDate
Open Fin_Cursor
Fetch Fin_Cursor into @Fin_Period
While @@Fetch_Status = 0
Begin
Select @Fin_List = @Fin_List + '"' + @Fin_Period + '",'
Fetch Fin_Cursor into @Fin_Period
End
Select @Fin_List = Left(@Fin_List, len(@Fin_List) - 1)
Select @Fin_List = @Fin_List + ')'
Close Fin_Cursor
Deallocate Fin_Cursor*/
SELECT @Fin_List = '(Select Distinct Financial_Period
From Company_Calendar
Where Work_Date Between ''' + cast(@MaxFromWorkDate AS VARCHAR(30)) + ''' and ''' + cast(@MaxToWorkDate AS VARCHAR(30)) + ''')'
--drop the temp table
IF EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE id = object_id('tempdb..#TMP1')
AND sysstat & 0xF = 3
)
DROP TABLE #TMP1
IF EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE id = object_id('tempdb..#Nodes')
AND sysstat & 0xF = 3
)
DROP TABLE #Nodes
CREATE TABLE #TMP1 (
Node VARCHAR(20)
,Budget_Checking CHAR(3)
,Sales DECIMAL(16, 5)
,Other_Sales DECIMAL(16, 5)
,COS_Total DECIMAL(16, 5)
,Scenario DECIMAL(16, 5)
,Pre_Comm DECIMAL(16, 5)
,Comm DECIMAL(16, 5)
,ACWP DECIMAL(16, 5)
,AvailableOverspend DECIMAL(16, 5)
,AvailableBudget DECIMAL(16, 5)
,AvailableSpend DECIMAL(16, 5)
,ProjectACWP DECIMAL(16, 5)
,ProjectCOS DECIMAL(16, 5)
)
CREATE TABLE #Nodes (Node VARCHAR(20))
IF @Node <> ''
INSERT INTO #Nodes
SELECT Node
FROM Node_Structure_Output(NOLOCK)
WHERE User_Id = @UserHostName
ELSE IF @Division <> ''
INSERT INTO #Nodes
SELECT Node
FROM Node(NOLOCK)
WHERE Division_Code = @Division
ELSE IF @Node_Group <> ''
INSERT INTO #Nodes
SELECT Node
FROM Node_Group_Link(NOLOCK)
WHERE Node_Group = @Node_Group
IF @Sales = 1
OR @OtherSales = 1
BEGIN
IF @HugeList = 0
BEGIN
INSERT INTO #TMP1
EXEC (
'Select
v3.Milestone_Node,
n.Budget_Checking,
Sales = isnull(v3.Sales_To_Date, 0) ,
[Other Sales] = isnull(v3.Other_Sales_To_Date, 0) ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Sales_Summary_V v3 join node n (nolock) on n.node = v3.Milestone_node where V3.Milestone_Node in ' + @Node_List
)
END
ELSE
BEGIN
INSERT INTO #TMP1
SELECT v3.Milestone_Node
,n.Budget_Checking
,'Sales' = isnull(v3.Sales_To_Date, 0)
,'Other Sales' = isnull(v3.Other_Sales_To_Date, 0)
,'COS' = 0
,'Scenaro' = 0
,'[Pre-Comm]' = 0
,'Comm' = 0
,'ACWP' = 0
,'AvailableOverspend' = 0
,'AvailableBudget' = 0
,'AvailableSpend' = 0
,'ProjectACWP' = 0
,'ProjectCOS' = 0
FROM Node_Sales_Summary_V v3
INNER JOIN node n(NOLOCK) ON n.node = v3.Milestone_Node
WHERE V3.Milestone_Node IN (
SELECT Node
FROM #Nodes
)
END
END
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
IF (
@PreComm = 1
OR @Comm = 1
)
AND @Material = 1
BEGIN
SELECT @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = isnull(sum(Pre_Comm_Material_Cost), 0) ,
Comm = isnull(sum(Comm_Material_Cost), 0) ,
ACWP = 0,
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Budget_Fin_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.budget_checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
IF (@ACWP = 1)
AND @Material = 1
AND @Node_Calculate_View = 'F'
BEGIN
SELECT @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(Amount), 0),
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Actual_Detail_V v join node n (nolock) on n.node = v.node Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Actual_Fin_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.budget_checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
IF (@ACWP = 1)
AND @Material = 1
AND @Node_Calculate_View = 'P'
BEGIN
SELECT @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(ACTUAL_MATERIAL_COST), 0),
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = 0, ProjectCOS = 0
from Node_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and BUDGET_FIN_PERIOD in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.budget_checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
--Net as Pre Comm, Comm of ACWP checked is (en Material Budget checked)
IF (@ProjectACWP = 1)
AND @Material = 1
BEGIN
SELECT @ExecString = 'Select v.Node, n.Budget_Checking, Sales = 0 , [Other Sales] = 0, COS = 0 , Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 , AvailableBudget = 0, AvailableSpend = 0,
ProjectACWP = isnull(sum(Actual_Material_Cost), 0) , ProjectCOS = 0
from Node_Project_WIP_Material_Comm_Actual_V v join node n (nolock) on n.node = v.node Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Budget_Fin_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.budget_checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
/* Replaced with fn_node_cost_summary */
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
/*if (@PreComm = 1 or @Comm = 1) and @Labour = 1
Begin
Select @ExecString = 'Select
v.Node,
n.Budget_Checking,
"Sales" = 0 ,
"Other Sales" = 0,
"COS" = 0 ,
"Scenaro" = 0,
"Pre-Comm" = isnull(sum(Pre_Comm_Total_Cost), 0) ,
"Comm" = isnull(sum(Comm_Total_Cost), 0) ,
"ACWP" = 0,
"AvailableOverspend" = 0 ,
"AvailableBudget" = 0,
"AvailableSpend" = 0,
"ProjectACWP" = 0,
"ProjectCOS" = 0
from Node_Labour_Comm_Actual_V v join node n (nolock) on n.node = v.node
Where v.node in '
if @HugeList = 1
Select @ExecString = @ExecString + '(Select Node from #Nodes) '
Select @ExecString2 = ' and Budget_Cost_Centre '
if @Cost_Centre_Include = 0
Select @ExecString2 = @ExecString2 + 'not '
Select @ExecString2 = @ExecString2 + 'in '
Select @ExecString3 = ' and Budget_Fin_Period in '
if @HugeList = 1
Insert into #TMP1
Exec (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Exec (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 +
@Fin_List + ' Group By v.Node, n.Budget_Checking')
End*/
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
IF (@ACWP = 1)
AND @Labour = 1
AND @Node_Calculate_View = 'F'
BEGIN
SELECT @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = isnull(sum(Actual_Total_Cost), 0),
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Amounts_V v join node n (nolock) on n.node = v.node
Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Budget_Cost_Centre '
IF @Cost_Centre_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Actual_Fin_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
IF (@ACWP = 1)
AND @Labour = 1
AND @Node_Calculate_View = 'P'
BEGIN
DECLARE @LabSP TABLE (
Node Node
,Labour_Resource Labour_Resource
,Budget_FIN_Period Financial_Period
,Budget_Cost_Centre cost_centre
,Budget_Employee employee_id
,Timesheet_Employee employee_id
,Timesheet_Date SMALLDATETIME
,Pre_Comm_Hours DECIMAL(16, 9)
,Pre_Comm_Labour_Cost DECIMAL(16, 9)
,Pre_Comm_Overhead_Cost DECIMAL(16, 9)
,Pre_Comm_Fixed_Overhead_Cost DECIMAL(16, 9)
,Pre_Comm_Total_Cost DECIMAL(16, 9)
,Comm_Hours DECIMAL(16, 9)
,Comm_Labour_Cost DECIMAL(16, 9)
,Comm_Overhead_Cost DECIMAL(16, 9)
,Comm_Fixed_Overhead_Cost DECIMAL(16, 9)
,Comm_Total_Cost DECIMAL(16, 9)
,Actual_Hours DECIMAL(16, 9)
,Actual_Total_Rate DECIMAL(16, 9)
,Actual_Labour_Cost DECIMAL(16, 9)
,Actual_Overhead_Cost DECIMAL(16, 9)
,Actual_Fixed_Overhead_Cost DECIMAL(16, 9)
,Actual_Total_Cost DECIMAL(16, 9)
)
INSERT @LabSP
EXEC Node_Labour_Comm_Actual @Node
,0
,@Division
,@Node_Group
,@Levels
,@MaxFromWorkDate
,@MaxToWorkDate
,@Cost_Centres
,@Cost_Centre_Include
INSERT INTO #TMP1
SELECT v.Node
,n.Budget_Checking
,Sales = 0
,[Other Sales] = 0
,COS = 0
,Scenaro = 0
,[Pre-Comm] = 0
,Comm = 0
,ACWP = isnull(sum(Actual_Total_Cost), 0)
,AvailableOverspend = 0
,AvailableBudget = 0
,AvailableSpend = 0
,ProjectACWP = 0
,ProjectCOS = 0
FROM @LabSP v
INNER JOIN node n(NOLOCK) ON n.node = v.node
GROUP BY v.Node
,n.Budget_Checking
END
--Net as Pre Comm, Comm of ACWP checked is (en Labour Budget checked)
IF (@ProjectACWP = 1)
AND @Labour = 1
BEGIN
SELECT @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = isnull(sum(Actual_Total_Cost), 0),
ProjectCOS = 0
from Node_Labour_Amounts_V v join node n (nolock) on n.node = v.node
Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Budget_Cost_Centre '
IF @Cost_Centre_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Budget_Fin_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
END
IF @AvailableCost = 1
AND @Material = 1
BEGIN
SELECT @ExecString = 'Select
v2.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = isnull(sum(v2.Available_Mat_Cost_Overspend), 0) ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Material_Budget_V v2 join node n (nolock) on n.node = v2.node
Where v2.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Financial_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v2.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v2.Node, n.Budget_Checking')
END
IF @AvailableCost = 1
AND @Labour = 1
BEGIN
SELECT @ExecString = 'Select
v2.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = isnull(sum(v2.Available_Total_With_Overspend), 0) ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Budget_Amounts_V v2 join node n (nolock) on n.node = v2.node
Where v2.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Cost_Centre '
IF @Cost_Centre_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Financial_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v2.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v2.Node, n.Budget_Checking')
END
IF @Scenario = 1
AND @Labour = 1
BEGIN
SELECT @ExecString = 'Select
v.Node,
n.budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = isnull(sum(Budget_Total_Cost),0),
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Labour_Budget_Scenario_V v join node n (nolock) on n.node = v.node
Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Cost_Centre '
IF @Cost_Centre_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Financial_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Cost_Centre_List + @ExecString3 + @Fin_List + ' and Scenario = ''' + @Budget_Scenario + ''' Group By v.Node, n.Budget_Checking')
END
IF @Scenario = 1
AND @Material = 1
BEGIN
SELECT @ExecString = 'Select
v.Node,
n.Budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenaro = isnull(sum(Budget_Material_Cost),0),
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
from Node_Material_Budget_Scen_V v join node n (nolock) on n.node = v.node
Where v.node in '
IF @HugeList = 1
SELECT @ExecString = @ExecString + '(Select Node from #Nodes) '
SELECT @ExecString2 = ' and Material_Resource '
IF @Resource_Include = 0
SELECT @ExecString2 = @ExecString2 + 'not '
SELECT @ExecString2 = @ExecString2 + 'in '
SELECT @ExecString3 = ' and Financial_Period in '
IF @HugeList = 1
INSERT INTO #TMP1
EXEC (@ExecString + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' Group By v.Node, n.Budget_Checking')
ELSE
INSERT INTO #TMP1
EXEC (@ExecString + @Node_List + @ExecString2 + @Resource_List + @ExecString3 + @Fin_List + ' and Scenario = ''' + @Budget_Scenario + ''' Group By v.Node, n.Budget_Checking')
END
IF @COS = 1
OR @AvailableBudget = 1
OR @AvailableSpend = 1
OR (
(
@PreComm = 1
OR @Comm = 1
)
AND @Labour = 1
)
BEGIN
DECLARE @Nodes_AT TABLE (
Primary_Key INT IDENTITY
,Node VARCHAR(20)
)
DECLARE @Node_Counter INT
,@Loop_Counter INT
INSERT INTO @Nodes_AT
SELECT Node
FROM #Nodes
SELECT @Node_Counter = 1
SELECT @Loop_Counter = (
SELECT Count(*)
FROM @Nodes_AT
)
WHILE @Loop_Counter > 0
AND @Node_Counter <= @Loop_Counter
BEGIN
SELECT @Node_Cnt = (
SELECT Node
FROM @Nodes_AT
WHERE Primary_Key = @Node_Counter
)
SELECT @Node_COS_F = sum(COS_Material + COS_Fixed_Overhead + COS_Variable_Overhead + COS_Labour)
FROM order_line_cos olc
INNER JOIN journal j ON j.source_timestamp = olc.date_time
AND j.account = olc.account
WHERE node = @Node_Cnt
AND financial_period >= CASE
WHEN @Financial_Period_From = ''
THEN financial_period
ELSE @Financial_Period_From
END
AND financial_period <= CASE
WHEN @Financial_Period_To = ''
THEN financial_period
ELSE @Financial_Period_To
END
SELECT @Available_To_Budget_F = Available_To_Budget
,@Available_To_Spend_F = Available_To_Spend
,@Pre_Commitment_Labour_F = Pre_Commitment_Labour
,@Pre_Commitment_Overhead_F = Pre_Commitment_Overhead
,@Pre_Commitment_Fixed_Overhead_F = Pre_Commitment_Fixed_Overhead
,@Commitment_Labour_F = Commitment_Labour
,@Commitment_Overhead_F = Commitment_Overhead
,@Commitment_Fixed_Overhead_F = Commitment_Fixed_Overhead
FROM dbo.fn_Node_Cost_Summary(@Node_Cnt, @Budget_Scenario, 0)
IF NOT (
@COS = 1
OR @AvailableBudget = 1
OR @AvailableSpend = 1
)
BEGIN
SELECT @Available_To_Budget_F = 0
,@Available_To_Spend_F = 0
,@Node_COS_F = 0
END
IF NOT (
(
@PreComm = 1
OR @Comm = 1
)
AND @Labour = 1
)
BEGIN
SELECT @Pre_Commitment_Labour_F = 0
,@Pre_Commitment_Overhead_F = 0
,@Pre_Commitment_Fixed_Overhead_F = 0
,@Commitment_Labour_F = 0
,@Commitment_Overhead_F = 0
,@Commitment_Fixed_Overhead_F = 0
END
INSERT INTO #TMP1
SELECT n.Node
,n.Budget_Checking
,'Sales' = 0
,'Other Sales' = 0
,'COS' = isnull(@Node_COS_F, 0)
,'Scenaro' = 0
,'[Pre-Comm]' = isnull(@Pre_Commitment_Labour_F + @Pre_Commitment_Overhead_F + @Pre_Commitment_Fixed_Overhead_F, 0)
,'Comm' = isnull(@Commitment_Labour_F + @Commitment_Overhead_F + @Commitment_Fixed_Overhead_F, 0)
,'ACWP' = 0
,'AvailableOverspend' = 0
,'AvailableBudget' = isnull(@Available_To_Budget_F, 0)
,'AvailableSpend' = isnull(@Available_To_Spend_F, 0)
,'ProjectACWP' = 0
,'ProjectCOS' = 0
FROM Node n
WHERE n.Node = @Node_Cnt
GROUP BY n.Node
,n.Budget_Checking
SELECT @Node_Counter = @Node_Counter + 1
END
/* if @HugeList = 0
Insert into #TMP1
Exec (
'Select
v.Node,
n.budget_Checking,
"Sales" = 0 ,
"Other Sales" = 0 ,
"COS" = isnull(sum(COS_To_Date),0),
"Scenaro" = 0,
"Pre-Comm" = 0 ,
"Comm" = 0 ,
"ACWP" = 0,
"AvailableOverspend" = 0 ,
"AvailableBudget" = isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Cost_Of_Node),0),
"AvailableSpend" = case when n.budget_Checking = "Y" then isnull(sum(Actual),0) + isnull(sum(Commitment),0) else isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Actual),0) + isnull(sum(Commitment),0) end,
"ProjectACWP" = 0,
"ProjectCOS" = 0
from Node_Cost_Summary_V v join node n (nolock) on n.node = v.node
Where v.node in ' + @Node_List + '
GROUP BY v.Node, n.Budget_Checking')
Else
Insert into #TMP1
Select
v.Node,
n.Budget_Checking,
'Sales' = 0 ,
'Other Sales' = 0 ,
'COS' = isnull(sum(COS_To_Date),0),
'Scenaro' = 0,
'Pre-Comm' = 0 ,
'Comm' = 0 ,
'ACWP' = 0,
'AvailableOverspend' = 0 ,
'AvailableBudget' = isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Budget),0),
'AvailableSpend' = case when n.Budget_Checking = 'Y' then isnull(sum(Actual),0) + isnull(sum(Commitment),0) else isnull(sum(Comp_Cost_Limit),0) + isnull(sum(Actual),0) + isnull(sum(Commitment),0) end,
'ProjectACWP' = 0,
'ProjectCOS' = 0
from Node_Cost_Summary_V v join node n (nolock) on n.node = v.node
Where v.node in (Select Node from #Nodes)
GROUP BY v.Node, n.Budget_Checking*/
END
IF @ProjectCOS = 1
BEGIN
IF @HugeList = 0
INSERT INTO #TMP1
EXEC (
'Select
v.Node,
n.budget_Checking,
Sales = 0 ,
[Other Sales] = 0 ,
COS = 0,
Scenaro = 0,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = isnull(sum(COS_To_Date),0)
from Node_Cost_Summary_V v inner merge join node n (nolock) on n.node = v.node
Where v.node in ' + @Node_List + '
GROUP BY v.Node, n.Budget_Checking'
)
ELSE
INSERT INTO #TMP1
SELECT v.Node
,n.Budget_Checking
,'Sales' = 0
,'Other Sales' = 0
,'COS' = 0
,'Scenaro' = 0
,'[Pre-Comm]' = 0
,'Comm' = 0
,'ACWP' = 0
,'AvailableOverspend' = 0
,'AvailableBudget' = 0
,'AvailableSpend' = 0
,'ProjectACWP' = 0
,'ProjectCOS' = isnull(sum(COS_To_Date), 0)
--CHANGED FOR TF1 BY ARNO from Node_Project_WIP_Cost_Summary_V v inner merge join node n (nolock) on n.node = v.node
FROM Node_Cost_Summary_V v
INNER MERGE JOIN node n(NOLOCK) ON n.node = v.node
WHERE v.node IN (
SELECT Node
FROM #Nodes
)
GROUP BY v.Node
,n.Budget_Checking
END
--Exclude Disposed Nodes if option selected
IF @Exclude_Disposed = 1
SELECT @IncludeStatus = '(''A'',''C'',''F'',''I'',''P'',''R'',''S'')'
ELSE
SELECT @IncludeStatus = '(''A'',''C'',''D'',''F'',''I'',''P'',''R'',''S'')'
SELECT @Min_Date = cast(@MaxFromWorkDate AS VARCHAR)
,@Max_Date = cast(@MaxToWorkDate AS VARCHAR)
,@Incl_CC = cast(@Cost_Centre_Include AS VARCHAR)
,@Incl_Res = cast(@Resource_Include AS VARCHAR)
SELECT @ExecString = ''
,@ExecString2 = ''
,@ExecString3 = ''
IF @Material = 1
SELECT @ExecString = ' Select m.Node, n.budget_checking,
Budgeted = isnull(sum(Budgeted_Value), 0),
Approved = isnull(sum(Approved_Value), 0)
from Material_Budget m (nolock) join node n (nolock) on n.node = m.node
where m.Financial_Period in
(Select distinct Financial_Period
from Company_Calendar
where Work_Date between ''' + @Min_Date + ''' and
''' + @Max_Date + ''') and
((' + @Incl_Res + ' = 1 and
Resource in ' + @Resource_List + ') OR
(' + @Incl_Res + ' = 0 and
Resource not in ' + @Resource_List + '))
group by m.node, n.budget_checking '
IF @Material = 1
AND @Labour = 1
SELECT @ExecString2 = ' UNION ALL' -- LLJ 2010-10-13
SELECT 'Begin test'
IF @Labour = 1
SELECT @ExecString3 = ' select l.Node, n.budget_Checking,
Budgeted = isnull(sum((Budgeted_Labour_Rate + Budgeted_Overhead_Rate +
Budgeted_Fixed_Overhead_Rate) * Budgeted_Hours), 0),
Approved = isnull(sum((Approved_Labour_Rate + Approved_Overhead_Rate +
Approved_Fixed_Overhead_Rate) * Approved_Hours), 0)
from Labour_Budget l (nolock) join node n (nolock) on n.node = l.node
where l.Financial_Period in
(Select distinct Financial_Period
from Company_Calendar
where Work_Date between ''' + @Min_Date + ''' and
''' + @Max_Date + ''') and
((' + @Incl_CC + ' = 1 and
Cost_Centre in ' + @Cost_Centre_List + ') OR
(' + @Incl_CC + ' = 0 and
Cost_Centre not in ' + @Cost_Centre_List + '))
group by l.node, n.Budget_Checking '
IF @Node <> ''
BEGIN
SELECT 'Node Portion'
EXEC (
'
Select distinct [Level/Node] =
convert(char(3), o.Level_Number)
+ '' ''
+ rtrim(o.Node)
+ '' ''
+ convert(Char(30),
n.Description),
o.Level_Number,
o.Node,
n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
o.Sequence,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
(' + @ExecString + @ExecString2 + @ExecString3 +
') as t1
group by t1.Node) as t2
right outer join Node_Structure_Output o on o.node = t2.node
join Node n (nolock) on n.Node = o.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where o.User_Id = ''' + @UserHostName + '''
and n.status in ' + @IncludeStatus + '
group by o.Level_Number, o.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit, o.Sequence
Order by o.Sequence '
)
END
ELSE IF @Division <> ''
BEGIN
SELECT 'Division Portion'
EXEC (
'
Select [Level/Node] = convert(char(3), 0) + '' '' +
''DIVISION '' + rtrim(Division_Code) + '' '' + convert(Char(30),Division_Description), 0,
''DIVISION '' + Division_Code, Division_Description,
[Budgeted Amount] = 0,
[Approved Amount] = 0,
[Cost Limit] = 0 ,
[Sales] = 0 ,
[Other Sales] = 0 ,
COS = 0 ,
Scenario = 0 ,
[Pre-Comm] = 0 ,
Comm = 0 ,
ACWP = 0,
AvailableOverspend = 0 ,
AvailableBudget = 0 ,
AvailableSpend = 0,
ProjectACWP = 0,
ProjectCOS = 0
From Division (nolock)
Where Division_Code = ''' + @Division +
'''
UNION
Select distinct [Level/Node] = convert(char(3), 1) + '' '' +
rtrim(n.Node) + '' '' + convert(Char(30),n.Description), 1,
n.Node, n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
(' + @ExecString + @ExecString2 + @ExecString3 +
') as t1
group by t1.Node) as t2
right outer join Node n (nolock) on n.Node = t2.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where n.Division_Code = ''' + @Division + '''
and n.status in ' + @IncludeStatus + '
group by n.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit
Order by 1 '
)
END
ELSE IF @Node_Group <> ''
BEGIN
SELECT 'NodeGroup Portion'
EXEC (
'Select distinct [Level/Node] = ''0 ' + @Node_Group + ''',
0, ''' + @Node_Group +
''', ''NODE GROUP'',
[Budgeted Amount] = 0,
[Approved Amount] = 0,
[Cost Limit] = 0 ,
Sales = 0,
[Other Sales] = 0,
COS = 0,
Scenario = 0,
[Pre-Comm] = 0,
Comm = 0,
ACWP = 0,
AvailableOverspend = 0,
AvailableBudget = 0,
AvailableSpend = 0,
0,
ProjectACWP = 0,
ProjectCOS = 0
UNION
Select distinct [Level/Node] = ''1 '' +
rtrim(ngl.Node) + '' '' + convert(Char(30), n.Description),
1, ngl.Node, n.Description,
[Budgeted Amount] = isnull(t2.Budgeted, 0),
[Approved Amount] = isnull(t2.Approved, 0),
[Cost Limit] = n.Cost_Limit ,
Sales = isnull(sum(v1.Sales), 0) ,
[Other Sales] = isnull(sum(v1.Other_Sales), 0) ,
COS = isnull(sum(v1.COS_Total), 0) ,
Scenario = isnull(sum(v1.Scenario), 0) ,
[Pre-Comm] = isnull(sum(v1.Pre_Comm), 0) ,
Comm = isnull(sum(v1.Comm), 0) ,
ACWP = isnull(sum(v1.ACWP), 0),
AvailableOverspend = isnull(sum(v1.AvailableOverspend),0) ,
AvailableBudget = isnull(sum(v1.AvailableBudget),0) ,
AvailableSpend = isnull(sum(v1.AvailableSpend),0) ,
0,
ProjectACWP = isnull(sum(v1.ProjectACWP), 0),
ProjectCOS = isnull(sum(v1.ProjectCOS), 0)
from
(Select Node,
Budgeted = isnull(sum(Budgeted), 0),
Approved = isnull(sum(Approved), 0)
from
('
+ @ExecString + @ExecString2 + @ExecString3 + ') as t1
group by t1.Node) as t2
right outer join Node_Group_Link ngl on ngl.node = t2.node
join Node n (nolock) on n.Node = ngl.Node
left outer Join #TMP1 v1 on n.Node = v1.Node
where ngl.Node_Group = ''' + @Node_Group + '''
and n.status in ' + @IncludeStatus + '
group by ngl.Node, n.budget_checking, n.Description, t2.Budgeted, t2.Approved, n.Cost_Limit'
)
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply