SUM Keeps decreasing when it should stay the same:

Question

Post reply

SUM Keeps decreasing when it should stay the same:

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
April 10, 2008 at 12:51 pm

#205684

I have this result set:
ConsultantID ConsultantName AchieveTitle AchieveLevel TeamSalesPoint TeamSalesLevelAmount TeamSalesAmount TeamSalesLevel
-------------------- ------------------------------ ------------------------------ ------------ --------------------------------------- -------------------- --------------------------------------- ---------------------
0000131 BARTSCH, LISA Senior Team Manager 60 0.338393 110000 37223.28 1st, 2nd and 3rd Line
0000131 BARTSCH, LISA Team Mentor 70 0.141557 250000 35389.36 1st, 2nd and 3rd Line
0000131 BARTSCH, LISA Sr Team Mentor 80 0.036484 500000 18242.03 1st, 2nd and 3rd Line
As you can see my TeamSalesAmount Keeps decreasing when reall it should be the same for all records as the first record (37223) I can't seem to account for the decrease. This is the code as it set now:
Declare @ConsultantID char(20)
Declare @StartDate dateTime
Declare @EndDate dateTime
Set @consultantID = '0000131'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
Select ConsultantID
,ConsultantName
,AchieveTitle
,AchieveLevel
,'TeamSalesPoint' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)/10000
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)/30000
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/110000
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/250000
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/500000
ELSE 0
END
,'TeamSalesLevelAmount' =
Case
When AchieveLevel = 40 Then '10000'
When AchieveLevel = 50 Then '30000'
When AchieveLevel = 60 Then '110000'
When AchieveLevel = 70 Then '250000'
When AchieveLevel = 80 Then '500000'
ELSE '10000'
END
,'TeamSalesAmount' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
ELSE 0
END
,'TeamSalesLevel' =
Case
When AchieveLevel = 40 Then '1st Line'
When AchieveLevel = 50 Then '1st and 2nd Line'
When AchieveLevel = 60 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 70 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 80 Then '1st, 2nd and 3rd Line'
ELSE '1st Line'
END
INTO #TeamSalesPoint FROM Volume WHERE PeriodEndDate >= @StartDate AND PeriodEndDAte <= @EndDate
AND ConsultantID = @ConsultantID
GROUP BY COnsultantID
, AchieveLevel
,AChieveTitle
,ConsultantName
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 9 posts - 1 through 8 (of 8 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Your formula is conditional, based on column AchieveLevel:

,'TeamSalesAmount' =

Case

When AchieveLevel = 40 Then SUM(saleAmountLevelOne)

When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)

The 3 records you have shown all have a different AchieveLevel value, so why are you expecting the Sum() result to be the same when you've explicitly code it to be different ?

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

For this particular COnsultant the amount should be the same because the AMount never changes regardless of the Achievelevel in which they are 60,70, and 80.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

You've got AchieveLevel in the Group By. That in turn impacts which records contribute to the SUM() at each AchieveLevel.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Could you post some sample data on which to test?

I agree with PW that it looks like having the AchieveLevel in the Group By may contribute to the results, but without seeing the source data it is hard to tell.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 5

What would be the best way to get you some sample data?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Just something like:

Create Table test

(

test_id int,

test_desc char(6)

)

Insert Into test

Select

1,

'Test 1'

Union

Select

2,

'Test 2'

Union

Select

3,

'Test 3'

Union

Select

4,

'Test 4'

Or

Name Level Amount

---------------------

Jack Level1 10000

Jack Level2 25999

Jack Level3 13000

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

alorenzini (4/10/2008)
What would be the best way to get you some sample data?

See the UFL in my signature line for some help on that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 8

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#TeamSales','U') IS NOT NULL

DROP TABLE #TeamSales

--===== Create the test table with

CREATE TABLE #TeamSales

(

COnsultantID CHAR(20),

SaleAmountLEvelOne Decimal (9,2),

SaleAmountLEvelTwo Decimal (9,2),

SaleAmountLEvelThree Decimal (9,2),

AchieveLevel INT,

PeriodEndDate Datetime )

Declare @ConsultantID char(20)

Declare @StartDate dateTime

Declare @EndDate dateTime

Set @consultantID = '0000131'

Set @StartDate = '11/1/2007'

Set @EndDate = '10/31/2008'

-- SELECT 'SELECT '

-- + QUOTENAME(ConsultantID,'''')+','

-- + QUOTENAME(SaleAMountLevelOne,'''')+','

-- + QUOTENAME(SaleAmountLevelTwo,'''')+','

-- + QUOTENAME(SaleAmountLevelThree,'''')+','

--+ QUOTENAME(AchieveLevel,'''')+','

-- + QUOTENAME(PeriodEndDate,'''')

-- + ' UNION ALL'

--From Volume

--WHERE ConsultantID = @ConsultantID

--AND PeriodEndDate >= @StartDate AND PeriodEndDAte <= @EndDate

INSERT INTO #TeamSales (ConsultantID,SaleAMountLevelOne, SaleAmountLevelTwo

,SaleAmountLevelTHree, AchieveLevel, PeriodEndDate)

SELECT '0000131','13440.33','21396.02','2386.93','60 ','Nov 30 2007 12:00AM' UNION ALL

SELECT '0000131','9418.65','14293.53','2362.12','70 ','Dec 31 2007 12:00AM' UNION ALL

SELECT '0000131','4659.15','3883.87','772.04','70 ','Jan 31 2008 12:00AM' UNION ALL

SELECT '0000131','7695.78','8102.12','2444.13','80 ','Feb 29 2008 12:00AM'

Select * from #TeamSales

It actually worked this time. I have never had luck doing this before. 🙂

Anyway, I think the issue now that I got a little more information from my BA, is that I need to sum all the SaleAmountLevelOne which periodenddate falls into my date range then do the same for the SaleAmountLevelTwo and SaleamountLevelThree then take those three totals and add them together.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!