February 11, 2008 at 11:06 am
I forgot to mention you will need to create the table first. You need to create the table with the correct definitions.
e.g.
CREATE TABLE #temp(
ConsultantID CHAR(7),
Level CHAR(2),
Title CHAR(25),
PeriodEndDate DATETIME
)
GO
INSERT INTO #temp
EXEC MyStoredProc 'TestVar'
GO
SELECT a.ConsultantID,
a.AChieveLevel,
a.AchieveTItle,
b.Repromote,
a.ENdPeriodDate
FROM Volume a
INNER JOIN #temp b
on a.ConsultantID = b.ConsultantID
GO
February 11, 2008 at 11:14 am
I found that out. I think it working now. I have a couple of dev testing it right now.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 11:52 am
This works fine for a single consultantID but what if I need to loop through multiple consultantIDs, how would that work?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 12:16 pm
Alorenzini,
What you can do is execute the stored procedure multiple times and store the results in the temp table each time via a while loop. My preferrend method and what I would do is this. I would use the code below (my script) because Jonnie's code breaks on multiple consultants. In the code below you can set the filter then do your join as normal.
SELECT * INTO #temp
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
--put consultant filter below
WHERE ConA.ConsultantID = '0002617' or ConA.ConsultantId = '0002354'
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
GO
SELECT a.ConsultantID,
a.AChieveLevel,
a.AchieveTItle,
b.Repromote,
a.ENdPeriodDate
FROM Volume a
INNER JOIN #temp b
on a.ConsultantID = b.ConsultantID
February 11, 2008 at 12:42 pm
Adam's solution seems more tested than mine...I'm sure it is 🙂 otherwise I think this will work...but be aware, it's not tested
Select c1.ConsultantID, c1.Level, c1.Title, c1.PeriodEndDate
from @C c1 join @C c2 on c1.Row_ID = (c2.Row_Id + 1)
where c2.level < c1.level and c1.ConsultantID = c2.ConsultantID
and exists (Select NULL from @C where Level = c1.Level and
ConsultantID = c1.ConsultantID and --incl. ConsultantId here
PeriodEndDate < c1.PeriodEndDate)
and c1.ConsultantID in ('0002354', '1112354') --Filter...
February 11, 2008 at 12:55 pm
Sorry you lost me. This is what the SP looks like now:
ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions]
@CONSULTANTID NVARCHAR(10)
AS
SET NOCOUNT ON
SET @ConsultantID = @ConsultantID
--Select * from Repromotes WHere ConsultantID = '0002354'
DECLARE @C TABLE(Row_ID INT IDENTITY(1,1),
ConsultantID CHAR(7),
AchieveLevel CHAR(2),
AchieveTitle CHAR(25),
PeriodEndDate DATETIME)
INSERT INTO @C
SELECTConsultantID
,AchieveLevel
,AchieveTitle
,PeriodEndDate
FROM volume a
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid
AND ConsultantID = @ConsultantID)
DECLARE @counter INT,@prev CHAR(2),@NbrRows INT
SET @counter = 1
SET @prev = NULL
SET @NbrRows = (SELECT COUNT(*) FROM @C)
DECLARE @r TABLE(ConsultantID CHAR(7),AchieveLevel CHAR(2),AchieveTitle CHAR(25),PeriodEndDate DATETIME)
WHILE @counter <= @NbrRows
BEGIN
INSERT INTO @rSELECT a.ConsultantID, a.AchieveLevel, a.AchieveTitle,a.PeriodEndDate
FROM @C a
WHERE a.Row_Id = @counter AND a.AchieveLevel > @prev AND a.AchieveLevel IN
(SELECT b.AchieveLevel
FROM @C b
WHERE b.Row_ID BETWEEN 1 AND @counter - 1)
SELECT @prev = a.AchieveLevelFROM @C a
WHERE a.Row_ID = @counter
SET @counter = @counter + 1
END
SELECT *FROM @r
Now how would I have to adapt that for multiple consultantIDs?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 1:06 pm
jonnie.pettersson (2/11/2008)
Adam's solution seems more tested than mine...I'm sure it is 🙂 otherwise I think this will work...but be aware, it's not tested
Select c1.ConsultantID, c1.Level, c1.Title, c1.PeriodEndDate
from @C c1 join @C c2 on c1.Row_ID = (c2.Row_Id + 1)
where c2.level < c1.level and c1.ConsultantID = c2.ConsultantID
and exists (Select NULL from @C where Level = c1.Level and
ConsultantID = c1.ConsultantID and --incl. ConsultantId here
PeriodEndDate < c1.PeriodEndDate)
and c1.ConsultantID in ('0002354', '1112354') --Filter...
That's essentially the code I gave him 2 months ago when he asked this question the first time. But I can't get him to tell us what's not right with it....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 1:24 pm
First you need to get away from the loop, while method works it is not the best solution. I changed my code in light of Marks logic and came up with a non recursive solution
**I editied this post because the site stripped my xml tags you will have to change the ? to greater than and less than signs.
ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions]
@CONSULTANTID NVARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @x XML
SET @x = '?i?' + REPLACE( @CONSULTANTID, '|', '?/i??i?') + '?/i?'
--Select * from Repromotes WHere ConsultantID = '0002354'
DECLARE @C TABLE(Row_ID INT IDENTITY(1,1),
ConsultantID CHAR(7),
AchieveLevel CHAR(2),
AchieveTitle CHAR(25),
PeriodEndDate DATETIME)
INSERT INTO @C
SELECT ConsultantID
,AchieveLevel
,AchieveTitle
,PeriodEndDate
FROM volume a
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid
AND ConsultantID = @ConsultantID)
SELECT *
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
WHERE ConB.ConsultantID IN (
SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i)
)
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
[/CODE]
You can then execute the code like this
EXEC [consultantreports].uspS_DownlineRepromotions '0002354|0002617'
February 11, 2008 at 1:26 pm
This process use xml to parse the string to derivce the consultant id that should be included. You can change the delmiter to whatever you like. Keep in mind that this is one way to skin a cat and you could choose to do something else if you perfer.
February 11, 2008 at 1:38 pm
Finally got the post to show up right :hehe:
February 11, 2008 at 1:42 pm
I may be missing something, but why not just use a Group By, Max(Date) and Having Count(*) > 1, type query on this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2008 at 1:55 pm
I have changed the '?' to ' ' and returns a blank recordset.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 2:01 pm
I may be missing something, but why not just use a Group By, Max(Date) and Having Count(*) > 1, type query on this?
- GSquared
This was my inital thought a few days ago, but based on the data using a group by with a having clause will not produce the correct results.
The desired result is to display all details for levels that have already been achieved. For example, a person is at a level 1 then gets promoted to 2,3,4 etc. Next year the person gets demoted to level 3. The OP wants to capture when the user gets promoted back to level 4. Using a group by,with a having clause, simply cannot deliver the desired result alone.
Additonally, the results of a group by are skewed because a person can maintain a level for months at a time, thus the count would be false because it would be > than 1.
February 11, 2008 at 2:16 pm
alorenzini,
I am going to attach the query via a txtfile. I have tested this query successfully. You should be able to see what is happening with yours.
February 11, 2008 at 2:38 pm
OK, now that I am venturing into unknown waters, I have the XML proc working but my next question would how do I create the String of consultantIDs? This is the proc that the would call the XML proc:
ALTER PROCEDURE [consultantreports].[uspS_ConsultantDownline]
@ConsultantIDASnVarChar(20), @PeriodDate AS DateTime, @LineFilter as Varchar(50)
----
AS
Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTime
DECLARE @QuarterStartDtASDateTime
DECLARE @QuarterEndDt AS DateTime
Declare @YearASDateTime
Declare @PeriodStartDt As DateTime
Declare @PeriodEndDt as DateTime
Declare @BumpupDate As Datetime
DECLARE @SQLStrnVarchar(100)
DECLARE @CurrMonthStartDt DateTime
DECLARE @CurrMonthEndDt DateTime
-- Breakdown @PeriodDate into Quarter and Months
SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)
Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1)
SET @CurrMonthStartDt = DATEADD(month, DATEDIFF(month, 0, GETDate()), 0)
Set @CurrMonthEndDt = DATEADD(month, DATEDIFF(month, -1, GetDate()), -1)
SET @LineFilter = @LineFilter
IF @MonthEndDt > GETDATE()
BEGIN
SET @MonthEndDt = GETDATE()
END;
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID)
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
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt 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
FROM dbo.uvwConsultantDownLine AS A with (nolock)
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
WHERE @MonthEndDt 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
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
I am think it would need to happen in here.
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
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS QuarterToDate_total
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS MonthToDate_Total
,D.ConsultantXID
,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt),0) AS QuarterToDate_Volume
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @MonthStartDt And @MonthEndDt),0) AS MonthToDate_Volume
,D.Active
,ISNULL(r.RepFlag,' ')AS RepFlag
,'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
,'ShowFlag' = CASE
WHEN @PeriodDate Between @CurrMonthStartDt AND @CurrMonthEndDt THEN 'X'
ELSE ' '
END
INTO #DLLevel from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 4
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
,r.RepFlag
,D.StatusID
ORDER BY 2
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 31 through 45 (of 124 total)
You must be logged in to reply to this topic. Login to reply