April 2, 2008 at 8:54 am
I have the following specifications:
1.Did you achieve Level 20 during the Promotional Period?
2.If ‘yes,’ then are you “Active?” (If ‘no,’ then keep looking for a Consultant who did.)
3.If “Active,” then count a Point in the month of Level 20.
4.If “Inactive,” then did you have $1,000 or more in Lifetime POR sales?
5.If Lifetime POR Sales is $1,000 or more, then count a Point in the month of Level 20.
6.If Lifetime POR Sales is less than $1,000, then do not count a Point for that Consultant.
The logic that is giving me fits are these:
3.If “Active,” then count a Point in the month of Level 20.
4.If “Inactive,” then did you have $1,000 or more in Lifetime POR sales?
5.If Lifetime POR Sales is $1,000 or more, then count a Point in the month of Level 20.
6.If Lifetime POR Sales is less than $1,000, then do not count a Point for that Consultant.
To achieve what I need I think is a OR statement to handle 3 and 4 but I can seem to get it to work. Code:
Declare @ConsultantID char(20)
DECLARE @MonthsOut int, @StartDate datetime, @EndDate datetime
Set @ConsultantID = '0000006'
Set @MonthsOut = 12
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008';--dateadd(mm,@MonthsOut, @StartDate)
IF @EndDate > GETDATE()
BEGIN
SET @EndDate = GETDATE()
END;
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID,ConsultantDate)
AS
(
-- Anchor member defintion
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX))
,'' As SponsorName
,0 as DownLineLevel
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
,A.ConsultantDate
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @EndDate Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive') -- 1 Record
UNION ALL
--Recursive member definition
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID
,'' AS SponsorName
,DownLineLevel + 1
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
,A.ConsultantDate
FROM dbo.uvwConsultantDownLine AS A with (nolock)
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
WHERE @EndDate Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive')
) -- Appropriate records
-- Create the Temp table #Downline that returns the CTE results
SELECT A.DownLineLevel
,A.ConsultantID
,A.EffectiveDate
,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName
--,C.EmailAddress
,D.Title AS AchievedTitle
, CONVERT(NVARCHAR(MAX),A.SponsorID) AS SponsorID
,A.SponsorName
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
,A.ConsultantDate
INTO #Downline
FROM DownLine AS A with (noLock)
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON A.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 4
GROUP BY A.ConsultantID
,A.EffectiveDate
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
--,C.BumpUpDate
--,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
,A.ConsultantDate
Select 'OrigConsID' = @ConsultantID
,(Select ConsultantName FROM #Downline d
WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'
,D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,'StatusID' = CASE
--WHEN StatusID ='Active' THEN ''
WHEN StatusID = 'Home Office' THEN ''
WHEN StatusID = 'New, Non Active Consultant' THEN ''
ELSE StatusID
END
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,D.CurrentLevelXID
,d.DeactivationDate
,SUM(v.PurchaseAmount) AS PurchaseAMount
,MAX(v.PeriodEndDate)as PEriodENdDate
,MAX(v.AChieveLevel) AS AchieveLEvel
,d.ConsultantDate
INTO #DLLevel from #Downline D with (nolock)
LEFT OUTER JOIN Volume v ON D.ConsultantID = v.ConsultantID
WHERE d.Active=1 AND d.DownlineLevel =1 AND v.AchieveLevel=20
OR (D.DeactivationDate BETWEEN @StartDate AND @EndDate AND d.Active=0 )
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelAchieveDate
,D.BumpupDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,D.StatusID
,D.CurrentLevelXID
,d.DeactivationDate
,d.ConsultantDate
--HAVING Sum(o.PurchaseAmount)>=1000
ORDER BY 3
Select * INTO #RecruitCount FROM #DLLevel
WHERE ConsultantDate >= @StartDate
AND COnsultantDate <= @EndDate
OR (deactivationDate >= @StartDate
AND DeactivationDate <=@EndDate )
--SELECT * from #RecruitCount
CREATE TABLE #Temp1 (
id_num int IDENTITY(1,1),
COnsultantID char(20),
ConsultantNAme char(50),
SponsorID char(20),
RecruitCOunt INT,
AchieveDate DateTime,
PurchaseAMount DEC(19,2),
Active int
)
INSERT iNTO #Temp1
Select r.ConsultantID, r.ConsultantName, r.SponsorID, COUNT(*) AS RecruitCOunt,r.AchieveDate,r.PurchaseAmount, r.Active
FRom #RecruitCount r
WHERE active = 1 or (r.Active=0 AND r.PurchaseAmount >=1000) -- I think this is where I need to calculate the lifetime sales which would be all records for this consultant in the volume table.GROUp BY r.ConsultantID, r.SponsorID,r.achieveDate,r.ConsultantName, r.PurchaseAmount
,r.Active
Select * from #Temp1
--DROP TABLE #ActivitySummary
DROP TABLE #Downline
DROP TABLE #DLLevel
DROP TABLE #RecruitCount
DROP TABLE #Temp1
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 2, 2008 at 10:23 am
I would try to identify the consultants and their points for one month. Then create a calendar table to cross reference salestype dates to identify numbers to months.
1. Did you achieve Level 20 during the Promotional Period?
2. If ‘yes,’ then are you “Active?” (If ‘no,’ then keep looking for a Consultant who did.)
3. If “Active,” then count a Point in the month of Level 20.
4. If “Inactive,” then did you have $1,000 or more in Lifetime POR sales?
5. If Lifetime POR Sales is $1,000 or more, then count a Point in the month of Level 20.
6. If Lifetime POR Sales is less than $1,000, then do not count a Point for that Consultant.
CREATE #tConsultantPoints ( id int identity, consultantID, ?month?, points)
Insert into temp table all possible consultants (points = 0
--Update temp table by criteria above (1 & 2)
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
where ot.month = @targetMonth AND Level > 20 and active = 1
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
where ot.month = @targetMonth AND lifetimePORSales > 1000 AND active = 0
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
INNER JOIN calendarTable ON ot.@targetMonth = calendarTable.month
where Level > 20 and active = 1
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
INNER JOIN calendarTable ON ot.@targetMonth = calendarTable.month
where lifetimePORSales > 1000 AND active = 0
Good luck
daralick
April 2, 2008 at 10:33 am
I haven't gone through Daryl's logic, but i'd be thinking of something similar. With complex logic like this, I'd probably break this into multiple steps.
Is this run constnatly or is it some type of batch process?
April 2, 2008 at 10:38 am
It is run when ever a consultant wished to see the list. So it's will be pretty sporatic.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 2, 2008 at 2:15 pm
I have a question on the following code:
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
where ot.month = @targetMonth AND lifetimePORSales > 1000 AND active = 0
lifetimePORSales is actually suppose to be a SUM > 1000
So I am thinking something like:
Update #Temp6
SET points = points + 1
FROM #Temp6 t
LEFT OUTER JOIN volume v ON t.ConsultantID = v.ConsultantID
where t.DeactivationDate Between @StartDate and @EndDate
and t.active = 0 AND (Select SUM(v.PurchaseAmount)...
But how would I write something like that?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 3, 2008 at 7:21 am
Any ideas on this?
I have a question on the following code:
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN OtherTable ot ON ....
where ot.month = @targetMonth AND lifetimePORSales > 1000 AND active = 0
lifetimePORSales is actually suppose to be a SUM > 1000
So I am thinking something like:
Update #Temp6
SET points = points + 1
FROM #Temp6 t
LEFT OUTER JOIN volume v ON t.ConsultantID = v.ConsultantID
where t.DeactivationDate Between @StartDate and @EndDate
and t.active = 0 AND (Select SUM(v.PurchaseAmount)...
But how would I write something like that?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 3, 2008 at 9:02 am
A- meaningful table names will almost always save effort at the time or later in maintenance.
B- do not worry about a left join. You are only updating the consultants that fit your criteria
C- use a derived table for getting your target sum
Update #tConsultantPoints SET points = points + 1
FROM #tConsultantPoints tCon
INNER JOIN
(SELECT consultantID, cal.month, sum(v.yourDollars) sumDollars
FROM volume v
INNER JOIN calendar cal
where v.date between cal.firstDayofMonth and cal.lastDayOfMonth
GROUP BY consultantID, cal.month
HAVING sum(v.yourDollars) > 1000
) pointMonth
ON tCon.consultantID = pointMonth.consultantID
That should allow you to get a list of months that the consultant made the numbers (derived table) that allows updating consultant temp table.
good luck
daryl
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply