March 19, 2008 at 11:55 am
I have seemed to reach and impass:
i have the following recordset:
ConsultantID Total PeriodEndDate
0000926 0.002007-11-30 00:00:00.000
0000926 0.002007-12-31 00:00:00.000
0000926 0.002008-01-31 00:00:00.000
0000926 0.002008-02-29 00:00:00.000
0002185 0.002007-11-30 00:00:00.000
0002185 0.002007-12-31 00:00:00.000
0002185 0.002008-01-31 00:00:00.000
0002185 0.002008-02-29 00:00:00.000
0002280 880.382007-11-30 00:00:00.000
0002280 691.042007-12-31 00:00:00.000
0002280 0.002008-01-31 00:00:00.000
0002280 156.812008-02-29 00:00:00.000
0002354 1005.702007-11-30 00:00:00.000
0002354 1683.332007-12-31 00:00:00.000
0002354 411.542008-01-31 00:00:00.000
0002354 402.472008-02-29 00:00:00.000
0002617 15215.252007-11-30 00:00:00.000
0002617 6000.392007-12-31 00:00:00.000
0002617 0.002008-01-31 00:00:00.000
0002617 0.002008-02-29 00:00:00.000
0003022 952.782007-11-30 00:00:00.000
0003022 601.572007-12-31 00:00:00.000
0003022 220.752008-01-31 00:00:00.000
0003022 263.122008-02-29 00:00:00.000
0003682 0.002007-11-30 00:00:00.000
0003682 0.002007-12-31 00:00:00.000
0003682 0.002008-01-31 00:00:00.000
0003682 0.002008-02-29 00:00:00.000
0004400 1127.582007-11-30 00:00:00.000
0004400 78.892007-12-31 00:00:00.000
0004400 922.792008-01-31 00:00:00.000
0004400 1394.662008-02-29 00:00:00.000
0004934 2160.152007-11-30 00:00:00.000
0004934 1760.142007-12-31 00:00:00.000
0004934 213.742008-01-31 00:00:00.000
0004934 553.782008-02-29 00:00:00.000
0005261 2264.122007-11-30 00:00:00.000
0005261 3692.462007-12-31 00:00:00.000
0005261 4260.112008-01-31 00:00:00.000
0005261 5103.642008-02-29 00:00:00.000
0005635 524.342007-11-30 00:00:00.000
0005635 0.002007-12-31 00:00:00.000
0005635 0.002008-01-31 00:00:00.000
0005635 407.502008-02-29 00:00:00.000
0005692 4951.712007-11-30 00:00:00.000
0005692 6911.262007-12-31 00:00:00.000
0005692 1739.752008-01-31 00:00:00.000
0005692 5720.892008-02-29 00:00:00.000
0006027 0.002007-11-30 00:00:00.000
0006027 0.002007-12-31 00:00:00.000
0006027 0.002008-01-31 00:00:00.000
0006027 0.002008-02-29 00:00:00.000
0007059 0.002007-11-30 00:00:00.000
0007059 0.002007-12-31 00:00:00.000
0007059 0.002008-01-31 00:00:00.000
0007059 0.002008-02-29 00:00:00.000
0007221 0.002007-11-30 00:00:00.000
0007221 0.002007-12-31 00:00:00.000
0007221 0.002008-01-31 00:00:00.000
0007221 0.002008-02-29 00:00:00.000
0007255 0.002007-11-30 00:00:00.000
0007255 0.002007-12-31 00:00:00.000
0007255 0.002008-01-31 00:00:00.000
0007255 0.002008-02-29 00:00:00.000
What I am trying to achieve is one row for each distinct ConsultantID with a single total amount.
I tried using sum but that just gives me multiple lines as the above output indicates: The code is as follows:
DECLARE @consultantID VARCHAR(20)
DECLARE @StartDt DateTime
DECLARE @EndDt DateTime
SET @ConsultantID = '0000344'
SET @StartDt = '11/01/2007'
SET @EndDt = '10/31/2008'
Selectd.ConsultantID
,SUM(v.SaleAmountLevelOne)As Total
,v.PeriodEndDate
FROM #DLLevel d
Left Outer Join Volume v ON d.ConsultantID = v.ConsultantID
WHERE DownlineLevel = 1
AND v.PeriodEndDate >= @StartDt
AND v.PeriodEndDate <= @EndDt
GROup BY
d.ConsultantID
d.AchievedTitle
,v.PeriodEndDate
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 19, 2008 at 12:02 pm
Because you have PeriodEndDate in the 'group by', it is using the date as part of the grouping, which is why multiple rows for same consultant (note that dates are different for each).
If it was easy, everybody would be doing it!;)
March 19, 2008 at 12:08 pm
I tried to remove the periodenddate from the GROUP By but I get this error:
Msg 8120, Level 16, State 1, Line 163
Column 'Volume.PeriodEndDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 19, 2008 at 12:11 pm
Right, because you are using SUM in your select. You must remove the PeriodEndDate from the SELECT as well.
Why do you want the PeriodEndDate if you are summing all amounts for each consultant? Seems to me, the PeriodEndDate no longer means anything if you are aggregating the amount.
If it was easy, everybody would be doing it!;)
March 19, 2008 at 12:18 pm
You were right. I took out the PeriodEndDate and it summed correctly.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 19, 2008 at 12:36 pm
If you needed the last (max) PeriodEndDate for each consultant, you can add it in like below.
This was suggested in your error...column must be in a 'group by' or an 'aggregation'...below, PeriodEndDate is in an aggregation (max).
Selectd.ConsultantID
,SUM(v.SaleAmountLevelOne)As Total
,MAX(v.PeriodEndDate)
FROM #DLLevel d
Left Outer Join Volume v ON d.ConsultantID = v.ConsultantID
WHERE DownlineLevel = 1
AND v.PeriodEndDate >= @StartDt
AND v.PeriodEndDate <= @EndDt
GROup BY
d.ConsultantID
d.AchievedTitle
This was more than you asked for, but this was a good opportunity to point this out just in case you need it in the future.
Hope this helped! 😀
If it was easy, everybody would be doing it!;)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply