Recursive Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive Query

Author

Topic

alorenzini
Starting Member

3 Posts

Posted - 2007-11-26 : 17:25:49

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?

My resultset looks like this:
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.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-26 : 19:20:30

If I'm understanding you properly, the error looks like it lies in

B.AdjustedPartOneTotal

The way you wrote the code it simply will keep repeating whatever o.AdjustedPartOneTotal was, because that's all you are telling it to do.

Is this supposed to be a running total? Please explain what you want that field to actually do, and it should be a easy fix.

alorenzini
Starting Member

3 Posts

Posted - 2007-11-27 : 08:48:47

There are 38 AdjustedPartOneTotal amounts
that come from the uvw_DownlistOrder view and there are like 64 Consultants. The 38 AdjustedPartOneTotal amounts should appear with the appropriate consultant based on the consultant id which are in both the consultant table and the view. The remaining consultants should have 0s for the AdjustedPartOneTotal amount.

Did that help?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 11:09:36

the code is a little overwhelming but assuming a basic understanding you need a query similar to this...
select dbo.consultant.consultantname, ISNULL(SUM(downline.AdjustedPartOneTotal), 0.0) AS Total
from dbo.consultant
left join downline on downline.consultantid = dbo.consultant.consultantid

there is a chance changing your "LEFT JOIN" to "RIGHT JOIN" will also get you closer

alorenzini
Starting Member

3 Posts

Posted - 2007-11-27 : 11:26:55

This gives me the same issue. It's repeating the SUM value.

Thanks
Art
Database Analyst
Tastefully Simple, Inc.
Alexandria, MN

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 15:56:06

can you please post some sample data and expected resultset?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources