Getting duplicates

Question

Post reply

Getting duplicates

Best-330874

SSCrazy

Points: 2350
More actions
March 20, 2007 at 7:46 am

#172830

i need help on this.when i run this proc i am getting duplicates.

set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
create Procedure [dbo].[COSTGetCSIProjectPhase] (@MSBA_ID varchar(10), @Project_Phase_ID int)
AS
Declare
@Total_CSICost int,
@gsf
int
-- Get GSF
Select
@gsf = Gross_Square_Footage
From
COST_Project_Phase
Where
MSBA_ID = @MSBA_ID
And
Project_Phase_ID = @Project_Phase_ID
If
@gsf is Null
Set @gsf = 0
-- Get the rollups
Select
ACC.Rollup_To_Class, ACC.Rollup_To_Category, ACC.Rollup_To_Phase, Sum(CPPC.CSI_Cost) CSI_Cost
Into
#tmp_Level
From
APPN_Classification_Category ACC,
COST_Project_Phase_CSI CPPC
Where
ACC.CSI_Flag = 1
And ACC.Classification_ID = CPPC.Classification_ID
And CPPC.MSBA_ID = @MSBA_ID
And CPPC.Project_Phase_ID = @Project_Phase_ID
Group
By ACC.Rollup_To_Class, ACC.Rollup_To_Category, ACC.Rollup_To_Phase
-- Get Total CSI.
Select
@Total_CSICost = COALESCE(Sum(CSI_Cost),0)
From
#tmp_Level
-- Level 1
Select
Rollup_To_Class, Sum(CSI_Cost) CSI_Cost
Into
#tmp_level1
From
#tmp_Level
Group
By Rollup_To_Class
-- Level 2
Select
Rollup_To_Category, Sum(CSI_Cost) CSI_Cost
Into
#tmp_level2
From
#tmp_Level
Group
By Rollup_To_Category

-- 1st level data.
Select

ACC
.Classification_ID,
ACC
.Classification_Code,
ACC
.Classification_Name,
ACC
.Classification_Level,
ACC
.Allow_To_Set_Budget,
Case When ACC.Classification_Level = 3 Then ACC.Rollup_To_Category
When ACC.Classification_Level = 2 Then ACC.Rollup_To_Class
When ACC.Classification_Level = 1 Then '0'
When ACC.Classification_Level = 4 Then ACC.Rollup_To_Phase End Parent_Classification_Code,
COALESCE(CPPC.CSI_Cost, 0) CSI_Cost,
Case When @Total_CSICost = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@Total_CSICost End Distribution_Percent,
Case When @gsf = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@gsf End Cost_per_SQFT
From
APPN_Classification_Category ACC
Left
Outer Join #tmp_Level1 CPPC
On
ACC.Classification_Code = CPPC.Rollup_To_Class
Where
ACC.CSI_Flag = 1
And Classification_Level = 1
Union
-- 2nd level data.
Select

ACC
.Classification_ID,
ACC
.Classification_Code,
ACC
.Classification_Name,
ACC
.Classification_Level,
ACC
.Allow_To_Set_Budget,
Case When ACC.Classification_Level = 3 Then ACC.Rollup_To_Category
When ACC.Classification_Level = 2 Then ACC.Rollup_To_Class
When ACC.Classification_Level = 1 Then '0'
When ACC.Classification_Level = 4 Then ACC.Rollup_To_Phase End Parent_Classification_Code,
COALESCE(CPPC.CSI_Cost, 0) CSI_Cost,
Case When @Total_CSICost = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@Total_CSICost End Distribution_Percent,
Case When @gsf = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@gsf End Cost_per_SQFT
From
APPN_Classification_Category ACC
Left
Outer Join #tmp_Level2 CPPC
On
ACC.Classification_Code = CPPC.Rollup_To_Category
Where
ACC.CSI_Flag = 1
And Classification_Level = 2
Union
-- 3rd level data.
Select

ACC
.Classification_ID,
ACC
.Classification_Code,
ACC
.Classification_Name,
ACC
.Classification_Level,
ACC
.Allow_To_Set_Budget,
Case When ACC.Classification_Level = 3 Then ACC.Rollup_To_Category
When ACC.Classification_Level = 2 Then ACC.Rollup_To_Class
When ACC.Classification_Level = 1 Then '0'
When ACC.Classification_Level = 4 Then ACC.Rollup_To_Phase End Parent_Classification_Code,
COALESCE(CPPC.CSI_Cost, 0) CSI_Cost,
Case When @Total_CSICost = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@Total_CSICost End Distribution_Percent,
Case When @gsf = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@gsf End Cost_per_SQFT
From
APPN_Classification_Category ACC
Left
Outer Join #tmp_Level CPPC
On
ACC.Classification_Code = CPPC.Rollup_To_Phase
Where
ACC.CSI_Flag = 1
And Classification_Level = 3
Union
-- 4th level data.
Select

ACC
.Classification_ID,
ACC
.Classification_Code,
ACC
.Classification_Name,
ACC
.Classification_Level,
ACC
.Allow_To_Set_Budget,
Case When ACC.Classification_Level = 3 Then ACC.Rollup_To_Category
When ACC.Classification_Level = 2 Then ACC.Rollup_To_Class
When ACC.Classification_Level = 1 Then '0'
When ACC.Classification_Level = 4 Then ACC.Rollup_To_Phase End Parent_Classification_Code,
COALESCE(CPPC.CSI_Cost, 0) CSI_Cost,
Case When @Total_CSICost = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@Total_CSICost End Distribution_Percent,
Case When @gsf = 0 Then 0 Else COALESCE(CPPC.CSI_Cost, 0)/@gsf End Cost_per_SQFT
From
APPN_Classification_Category ACC
Left
Outer Join COST_Project_Phase_CSI CPPC
On
ACC.Classification_ID = CPPC.Classification_ID
And CPPC.MSBA_ID = @MSBA_ID
And CPPC.Project_Phase_ID = @Project_Phase_ID
Where
ACC.CSI_Flag = 1
And Classification_Level = 4
Union
-- Get the top level grant total data.
Select
0 Classification_ID,
'0' Classification_Code,
'Grant Total' Classification_Name,
0 Classification_Level
,
0 Allow_to_Set_Budget
,
'0' Parent_Classification_Code,
COALESCE(Sum(CSI_Cost),0) CSI_Cost,
1 Distribution_Percent
,
Case When @gsf = 0 Then 0 Else @Total_CSICost/@gsf End Cost_per_SQFT
From
#tmp_Level
Order
by 4, 2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Best-330874 SSCrazy Points: 2350 More actions · Answer 1

Best-330874

SSCrazy

Points: 2350

March 20, 2007 at 9:04 am

#695972

is there any guru available to help me

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

Can you provide the create script for the tables and some sample data? There is a lot going on there and we will need that to test. Make sure to include the data that is getting duplicated.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 3

Hi Best ,

Wht kind of duplicacy it is.... Do u have some sample data which u can provide...

Thanks & Regards..

Amit Gupta..

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

i dont have sample data but if you see the proc on all the four level where iam doing the rollup i am getting duplicate.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

without having table layouts and sample data there isn't much we can do. There does not seem to be a group by in the level 4 part of the procedure so it looks like duplicate data would have been inserted into the temp table early on in the process that is being returned in the select.

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Sorry, Best, but you need to help us first if we are to help you. No structure, no sample data = no help. You should understand that it is impossible to pinpoint source of errors without having any idea about how the data look. We can offer some wild guess, but that's not the best way to do it and probably doesn't help you much.