March 28, 2008 at 9:13 am
I am not sure how to write this:
DECLARE @ConsultantID char(20)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
Set @ConsultantID = '0000558'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
Selectd.ConsultantID
,d.Downlinelevel
,d.ConsultantName
,D.SponsorID
,d.AchievedTitle
,d.AchieveDate
,v.AchieveLevel
,SUM(v.PurchaseAmount) as PurchaseAmount
,MAX(v.PeriodEndDate) AS PeriodEndDate
,d.Active
,d.StatusID
INTO #RecruitCount FROM #DLLevel d
Left Outer Join Volume v ON d.ConsultantID = v.ConsultantID
WHERE DownlineLevel = 1
AND v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
AND d.AchieveDate >= @StartDate
AND d.AchieveDate <= @EndDate
AND v.AchieveLevel = 20
GROup BY
d.ConsultantID
,v.Consultantid
,d.Downlinelevel
,d.ConsultantName
,d.AchievedTitle
,v.AchieveLevel
--,v.PeriodEndDate
,d.Active
,d.StatusID
,d.AchieveDate
,d.SponsorID
--,v.Purchaseamo
I will try to make this as sdtraight forward as possible. This code represents Consultants that have purchaseamounts in the period declared in the @StartDate and @EndDate variable. These dates will never change. The crux of the matter is if the Consultant is in and Inactive status (Active=0) then I need to take in account all purchase amount in the consultants lifetime history and not just the Reporting Period. This information is contained in the volume table (PeriodEndDate).
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 28, 2008 at 9:19 am
Many options here.
I would probably first off
select ...
where Active = 1
and that active logic
UNION ALL
select ...
where Active = 0
and inactive logic
ORDER BY ...
March 28, 2008 at 9:52 am
Thats what i'd say too, keep it simple and do a union with the two sets of logic.
March 28, 2008 at 10:25 am
I am trying the UnionStatements:
Select ConsultantID
, ConsultantName
, SponsorID
,RecruitCOunt
,AchieveDate
,PurchaseAmount
,Active from #temp1
WHERE Active = 1
UNION ALL
Select ConsultantID
, ConsultantName
, SponsorID
,RecruitCOunt
,AchieveDate
,PurchaseAmount
,Active from #temp1
UNION ALL
Select t.ConsultantID
, t.ConsultantName
, t.SponsorID
,t.RecruitCOunt
,t.AchieveDate
,SUM(v.PurchaseAmount) AS PurchaseAmount
,t.Active
from #temp1 t
LEFT OUTER JOIN Volume v ON t.ConsultantID = v.ConsultantID
WHERE t.Active = 0 AND ....
GROUP BY
t.ConsultantID
, t.ConsultantName
, t.SponsorID
,t.RecruitCOunt
,t.AchieveDate
,t.Active
But the last one is the one I need help on. I needs to be something like WHere t.Active = 0 and the SUm(v.PurchaseAmount) >=1000
But I can't have a aggregate in a Where clause without a HAVING or something like that.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 28, 2008 at 10:53 am
the having statement will go after your group by
WHERE t.Active = 0 AND ....
GROUP BY
t.ConsultantID
, t.ConsultantName
, t.SponsorID
,t.RecruitCOunt
,t.AchieveDate
,t.Active
HAVING SUM(v.PurchaseAmount) > 1000
March 28, 2008 at 11:10 am
That worked. Thanks a lot. Final code
Select ConsultantID
, ConsultantName
, SponsorID
,RecruitCOunt
,AchieveDate
,PurchaseAmount
,Active from #temp1
WHERE Active = 1
UNION ALL
Select t.ConsultantID
, t.ConsultantName
, t.SponsorID
,t.RecruitCOunt
,t.AchieveDate
,SUM(v.PurchaseAmount) AS PurchaseAmount
,t.Active
from #temp1 t
INNER JOIN Volume v ON t.ConsultantID = v.ConsultantID
WHERE t.Active = 0
GROUP BY
t.ConsultantID
, t.ConsultantName
, t.SponsorID
,t.RecruitCOunt
,t.AchieveDate
,t.Active
HAVING SUm(v.PurchaseAmount) >=1000
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply