February 12, 2008 at 12:21 pm
I missed it. It is working now. It flys with 2 IDs (<1 sec)so the real test will be to get a string of 50 or more IDs and see what it does.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 12:22 pm
Ok. Keep us posted.
February 12, 2008 at 12:25 pm
You bet I will and thanks for everybody helps. It has been quite the learning experience. I am sure I will need more help as I go foward from here.:)
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 12:38 pm
I am thinking the XML string is not going to work now that I am trying to put together like 983 Consultant IDs:
Msg 9400, Level 16, State 1, Procedure uspS_DownlineRepromotions2, Line 7
XML parsing: line 1, character 4000, unexpected end of input
I have declared the @ConId as Varchar(Max) but it does seem wide enough or did I miss something?
The code for the main proc:
DECLARE@ConsultantIDASnVarChar(50)
DECLARE @PeriodDateASDateTime
DECLARE @LineFiltervarchar(20)
DECLARE @SQLStrnVarchar(100)
SET @ConsultantID = '0000112'
SET @PeriodDate = '2/02/2008'
SET @LineFilter = 'Lines 1, 2 and 3'
-- Declare Local Variables
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 @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 Downline Level Filters
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
DECLARE @ConIDs VARCHAR(MAX)
SET @ConIDs =
(SELECT DISTINCT RTRIM(ConsultantID) + '|'
FROM #DownLine
FOR XML PATH(''))
CREATE TABLE #TEMP (Row_ID INT IDENTITY(1,1),
ConsultantID CHAR(20),
AchieveLevel CHAR(2),
AchieveTitle CHAR(50),
PeriodEndDate DATETIME,
RepFlag char(1))
INSERT INTO #Temp
EXEC uspS_DownlineRepromotions2 @ConIDs
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 #Temp 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
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
-- ALL Level Filters
IF @LineFilter = ('Lines 1, 2, 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'0' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- Only Line 1 Filter
IF @LineFilter = ('Only Line 1')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'1' )
END
-- Only Line 2 Filter
IF @LineFilter = ('Only Line 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'2' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Only Line 3 Filter
IF @LineFilter = ('Only Line 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Only Line 3 Filter
IF @LineFilter = ('Only Line 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 1 and 2 Filter
IF @LineFilter = ('Lines 1 and 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Lines 1, 2, and 3 Filter
IF @LineFilter = ('Lines 1, 2 and 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 3 and 4 Filter
IF @LineFilter = ('Lines 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- Execute the string
EXEC sp_executesql @SQLstr;
-- Drop the temp table
--Select * FROM #Downline
--Select * FROM #DLLevel
DROP TABLE #Downline
DROP TABLE #DLLevel
DROP TABLE #Temp
Any Ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 12:52 pm
Is it possible that the parameter length in uspS_DownlineRepromotions2 is shorter?
In another post it was varchar(8000)
February 12, 2008 at 12:53 pm
Check your procedure you may have your procedure variable not declared as varchar(max)
Is this your stored procedure variable that you are passing conids into?
if so you will need to bump this guys up to DECLARE @ConsultantID AS VarChar(max)
February 12, 2008 at 1:29 pm
I went to ConsultantID that had fewer consultants on their downline (28) and it gave me this new error:
Warning: Null value is eliminated by an aggregate or other SET operation.
(28 row(s) affected)
Msg 213, Level 16, State 7, Procedure uspS_DownlineRepromotions2, Line 33
Insert Error: Column name or number of supplied values does not match table definition.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 1:32 pm
This is the temp table from the Repromotion2 proc:
CREATE TABLE #C
(ConsultantID CHAR(7)
,AchieveLevel CHAR(2)
,AchieveTitle CHAR(25)
,PeriodEndDate DATETIME
,RepFlag VarChar(2))
THis is the temp table from the main proc:
CREATE TABLE #TEMP (Row_ID INT IDENTITY(1,1),
ConsultantID CHAR(20),
AchieveLevel CHAR(2),
AchieveTitle CHAR(50),
PeriodEndDate DATETIME,
RepFlag Varchar(2)
)
Is it the row_id what is causing the problem in the Repromotion proc?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 1:47 pm
Yes it is but if you change...
INSERT INTO #Temp(ConsultantID, AchieveLevel, AchieveTitle, PeriodEndDate, RepFlag)
EXEC uspS_DownlineRepromotions2 @ConIDs
It should work
February 12, 2008 at 1:52 pm
You can remove the row_id column as the solution you are using does not require it.
February 12, 2008 at 2:03 pm
It's still doing it. This is the what the #Temp looks like now in the main proc:
CREATE TABLE #TEMP (
ConsultantID CHAR(20),
AchieveLevel CHAR(2),
AchieveTitle CHAR(50),
PeriodEndDate DATETIME,
RepFlag Varchar(2)
)
And this is what the Repromote proc look like now:
DECLARE @x XML
SET @x = ' '
CREATE TABLE #C
(ConsultantID CHAR(7)
,AchieveLevel CHAR(2)
,AchieveTitle CHAR(25)
,PeriodEndDate DATETIME
,RepFlag VarChar(2))
INSERT INTO #C
SELECT a.ConsultantID,a.AchieveLevel ,a.AchieveTitle,
a.PeriodEndDate, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
SELECT * FROM
(SELECT DISTINCT ConB.ConsultantID
, ConB.AchieveLevel
, ConB.AchieveTitle
, ConB.PeriodEndDate
, ConB.RepFlag
FROM #C ConA
INNER JOIN #C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.AchieveLevel = ConB.AchieveLevel AND ConA.PeriodEndDate < ConB.PeriodEndDate) as CON
WHERE EXISTS (SELECT * FROM #C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.AchieveLevel < CON.AchieveLevel
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
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 2:20 pm
It looks like your insert into temp table worked successfully. Can you verify this by putting a select * from #temp after the insert.
Also it might be benificial to eliminate null consultant ids for the insert. so change your insert to this.
INSERT INTO #C
SELECT a.ConsultantID, a.AchieveLevel ,a.AchieveTitle,
a.PeriodEndDate, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
WHERE b.ConsultantID IS NOT NULL
February 12, 2008 at 2:26 pm
I change the NULLS and when I did this in the main proc:
CREATE TABLE #TEMP (
ConsultantID CHAR(20),
AchieveLevel CHAR(2),
AchieveTitle CHAR(50),
PeriodEndDate DATETIME,
RepFlag Varchar(2)
)
INSERT INTO #Temp
EXEC uspS_DownlineRepromotions2 @ConIDs
Select * from #Temp
It errors out here:
Warning: Null value is eliminated by an aggregate or other SET operation.
(28 row(s) affected)
Msg 213, Level 16, State 7, Procedure uspS_DownlineRepromotions2, Line 33
Insert Error: Column name or number of supplied values does not match table definition.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 2:40 pm
Have you changed uspS_DownlineRepromotions2 in some way?
Try to run the uspS_DownlineRepromotions2 procedure separately in a query window with the same ConIds....
February 12, 2008 at 2:45 pm
I haven't change anything in the Repromote proc, this is code as it now:
ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]
@CONSULTANTID VARCHAR(MAX)
AS
SET NOCOUNT ON
DECLARE @x XML
SET @x = ' '
CREATE TABLE #C
(ConsultantID CHAR(20)
,AchieveLevel CHAR(2)
,AchieveTitle CHAR(50)
,PeriodEndDate DATETIME
,RepFlag VarChar(2))
INSERT INTO #C
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
WHERE b.ConsultantID IS NOT NULL
SELECT * FROM
(SELECT DISTINCT ConB.ConsultantID
, ConB.AchieveLevel
, ConB.AchieveTitle
, ConB.PeriodEndDate
, ConB.RepFlag
FROM #C ConA
INNER JOIN #C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.AchieveLevel = ConB.AchieveLevel AND ConA.PeriodEndDate < ConB.PeriodEndDate) as CON
WHERE EXISTS (SELECT * FROM #C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.AchieveLevel < CON.AchieveLevel
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
This works when I run it by itself.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 61 through 75 (of 124 total)
You must be logged in to reply to this topic. Login to reply