Adding Summs to Recursive Query

  • 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

  • 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

  • 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

  • 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

  • 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