November 26, 2007 at 11:50 am
I have the following code:
DECLARE @ConsultantID AS nVarChar(50)
,@StartDt AS DateTime
,@EndDt AS DateTime
SET @ConsultantID = '0000112'
SET @StartDt = '2007-05-01'
SET @Enddt = '2007-05-31'
DECLARE @Quarter AS DateTime
Declare @Year AS DateTime
SET @StartDt = Convert(DateTime,Convert(nVarChar(50),@StartDt,101) + ' 00:00:00.000')
SET @EndDt = Convert(DateTime,Convert(nVarChar(50),@EndDt,101) + ' 23:59:59.997')
SET @Quarter = DATEADD(qq, DATEDIFF(qq,0,@StartDt), 0)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@StartDt), 0);
--[consultantreports].[uspS_ConsultantDownline] '0000003'
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID, AdjustedPartOneTotal)
AS
(
SELECT Convert(NVARCHAR(MAX),A.ConsultantID)
,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX))
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0 as DownLineLevel
,A.ConsultantXID
,A.SponsorXID
,O.AdjustedPartOneTotal
FROM dbo.consultant A
INNER JOIN uvw_DownlineOrder O ON A.ConsultantID = O.ConsultantID
WHERE A.ConsultantID = @ConsultantID
UNION ALL
SELECT CAST (A.ConsultantID AS NVARCHAR(MAX))AS ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID) As SponsorName
,DownLineLevel + 1
,A.ConsultantXID
,A.SponsorXID
,B.AdjustedPartOneTotal
FROM dbo.consultant AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
)
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,CAST(CONVERT(NVARCHAR(MAX),A.ConsultantID)AS INT) as ConsultantID
,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
,AdjustedPartOneTotal
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
,A.AdjustedPartOneTotal
GO
My issue is I need to sum the AdjustedPartOneTotal column per consultant. But this way I get individual amount per consultant. I tried adding the following code but it repeats the first amount over and over although it is rolling up the consultants appropriately:
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,CAST(CONVERT(NVARCHAR(MAX),A.ConsultantID)AS INT) as ConsultantID
,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
,SUM(AdjustedPartOneTotal) AS Total
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
can some one shed some light on this?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 26, 2007 at 2:18 pm
Arthur.Lorenzini (11/26/2007)
I have the following code:DECLARE @ConsultantID AS nVarChar(50)
,@StartDt AS DateTime
,@EndDt AS DateTime
SET @ConsultantID = '0000112'
SET @StartDt = '2007-05-01'
SET @Enddt = '2007-05-31'
DECLARE @Quarter AS DateTime
Declare @Year AS DateTime
SET @StartDt = Convert(DateTime,Convert(nVarChar(50),@StartDt,101) + ' 00:00:00.000')
SET @EndDt = Convert(DateTime,Convert(nVarChar(50),@EndDt,101) + ' 23:59:59.997')
SET @Quarter = DATEADD(qq, DATEDIFF(qq,0,@StartDt), 0)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@StartDt), 0);
--[consultantreports].[uspS_ConsultantDownline] '0000003'
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID, AdjustedPartOneTotal)
AS
(
SELECT Convert(NVARCHAR(MAX),A.ConsultantID)
,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX))
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0 as DownLineLevel
,A.ConsultantXID
,A.SponsorXID
,O.AdjustedPartOneTotal
FROM dbo.consultant A
INNER JOIN uvw_DownlineOrder O ON A.ConsultantID = O.ConsultantID
WHERE A.ConsultantID = @ConsultantID
UNION ALL
SELECT CAST (A.ConsultantID AS NVARCHAR(MAX))AS ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID) As SponsorName
,DownLineLevel + 1
,A.ConsultantXID
,A.SponsorXID
,B.AdjustedPartOneTotal
FROM dbo.consultant AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
)
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,CAST(CONVERT(NVARCHAR(MAX),A.ConsultantID)AS INT) as ConsultantID
,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
,AdjustedPartOneTotal
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
,A.AdjustedPartOneTotal
GO
My issue is I need to sum the AdjustedPartOneTotal column per consultant. But this way I get individual amount per consultant. I tried adding the following code but it repeats the first amount over and over although it is rolling up the consultants appropriately:
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,CAST(CONVERT(NVARCHAR(MAX),A.ConsultantID)AS INT) as ConsultantID
,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
,SUM(AdjustedPartOneTotal) AS Total
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
can some one shed some light on this?
Check your group by clause currently :
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
So if SponsorId is different you will split the quantities ... are you sure that is what you want ?
* Noel
November 26, 2007 at 2:22 pm
The should only be broken by COnsultantID not SponsorID. The SponsorID is there because it is used to pull the name sponsor of the consultant which is in the same table.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 26, 2007 at 3:20 pm
I don't know if this will help but this is what I am getting back:
DownlineLevel, ConsultantID, Sum(Total)
0 3 104.80
1 23 104.80
1 30 104.80
2 40 104.80
1 65 104.80
1 73 104.80
2 89 104.80
1 102 104.80
1 112 104.80
2 127 104.80
3 131 104.80
1 142 104.80
2 244 104.80
The issue is the Sum value should be differnt amounts and actually a majority should be 0.
hope this sheds a little bit of light.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 27, 2007 at 7:54 am
I think I have it narrowed down to this statement:
---- Return member
SELECT A.DownLineLevel
--,C.BumpUpDate
,CAST(CONVERT(NVARCHAR(MAX),A.ConsultantID)AS INT) as ConsultantID
,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
,ISNULL(Sum(A.AdjustedPartOneTotal ),0) AS YTD_Total
,Count(*) AS YTD_Orders
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
-- ,C.BumpUpDate
,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
GO
The ,ISNULL(Sum(A.AdjustedPartOneTotal ),0) AS YTD_Total is repeating the same total for each consultantID. Do I have it setup wrong?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply