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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy