March 25, 2008 at 10:37 am
I am running into a issue when trying to get a correct total using SUM.
Code:
Declare @ConsultantID char(20)
Declare @StartDate dateTime
Declare @EndDate dateTime
Set @consultantID = '0007621'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
SELECT v.ConsultantID
,PurchaseAmount
,AchieveLevel
,c.NACDate
,MAX(v.PeriodEndDate)
FROM Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID AND v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,AchieveLevel
,c.NACDate
,PurchaseAmount
OUTPUT:
ConsultantID PurchaseAmount AchieveLevel NACDate
-------------------- --------------------------------------- ------------ ----------------------- -----------------------
0007621 6087.63 70 2002-05-01 00:00:00.000 2008-01-31 00:00:00.000
0007621 6228.40 70 2002-05-01 00:00:00.000 2008-02-29 00:00:00.000
0007621 10790.69 70 2002-05-01 00:00:00.000 2007-12-31 00:00:00.000
0007621 19621.29 70 2002-05-01 00:00:00.000 2007-11-30 00:00:00.000
This seems to be working correctly but when I try to get a SUM of the PurchaseAmount I seem to be tripling the amount. which I understand because if you take off the DISTINCT clause i end up with 3 records per Consultant.
Code to SUM:
SELECT v.ConsultantID
,SUM(PurchaseAmount)
,AchieveLevel
,c.NACDate
,MAx(v.PeriodEndDate)
FROM Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID AND v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,AchieveLevel
,c.NACDate
ConsultantID AchieveLevel NACDate
-------------------- --------------------------------------- ------------ ----------------------- -----------------------
0007621 128184.03 70 2002-05-01 00:00:00.000 2008-02-29 00:00:00.000
So I guess my question is how do I either stop the duplicating of rows in the top query without a DISTINCT clause or how do I fix the bottom query to SUM appropriately. I can divide the SUM by 3 and get the correct amount but as records get added to that table, well you can see the issue.
SELECT v.ConsultantID
,SUM(PurchaseAmount)/3
,AchieveLevel
,c.NACDate
,MAx(v.PeriodEndDate)
FROM Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID AND v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,AchieveLevel
,c.NACDate
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 25, 2008 at 10:50 am
Make a derived table with the sum for the consultant. You can then join that to your main query on the appropriate id. This way the amount is fixed.
http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables
March 25, 2008 at 11:11 am
There is my duh for the day. That worked great. How would I go about taking the total and then another field assign points to it.
So lets say I have a total of 42,567 in my total amount field. So the business rule states if the total amount is over 15000 assign 1 point so in this case I would end up with 2 pts (30,000).
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 25, 2008 at 11:24 am
SELECT FLOOR(MySumColumn / 15000) * 15000
If you want to display 2 instead of 15000, take off the multiplication at the end.
March 25, 2008 at 11:25 am
If you want to display 2 instead of 15000, take off the multiplication at the end.
This was supposed to read if you want to display 2, instead of 30000.
March 25, 2008 at 12:18 pm
Thanks for the tip. This is what it turned out to be.
SELECT v.ConsultantID
,SUM(PurchaseAmount) AS PORSalesTotal
,'PORSalesPoints' =
CASE
WHEN SUM(PurchaseAmount) > 15000 And NACDATE <= '06/30/2008' then SUM(PurchaseAmount / 15000)
WHEN SUM(PurchaseAmount) > 15000 And NACDATE > '06/30/2008' then SUM(PurchaseAmount / 10000)
END
,v.AchieveLevel
,v.NACDate
--,MAx(v.PeriodEndDate)
FROM #TotalPORSales v
WHERE v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,AchieveLevel
,v.NACDate
--,PurchaseAmount
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 25, 2008 at 12:23 pm
You will have problems when your purchase amount is not a whole number, unless you want decimal values.
select 19999.99 / 15000
If you want to remedy this you can use floor.
select FLOOR(19999.99 / 15000)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply